Sponsored Content
Top Forums Shell Programming and Scripting Need to run Oracle stored procedure from UNIX env Post 302751469 by prasson_ibm on Friday 4th of January 2013 03:24:27 AM
Old 01-04-2013
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:-

Code:
DECLARE 
  P_TRANSTYPE VARCHAR2(20);
  P_LOCATIONCODE VARCHAR2(10);
  P_SEQNO NUMBER;
  P_ERRORMSG VARCHAR2(32767);
 
BEGIN 
  P_TRANSTYPE := 'RBADDR';
  P_LOCATIONCODE := 'ODWH';
  P_SEQNO := NULL;
  P_ERRORMSG := NULL;
 
  AITBROKER.USP_GET_NEXT_SEQNO ( P_TRANSTYPE, P_LOCATIONCODE, P_SEQNO, P_ERRORMSG ); 
  DBMS_OUTPUT.PUT_LINE('SeqNo: ' || to_char(P_SEQNO));
END;

Can anyone help me to write a shell script for that, I have no exprience in calling stored procedure from unix.


Thanks
Prasoon

Last edited by Scrutinizer; 01-04-2013 at 04:27 AM.. Reason: code tags
 

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 Replies)
Discussion started by: kamil
2 Replies

2. 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 (6 Replies)
Discussion started by: superprogrammer
6 Replies

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

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

5. 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 (6 Replies)
Discussion started by: barani75
6 Replies

6. 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;... (6 Replies)
Discussion started by: barani75
6 Replies

7. 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 (0 Replies)
Discussion started by: jakSun8
0 Replies

8. Shell Programming and Scripting

run oracle procedure in unix scripts

for j in $(du -h $1| awk '{printf("%100-s \n",$2)}') do for a in $(ls -time $(find $j -name '*.txt') | awk '{printf("\n%s %s %s %s %s",$4,$7,$8,$10,$11)}') do echo "$a">output.txt done done exit 0 echo "Password : xxxxxx " > LOG/BGH_$3.out (0 Replies)
Discussion started by: utoptas
0 Replies

9. Shell Programming and Scripting

Run stored procedure from shell script

Hello all, I am trying to run stored procrdure from shell script which takes one argument. And also I want to verify in the script whether the script executed successfully. However the Stored procedure is not running from shell script. Manually if I run it update the data in the table. Can... (29 Replies)
Discussion started by: PriyaSri
29 Replies

10. Shell Programming and Scripting

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... (5 Replies)
Discussion started by: homer4all
5 Replies
uplevel(n)						       Tcl Built-In Commands							uplevel(n)

__________________________________________________________________________________________________________________________________________________

NAME
uplevel - Execute a script in a different stack frame SYNOPSIS
uplevel ?level? arg ?arg ...? _________________________________________________________________ DESCRIPTION
All of the arg arguments are concatenated as if they had been passed to concat; the result is then evaluated in the variable context indi- cated by level. Uplevel returns the result of that evaluation. If level is an integer then it gives a distance (up the procedure calling stack) to move before executing the command. If level consists of # followed by a number then the number gives an absolute level number. If level is omitted then it defaults to 1. Level cannot be defaulted if the first command argument starts with a digit or #. For example, suppose that procedure a was invoked from top-level, and that it called b, and that b called c. Suppose that c invokes the uplevel command. If level is 1 or #2 or omitted, then the command will be executed in the variable context of b. If level is 2 or #1 then the command will be executed in the variable context of a. If level is 3 or #0 then the command will be executed at top-level (only global variables will be visible). The uplevel command causes the invoking procedure to disappear from the procedure calling stack while the command is being executed. In the above example, suppose c invokes the command uplevel 1 {set x 43; d} where d is another Tcl procedure. The set command will modify the variable x in b's context, and d will execute at level 3, as if called from b. If it in turn executes the command uplevel {set x 42} then the set command will modify the same variable x in b's context: the procedure c does not appear to be on the call stack when d is executing. The command ``info level'' may be used to obtain the level of the current procedure. Uplevel makes it possible to implement new control constructs as Tcl procedures (for example, uplevel could be used to implement the while construct as a Tcl procedure). namespace eval is another way (besides procedure calls) that the Tcl naming context can change. It adds a call frame to the stack to rep- resent the namespace context. This means each namespace eval command counts as another call level for uplevel and upvar commands. For example, info level 1 will return a list describing a command that is either the outermost procedure call or the outermost namespace eval command. Also, uplevel #0 evaluates a script at top-level in the outermost namespace (the global namespace). SEE ALSO
namespace(n), upvar(n) KEYWORDS
context, level, namespace, stack frame, variables Tcl uplevel(n)
All times are GMT -4. The time now is 10:09 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy