Visit Our UNIX and Linux User Community


Unix + oracle doubt....involving shell script


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Unix + oracle doubt....involving shell script
# 1  
Old 08-02-2001
Question Unix + oracle doubt....involving shell script

....does the dbms_output.put_line work inside unix shell script?
i mean this is to be inside the sqlplus connection as follows!!

sqlplus -s $UP <<EOJ
..
..
..
dbms_output.put_line ('Insertion procedure failed for UPC BC : ' || wk_key_value || ' Sqlcode: ' || SQLCODE || ' Error message :' || SQLERRM);
..
..

also could neone tell me as to why i am getting the following error
ERROR: ORA-01756: quoted string not properly terminated

after debugging i came to know that this error arises when the control comes across keyword DECLARE in the following code in the script
spout=`sqlplus -s $UP <<EOJ

sqlplus -s $UP <<EOJ

SET HEAD OFF
SET AUTOTRACE OFF

WHENEVER SQLERROR EXIT 255

DECLARE

errmessage VARCHAR2
errcode NUMBER
v_alter_statement VARCHAR2(255)

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

i wasn't able to locate why is this happening!
and amazingly the same thing works absolutely fine in another script of mine.
PLZZZZZ help

Regards
Manish
# 2  
Old 08-02-2001
Bug

You will write script. In your script,
you have
<erased>
sqlplus username/password @filename.sql
<erased>
code.

Also, in your script directory you will have filename.sql,which have
SELECT clauses. : )

It works...
# 3  
Old 08-02-2001
MySQL Problem solved

Hi,
Thanks for the info.

The problem got solved as follows.
the script was as follows
-------------------------------------------------------------
..
..
..
spout=`sqlplus -s $UP <<EOJ

SET HEAD OFF
SET AUTOTRACE OFF

WHENEVER SQLERROR EXIT 255

ALTER TRIGGER schema.trg DISABLE;

DECLARE

v_alter_statement VARCHAR2(255);

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

BEGIN
FOR REC IN c_constraint_name
LOOP
v_alter_statement := 'ALTER TABLE XXXX';
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`


ret_code=$?

echo "$spout" >>${logfile} 2>&1

if [ $ret_code -ne $SUCCESS ]
then
cat ${logfile}
exit $FATAL
fi
..
..
..


----------------------------------------------------------------

now what was happening was that if there is a pl/sql block in a script then whole of it is executed in one go (that's what a pl/sql block is supposed to mean , poor me didn't remember). now i had by mistake given an extra quote(') in the dbms_output.put_line of exception block. this raised an error. now if u give dbms_output.put_line b4 and after declare the one b4 declare gets printed but not the one after. so i was inferring that the control never reached inside the declare statement. whereas it did reach inside but the pl/sql block failed at the dbms_output.put_line in exception block!!!!

also i was getting directory listing of the directory in which i ran this script in the log file if the script failed. this was happening becaue i had the following statemnt

echo $spout >>${logfile} 2>&1

now the var spout had * in it since al the output of sql is directed into it and the procedure failed or in case some pl/sql error occured.

makin this statement as

echo "$spout" >>${logfile} 2>&1

made it fine.

Thanks n Regards
Manish
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #576
Difficulty: Medium
All programming languages have automatic garbage collection that monitors the dynamically allocated pieces of memory and determine if any variable in the program still references it. If the memory is no longer referenced, it is 'garbage' and becomes eligible to be 'collected'.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Oracle function invoked from shell script doubt

hi gurus, I have tried myself to invoke an oracle function. there are three different function available need to be called for differnt. can you tell me whether the below code is correct to call oracle function from shell script. Any help would be highly appreciated. cat location.sh ... (5 Replies)
Discussion started by: arun888
5 Replies

2. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

3. UNIX for Advanced & Expert Users

Use of Oracle pl/sql in UNIX shell script

Hi, I have basic knowledge on how to write pl/sql code inside shell script. I am looking for more advance thing. Is there any book for that which can just talk about how to write more advance plsql code inside shell script. Please help Thanks!!!!!! (1 Reply)
Discussion started by: diehard
1 Replies

4. Shell Programming and Scripting

Need to access Oracle DB with shell/perl script in Unix

Hi, We need to access Oracle DB with shell/perl script in Unix. Is Oracle client needed in Unix for this. I have seen threads which tell abt using SQL plus to access Oracle tables. Can we access DB without SQL PLus installation using scripts in UNix like we access DB using jar files in Java .... (1 Reply)
Discussion started by: justinacc
1 Replies

5. Shell Programming and Scripting

How to fetch data from oracle in unix shell script

Hi, How to fetch data from oracle database in unix shell scripting. list=`sqlplus -s ds_user/dsuser@EMI <<EOF set feedback off set serveroutput on set heading off set pagesize 0 set tab off select IP_ID from table / exit EOF` The output is not what i expected.I need output in... (4 Replies)
Discussion started by: Anusha_Reddy
4 Replies

6. UNIX for Dummies Questions & Answers

Unix shell script Certification for oracle developer.

hi friends, I would like to do the certification in Korn shell scripts { i am using HP UNIX machine }. is there any certification for UNIX shell script . If yes please tell me the details . i am junior level oracle plsql developer . i interseted in unix shell scripting . i need... (1 Reply)
Discussion started by: rdhaprakasam
1 Replies

7. UNIX for Dummies Questions & Answers

Doubt in Oracle connecting unix - Very urgent

I am using a oracle query from unix.. flag=`sqlplus -s <<EOF SELECT 'Y' FROM table_name WHERE cond1 = '${table_name}' AND DECODE('${var_a}','''NA''',own,'${var_b0}')= own exception when no_data_found then dbms_output.put_line(NVL(l_owner_flag,'X')); end; EOF` Its not validating the... (7 Replies)
Discussion started by: sivakumar.rj
7 Replies

8. Shell Programming and Scripting

Invoking Oracle stored procedure in unix shell script

Here's a shell script snippet..... cd $ORACLE_HOME/bin Retval=`sqlplus -s <<eof $TPDB_USER/april@$TPD_DBCONN whenever SQLERROR exit 2 rollback whenever OSERROR exit 3 rollback set serveroutput on set pages 999 var status_desc char(200) var status_code... (1 Reply)
Discussion started by: hidnana
1 Replies

9. Shell Programming and Scripting

Calling an Oracle Stored Procedure from Unix shell script

hai, can anybody say how to call or to execute an oracle stored procedure in oracle from unix... thanks in advance.... for ur reply.... by, leo (2 Replies)
Discussion started by: Leojhose
2 Replies

10. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

how to pass values from oracle sql plus to unix shell script (2 Replies)
Discussion started by: trichyselva
2 Replies

Featured Tech Videos