ksh and Oracle stored procedure output in logfile Post: 302931703

Sponsored Content

Top Forums Shell Programming and Scripting ksh and Oracle stored procedure output in logfile Post 302931703 by homer4all on Wednesday 14th of January 2015 07:15:15 PM

01-14-2015

Registered User

ksh and Oracle stored procedure output in logfile

Friends,
I pass some runtime arguments (date, number) through ksh script to Oracle procedure, use input value and pass it on to procedure.

Oracle procedure gets input value, run query and logs everything in the logfile.

I'm facing with couple of challenges

1. Even though I pass all required parameter for procedure variable still script run complains about wrong number or type of arguments.

2. How to get all rows stored into logfile

3. Is this correct approach? looks like getting Oracle stored procedure output in logfile is really challenging?

Query is SELECT query but connecting KSH and Oracle seems challenging.

Code:

#!/bin/ksh

$1=10;               #runtime input value
$2=30;               #runtime input value
$3=50;               #runtime input value
$4='20150114'; #yyyymmdd input value

echo "running Oracle procedure"
(
$ORACLE_HOME/bin/sqlplus -s -l

set linesize 100

var v_empid  number,
var v_name   varchar2;
var v_last   varchar2;
var v_dt     varchar2;

alter session set nls_date_format = 'mm-dd-yyyy hh24:mi:ss';
exec pkg.proc($1, $2, $3, $4,:v_empid, :v_name, :v_last, :v_dt);

) > $logFile

Code:

=== Oracle package/procedure ===
CREATE OR REPLACE PACKAGE pkg
AS

   procedure proc (lFirst    IN number,
                   lSecond   IN number,
                   lThird    IN number,
                   lFour     IN date,
                     v_empid   OUT number,
                     v_name    OUT varchar2,
                     v_last    OUT varchar2,
                     v_dt      OUT date);

END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS

PROCEDURE proc (lFirst    IN number,
                lSecond   IN number,
                lThird    IN number,
                lFour     IN date,
                  v_empid   OUT number,
                  v_name    OUT varchar2,
                  v_last    OUT varchar2,
                  v_dt      OUT date)
  
IS
BEGIN

SELECT dt, emp_id, first_name, last_name
INTO v_dt, v_empid, v_name, v_last
FROM employees
WHERE emp_id in (lFirst, lSecond)
AND dt >= to_date(lFour, 'yyyymmdd') - 1
AND date < date + INTERVAL '(lThird)' MINUTE;

END proc;

END pkg;
/

homer4all

View Public Profile for homer4all

Find all posts by homer4all

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 PLAN9

times

times(1)							   User Commands							  times(1)

NAME

       times - shell built-in function to report time usages of the current shell

SYNOPSIS

   sh
       times

   ksh
       times

DESCRIPTION

   sh
       Print the accumulated user and system times for processes run from the shell.

   ksh
       Print the accumulated user and system times for the shell and for processes run from the shell.

       On this man page, ksh(1) commands that are preceded by one or two * (asterisks) are treated specially in the following ways:

       1.  Variable assignment lists preceding the command remain in effect when the command completes.

       2.  I/O redirections are processed after variable assignments.

       3.  Errors cause a script that contains them to abort.

       4.  Words,  following a command preceded by ** that are in the format of a variable assignment, are expanded with the same rules as a vari-
	   able assignment. This means that tilde substitution is performed after the = sign and word splitting and file name generation  are  not
	   performed.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Availability		     |SUNWcsu			   |
       +-----------------------------+-----------------------------+

SEE ALSO

       ksh(1), sh(1), time(1), attributes(5)

SunOS 5.10							    15 Apr 1994 							  times(1)