08-02-2001
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
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
how to pass values from oracle sql plus to unix shell script (2 Replies)
Discussion started by: trichyselva
2 Replies
2. Shell Programming and Scripting
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
3. Shell Programming and Scripting
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
4. UNIX for Dummies Questions & Answers
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
5. UNIX for Dummies Questions & Answers
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
6. Shell Programming and Scripting
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
7. Shell Programming and Scripting
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
8. UNIX for Advanced & Expert Users
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
9. Shell Programming and Scripting
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
10. Shell Programming and Scripting
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
LEARN ABOUT OPENSOLARIS
script
script(1) User Commands script(1)
NAME
script - make record of a terminal session
SYNOPSIS
script [-a] [filename]
DESCRIPTION
The script utility makes a record of everything printed on your screen. The record is written to filename. If no file name is given, the
record is saved in the file typescript. See WARNINGS.
The script command forks and creates a sub-shell, according to the value of $SHELL, and records the text from this session. The script ends
when the forked shell exits or when Control-d is typed.
OPTIONS
The following option is supported:
-a Appends the session record to filename, rather than overwriting it.
NOTES
script places everything that appears on the screen in filename, including prompts.
ATTRIBUTES
See attributes(5) for descriptions of the following attributes:
+-----------------------------+-----------------------------+
| ATTRIBUTE TYPE | ATTRIBUTE VALUE |
+-----------------------------+-----------------------------+
|Availability |SUNWcsu |
+-----------------------------+-----------------------------+
|CSI |Enabled |
+-----------------------------+-----------------------------+
SEE ALSO
attributes(5)
WARNINGS
script can pose a security risk when used in directories that are writable by other users (for example, /tmp), especially when run by a
privileged user, that is, root. Be sure that typescript is not a link before running script.
SunOS 5.11 30 Jan 2004 script(1)