Sponsored Content
Top Forums UNIX for Dummies Questions & Answers Unix + oracle doubt....involving shell script Post 4879 by mexx_freedom on Thursday 2nd of August 2001 09:48:56 AM
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
 

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

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

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

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

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

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

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

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

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

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

10. 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
SCRIPT(1)						    BSD General Commands Manual 						 SCRIPT(1)

NAME
script -- make typescript of terminal session SYNOPSIS
script [-akq] [-t time] [file [command ...]] DESCRIPTION
The script utility makes a typescript of everything printed on your terminal. It is useful for students who need a hardcopy record of an interactive session as proof of an assignment, as the typescript file can be printed out later with lpr(1). If the argument file is given, script saves all dialogue in file. If no file name is given, the typescript is saved in the file typescript. If the argument command is given, script will run the specified command with an optional argument vector instead of an interactive shell. The following options are available: -a Append the output to file or typescript, retaining the prior contents. -k Log keys sent to program as well as output. -q Run in quiet mode, omit the start and stop status messages. -t time Specify time interval between flushing script output file. A value of 0 causes script to flush for every character I/O event. The default interval is 30 seconds. The script ends when the forked shell (or command) exits (a control-D to exit the Bourne shell (sh(1)), and exit, logout or control-D (if ignoreeof is not set) for the C-shell, csh(1)). Certain interactive commands, such as vi(1), create garbage in the typescript file. The script utility works best with commands that do not manipulate the screen. The results are meant to emulate a hardcopy terminal, not an addressable one. ENVIRONMENT
The following environment variable is utilized by script: SHELL If the variable SHELL exists, the shell forked by script will be that shell. If SHELL is not set, the Bourne shell is assumed. (Most shells set this variable automatically). SEE ALSO
csh(1) (for the history mechanism). HISTORY
The script command appeared in 3.0BSD. BUGS
The script utility places everything in the log file, including linefeeds and backspaces. This is not what the naive user expects. It is not possible to specify a command without also naming the script file because of argument parsing compatibility issues. When running in -k mode, echo cancelling is far from ideal. The slave terminal mode is checked for ECHO mode to check when to avoid manual echo logging. This does not work when in a raw mode where the program being run is doing manual echo. BSD
January 22, 2004 BSD
All times are GMT -4. The time now is 03:59 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy