Visit Our UNIX and Linux User Community


sqlplus and shell scripting


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers sqlplus and shell scripting
# 1  
Old 08-07-2001
Question sqlplus and shell scripting

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 Smilie 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 Smilie
inquirer
# 2  
Old 08-07-2001
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  
Old 08-07-2001
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  
Old 08-08-2001
still have questions but thanks

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. Smilie
inquirer
# 5  
Old 08-09-2001
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  
Old 08-09-2001
thanks for the reply

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! Smilie
inquirer
# 7  
Old 08-09-2001
a li'l more help maybe.....

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
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #528
Difficulty: Medium
The Extended ASCII character set adds support for 128 additional characters (numbered 128 through 255) by adding 1 more bit (16 total).
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus shell

friends because when calling a sqlplus from a shell it brings me the following message sqlplus -s $BDDUSER/$BDDPASS@$BDDHOST @$DIR_SQL/prueba.sql echo "bandera 3 " $? STATUS=$? if ;then echo "bandera 4 " $? #log_info "La ejecucion de... (1 Reply)
Discussion started by: tricampeon81
1 Replies

2. Shell Programming and Scripting

Running sqlplus for 5 DB in a shell

Hi all, on AIX 6.1 I want to run the following but for 5 DB. How should I do that ? Using FOR, WHILE ???? How ? export ORACLE_SID=DB1 sqlplus / as sysdba << EOF whenever sqlerror exit sql.sqlcode; whenever oserror exit FAILURE set define off set head off set feedback off set echo off... (1 Reply)
Discussion started by: big123456
1 Replies

3. Shell Programming and Scripting

sqlplus in shell script

Hi When I use sqlplus in shell script, I get sqlplus: command not found. ORACLE_HOME is not set. How to set ORACLE_HOME in unix? Thanks (3 Replies)
Discussion started by: vinoth_kumar
3 Replies

4. Shell Programming and Scripting

(solved) Shell scripting to access SQLPLUS using variable

I have shell script which will try to login to SQL Plus and retrieve some data, based on the outcome i will proceed further Below is the content of the file pebblz02% cat test1.ksh #! /bin/ksh dummyvar=`sqlplus -S csm_admin/csm_admin@SIDNAME <<EOF echo hi; exit; EOF` Error message on... (0 Replies)
Discussion started by: kiranlalka
0 Replies

5. Shell Programming and Scripting

SQLPLUS within shell script

Hi I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional. Can anybody help me with this. Thanks Gaurav (4 Replies)
Discussion started by: gaurav_1711
4 Replies

6. Programming

Shell SQLPlus

Hi, I am trying to execute the update statment in shell sqlplus.But nothing prompts.if i do ctrl+c i got the below error. SQL> update table set enabled='N' where type_code='xx'; ^C update table set enabled='N' where type_code='xx' * ERROR at line 1: ORA-01013: user requested... (2 Replies)
Discussion started by: nmahendran
2 Replies

7. UNIX for Dummies Questions & Answers

Shell Script And SQLPLUS

i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help ;) I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is... (6 Replies)
Discussion started by: maco_home
6 Replies

8. Shell Programming and Scripting

SQLplus in Shell scripts

How to execute a query which is stored in a variable. Say for example : v_source_query=”select count(*) from emp” v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF | tr '\n' ' ' set feed off set pagesize 0 set head... (12 Replies)
Discussion started by: trupti_d
12 Replies

9. Shell Programming and Scripting

connect to sqlplus from shell

I'm writting a shell script and at the begining I login to sqlplus by sqlplus -l user_name/password@instance what I would like is to check if the database is down or not , and if the database has started moved to the next step else sleep for a certain time and then check again . I know how to... (4 Replies)
Discussion started by: aya_r
4 Replies

10. Shell Programming and Scripting

difference between AIX shell scripting and Unix shell scripting.

please give the difference between AIX shell scripting and Unix shell scripting. (2 Replies)
Discussion started by: haroonec
2 Replies

Featured Tech Videos