UNIX for Dummies Questions & Answers

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

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

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