sqlplus and shell scripting

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

i would like to learn how to integrate my little knowledge in shell scripting with sqlplus. well... i know how to make basic query in sqlplus but i dont know how i can integrate it with shell script. can someone :slight_smile: please help me on this? can you give me some basic example on how to do this kind of stuff? for example, i would like to create some report through sqlplus query and later on modified/alter its output using good basic shell scripting. can somebody out there help me on this, i pretty pretty much would like to learn. and if not much to ask kindly provide me a site where i can have info/practice on sqlplus on UNIX. thanks :slight_smile:

2

write your sql statement in an seperated file. Name this file for example myreport.sql, then start this file from yout shell script like

sqlplus system/manager@SID @myreport

or if you do not use sql*net

sqlplus system/manager @myreport

3

Some of the simple scripts we have going here can be dynamic queries... something like this:

#!/usr/bin/ksh
Blah=$1
....

sqlplus system/manager@testdb1 << EndOfFile
select * from dual ;
{blah blah blah}
quit
EndOfFile

{more shell script}

Not very secure, but very quick and useful...

4

gee, thanks michael and livinfree for the reply. i'll try those suggestion you gave me on my way to work. but how about errors during query? will the script still run its course or is it the other way around? by the way livinfree, how about showing me shell script version coz i'm not familiar with ksh. :wink:

5

Heh, sorry... you could use the same idea with /bin/sh. /usr/bin/ksh has some advantages, but a simple script should be portable between the two.

If you get an error, the SQL statement / query will error out, but the script will keep going. So if there are functions later on in the script that depend on the results of your query, you may need to find some sort of error handling to add to the script.

HTH

--
LivinFree!

6

i just got home to work and i've tried all of your suggestions and they did work just fine. i just have to work on some minor details before finalizing my script...thanks again michael and livinfree hope to here from you again! :smiley:

7

hi,
as for catching errors in sql this might help.
u can have an exception block in the sqplus block and catch the return code once u come out of the block.
so if u have an error u can abort the script so that the next in line functions r not executed.

....script
.....
....
spout=`sqlplus -s $UP <<EOJ

SET HEAD OFF
SET AUTOPRINT OFF

WHENEVER SQLERROR EXIT;
(this way if u have an sql error u get an exit condition)
(either have this or catch it in the excepton block)

ALTER TRIGGER XX ENABLE;

CREATE ........ON ........(......)
TABLESPACE .......... PCTFREE 10
STORAGE(INITIAL 104K NEXT 104K PCTINCREASE 0 );

DECLARE

v_alter_statement VARCHAR2(255);

CURSOR c_constraint_name IS
SELECT constraint_name
FROM dba_constraints
WHERE table_name = 'YYYY';

BEGIN
FOR REC IN c_constraint_name
LOOP
v_alter_statement := 'ALTER TABLE YYYY';
v_alter_statement := v_alter_statement || ' ENABLE CONSTRAINT ';
v_alter_statement := v_alter_statement || REC.constraint_name;
DBMS_UTILITY.EXEC_DDL_STATEMENT(v_alter_statement);
END LOOP;

Exception

when OTHERS then
    Rollback;
    dbms\_output.put_line \('Script failed  :   Sqlcode: ' || SQLCODE  || ' Error message :' ||
    SQLERRM\);
    return;

END;
/
EOJ`

....pl sql ends here!!
you have to define FATAL and sucess in ur env file afaik!

ret_code=$?

echo "$spout" >> ${logfile}

if [ $ret_code -ne $SUCCESS ]
then
cat ${logfile}
echo
echo
echo Script Failed.
echo "Script Ended : " `date`
echo

exit $FATAL
fi

if test $ret_code -ne 0
then
cat ${logfile}
echo
echo
echo Script Failed.
echo "Script Ended : " `date`
echo

exit ${OXYGEN_FATAL}
fi

# no errors.
cat ${logfile}
echo
echo
echo Script completed sucessfully
echo "Script Ended : " `date`
echo

exit $SUCCESS

well this is just an example script!
hope this helps.
Regards
Manish

8

thanks for the additional info... however right now i'm not that much on sqlplus. simple queries are fine for the moment and your response to the error handling caught my attention :wink: i was wondering is there any site where i can practice sqlplus. let say create an account and have access to a test database where i can practice? coz i know that there are sites that give free shell accounts where anyone can practice, how about sqlplus in specific? appreciate your reply :slight_smile:

thanks.

9

Are you running Linux at home, or have access to somewhere that it is? If so, head on over to oracle.com and download yourself a free personal version. Install away, and play!

10

no i don't have linux installed in me, what i have is a compaq presario 1200 notebook in which windows 98 is currently installed. i only starting to get a hang on unix at work and someday would like to familiarize with sqlplus. it will be good to my competency at the same time self-fulfilling in my part :slight_smile: please help...

thanks.

11

Well, you're already on the right track...
Asking questions, showing an interest; that's the start to learning I can think of.