Home » Infrastructure » Unix » Procedure with input/output params called in Unix (10g)
Procedure with input/output params called in Unix [message #444782] Wed, 24 February 2010 03:24 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
hi,

I have a procedure which accepts 2 INPUT parameters and has 5 OUTPUT parameters

CREATE OR REPLACE PROCEDURE My_Procedure
(i_name        IN VARCHAR2,
 i_address     IN VARCHAR2,
 i_id          IN NUMBER,
 o_new_id      OUT NUMBER,
 o_new_address OUT VARCHAR2,
 o_new_name    OUT VARCHAR2,
 error_number  OUT NUMBER,
 error_msg     OUT VARCHAR2) IS
begin 
 ....
 
end; 


How do I call this procedure in Unix? And also I'd like to get the OUTPUT parameter values and save them to a text file as comma delimited.

Thank you for your help.
Re: Procedure with input/output params called in Unix [message #444785 is a reply to message #444782] Wed, 24 February 2010 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
save them to a text file as comma delimited.

Use dbms_output.

Regards
Michel
Re: Procedure with input/output params called in Unix [message #444815 is a reply to message #444782] Wed, 24 February 2010 05:48 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
I actually have this:

#!/bin/sh
# set environment
. /set_env

i_name=$1
i_address=$2
i_id=$3

retval=`sqlplus  $CONN_DETAILS <<eof
set serveroutput on
whenever sqlerror exit -1
var o_new_id number
var o_new_address varchar2(500)
var o_new_name varchar2(500)
var error_number number
var error_msg varchar2(500)
exec My_Procedure('$1','$2',$3,:o_new_id,:o_new_address,:o_new_name,:error_number,:error_msg);
eof`
echo $retval > stat_file


When I see the stat_file, I get:

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 24 03:37:58 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 333|Albert Ng|101 BrookSide||| PL/SQL procedure successfully completed. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

In the above text, I just want to save the text in green to the stat_file.. not all Oracle displays.
Please help..

[Updated on: Wed, 24 February 2010 05:53]

Report message to a moderator

Re: Procedure with input/output params called in Unix [message #444821 is a reply to message #444815] Wed, 24 February 2010 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "sqlplus -s"
and "set feedback off" in the script.

Regards
Michel

[Updated on: Wed, 24 February 2010 05:59]

Report message to a moderator

Re: Procedure with input/output params called in Unix [message #444854 is a reply to message #444821] Wed, 24 February 2010 09:14 Go to previous message
beetel
Messages: 96
Registered: April 2007
Member
IT WORKED!!! Thanks much!!
Previous Topic: script to report contents of trace files
Next Topic: Search word between two strings
Goto Forum:
  


Current Time: Thu Mar 28 04:39:18 CDT 2024