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 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

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

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...

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.

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!

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!

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