ksh and Oracle stored procedure output in logfile Post: 302931777

Sponsored Content

Top Forums Shell Programming and Scripting ksh and Oracle stored procedure output in logfile Post 302931777 by cero on Thursday 15th of January 2015 11:14:22 AM

01-15-2015

Registered User

There are some issues:
* you never actually connect to any database (have a look at the SQL*Plus command connect)
* you need to feed the commands you wish to run to SQL*Plus. Here-documents or sql-commandfiles are common practice for this.
* in your procedure you declare parameter lFour as date but you pass a string. Call it like this:

Code:

exec pkg.proc($1, $2, $3, to_date($4,'YYYYMMDD'),:v_empid, :v_name, :v_last, :v_dt);

* you read some values into variables but never print them.
* If your query does not return exactly one row you'll see an error message.

What is your final goal?
Spooling output of a query to a file does not have to be done via PL/SQL, actually it is a lot easier without.
The dbms_ouput package can be used to display results if PL/SQL is needed.
There are lots of ways to accomplish what you are trying to do, but from your example it is hard to tell which would fit.

Last edited by cero; 01-15-2015 at 12:21 PM..

This User Gave Thanks to cero For This Post:

cero

View Public Profile for cero

Find all posts by cero

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Oracle stored procedure.

I am using sqlplus. I have the stored procedure name. How can i print the stored procedure content?

2. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ...

3. Shell Programming and Scripting

Shell arrays in oracle stored procedure

Is it possible to pass unix shell arrays in Oracle stored procedure? Is yes, how? Thanks

4. Shell Programming and Scripting

calling a PL/SQL stored procedure from KSH

Hi I have a stored procedure which should be called from KSH. Could ayone please help me with this. Thanks

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

6. Shell Programming and Scripting

how to pass the values to unix shell from the oracle stored procedure.

Hi i am calling a stored procedure from unix shell like this call test_proc('0002','20100218'); the stored procedure was giving output like this dbms_output.put_line(' processed earlier'); i want to see the output in the unix shell where i called. Thanks barani

7. Shell Programming and Scripting

how to call oracle stored procedure from unix shell

Hi i want to call a oracle stored procedure from unix (using bash shell). consider this is my oracle stored procedure with parameter create procedure testproc(name IN varchar, age IN Number, id OUT Number ) AS begin id=1; dbms_output.put.line('successfull validation') end;...

8. Shell Programming and Scripting

Call and redirect output of Oracle stored procedure from unix script

Hi, Can you assist me in how to redirect the output of oracle stored procedure from unix script? Something similar to what i did for sybase isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %% proc_my_test 8 go %% Thanks in advance - jak

9. Shell Programming and Scripting

Need to run Oracle stored procedure from UNIX env

Hi Everyone, I want to create a script where i need to run the oracle stored procedure from unix script and get the output(sequence number ) into a variable which i will pass in my datastage job. Below is my stored procedure:- DECLARE P_TRANSTYPE VARCHAR2(20); ...

10. Shell Programming and Scripting

Calling Oracle stored procedure from ksh script

Friends, I'm newbie with ksh so wanting some help.... 1. I'm trying to call oracle stored procedure from ksh script by taking variable value from runtime, feed into script and execute procedure. 2. Put name1 and name2 value from script run replacing $3 & $4 I'm trying to put name1 in...

LEARN ABOUT OSF1

false

true(1) 						      General Commands Manual							   true(1)

NAME

       true, false - Returns a standard exit value

SYNOPSIS

       true

       false

STANDARDS

       Interfaces documented on this reference page conform to industry standards as follows:

       true:  XCU5.0

       false:  XCU5.0

       Refer to the standards(5) reference page for more information about industry standards and associated tags.

DESCRIPTION

       The true command returns a 0 (zero) exit value. The false command returns a nonzero exit value. These commands are usually used in input to
       shell commands.

NOTES

       The special built-in utility : (colon) is sometimes more efficient than the true command.

EXIT STATUS

       [Tru64 UNIX]  The nonzero value returned by the false command may vary from system to system.

EXAMPLES

       To construct a loop in a shell procedure, enter: while true do	   date      sleep 60 done

       This procedure displays the date and time once a minute.  To stop it, press the Interrupt key sequence.

SEE ALSO

       Commands:  csh(1), ksh(1), Bourne Shell sh(1b), POSIX shell sh(1p)

       Standards:  standards(5)

																	   true(1)