02-19-2008
Passing the value of Out parm in SP to UNIX Shell Script
I have a simple procedure which accepts two input parms and returns and output count:
CREATE OR REPLACE Procedure GET1 ( IN_FOLDER IN VARCHAR2,
IN_SUBJECT_AREA IN VARCHAR2,
OUT_CNT IN OUT VARCHAR2
)
AS
BEGIN
select count(*)
into OUT_CNT
from REP_WFLOW_RUN
where subject_area = IN_FOLDER
and workflow_name = IN_SUBJECT_AREA
and end_time is null
order by end_time desc;
END GET1 ;
/
If I execute it in SQL*PLUS, with following code:
SET SERVEROUTPUT ON
DECLARE
v_folder_name varchar2(20) ;
v_wf_name varchar2(20) ;
v_cnt varchar2(20) ;
BEGIN
v_folder_name := '~user1' ;
v_wf_name := 'w_Workflow2';
GET1(v_folder_name, v_wf_name,v_cnt) ;
DBMS_OUTPUT.PUT_LINE('Folder:' || v_folder_name || ' Wf:' || v_wf_name ||' cnts:' || v_cnt);
END;
/
It returns corrrect value
Folder:~user1 Wf:w_Workflow2 cnts:1
Now how can I get this return value from SP into a UNIX shell Script variable??
I tried following shell script:
wf_name="w_Workflow2"
sub_area="~user1"
echo "wf name :" $wf_name
echo "sub_area:" $sub_area
echo "REP User:" $REP_READ_ONLY_USR
echo "REP PASS:" $REP_READ_ONLY_PASS
v_cnt=6
return_cc=`sqlplus << EOINPUT
##sqlplus << EOINPUT
$REP_READ_ONLY_USR/$REP_READ_ONLY_PASS@dadev
set echo off
set feedback off
set serveroutput on
set verify off
whenever sqlerror exit 1
@/etlapps/SrcFiles/SPD/exec_sp.sql $sub_area $wf_name $v_cnt
exit
##EOINPUT
EOINPUT`
echo "out cnt:" $v_cnt
echo "return cc:" $return_cc
I don't get the value in shell script.
I also tried to use a BIND Variable in my anonymous SQL that runs the SP as follows:
SET SERVEROUTPUT ON
DECLARE
v_folder_name varchar2(20) ;
v_wf_name varchar2(20) ;
var v_cnt varchar2(20) := $v_cnt ;
BEGIN
v_folder_name := '~user1' ;
v_wf_name := 'w_Workflow2';
GET1(v_folder_name, v_wf_name,:v_cnt) ;
DBMS_OUTPUT.PUT_LINE('Folder:' || v_folder_name || ' Wf:' || v_wf_name ||' cnts:' || v_cnt);
END;
/
I get error that Bind variable $v_cnt not declared.
What am I doing wrong????
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
I'm a newbie in scripting attempting to create a script where i can submit a job (in maestro/tivoli) with parameters,in maestro CLI i can do that no problem,i'm thinking about creating a script that will accept input while the script is executing, more like below structure:
exec the script
-... (1 Reply)
Discussion started by: Kirojin
1 Replies
2. Shell Programming and Scripting
Hi,
I have an existing cron job like the following.
15 5 * * * /appl/scripts/MyScript.sh 2>/dev/null >/dev/null
and this works just fine.
Now I had to modify the script and now it requires an input parm as 2.
I had modified the cron entry so that the script can run now with the... (12 Replies)
Discussion started by: bheemsen
12 Replies
3. Shell Programming and Scripting
Hi Folks...
I am using a ksh script to submit the at command to run a shell script for immediate execution. The shell script requries 1 parameter.
Command in the script is
at -m -f $EXE_DIR/process_server.sh $START_TIME $DB_NAME
where START_TIME=now and DB_NAME= tnsname of Oracle... (1 Reply)
Discussion started by: island360
1 Replies
4. Shell Programming and Scripting
Hi All ,
I need to call a script runscript_B.sh on server A, the runscript_B.sh script locating in server B.
The runscript_B.sh in calls another script runscript_A on server A itself.
it seend, i need to be connect from Server A to Server B using ssh.
I have tryed like this in... (3 Replies)
Discussion started by: koti_rama
3 Replies
5. UNIX for Dummies Questions & Answers
I have an Oracle concurrent program that I'm passing a parameter to a unix shell script. An example value of the Oracle parameter is PO_TOP. The Oracle parameter represents the unix env var PO_TOP, meaning, on the unix side there is env var called PO_TOP (ex value: /oradev/apps/po/11.0.3/). My... (7 Replies)
Discussion started by: Mark_Wright
7 Replies
6. Shell Programming and Scripting
Hi Dudes :)
I want a unix shell script to pass value to SQL stored procedure.
Below is the procedure
declare
res varchar2(10);
begin
odm_load_check('PRE_SANITY',res);
dbms_output.put_line(res);
end;
select * from error_log;
truncate table error_log;
select * from test; (1 Reply)
Discussion started by: shirdi
1 Replies
7. Shell Programming and Scripting
I need to run a local shell script on a remote machine. I am able to achieve that by executing the command
> ssh -qtt user@host < test.sh
However, when I try to pass arguments to test.sh it fails.
Any pointers would be appreciated. (7 Replies)
Discussion started by: Sree10
7 Replies
8. UNIX for Dummies Questions & Answers
I have a shell script (.sh) and I want to pass a parameter value to the awk command but I am getting exception, please assist.
diff=$1$2.diff
id=$2 new=new_$diff
echo "My id is $1"
echo "I want to sync for user account $id"
##awk command I am using is as below
cat $diff |... (1 Reply)
Discussion started by: Sarita Behera
1 Replies
9. Shell Programming and Scripting
I have users that print files to selected printers. Instead of creating one function for each printer I would like to have just one and passing the files to print as well as the wanted printer. The following code does not work, of course.
I'm expecting that $1 is the list of files to be printed... (6 Replies)
Discussion started by: emare
6 Replies
10. UNIX and Linux Applications
I m passing a variable stringg from Unix shell which has value 'Good Day' to ansible and from ansible to a second shell script where it print only Good instead of 'Good Day'
passing the variable stringg from unix shell script1.sh
echo $stringg
ansible-playbook install.yml -i... (1 Reply)
Discussion started by: mohtashims
1 Replies
LEARN ABOUT PHP
oci_fetch_object
OCI_FETCH_OBJECT(3) OCI_FETCH_OBJECT(3)
oci_fetch_object - Returns the next row from a query as an object
SYNOPSIS
object oci_fetch_object (resource $statement)
DESCRIPTION
Returns an object containing the next result-set row of a query. Each attribute of the object corresponds to a column of the row. This
function is typically called in a loop until it returns FALSE, indicating no more rows exist.
For details on the data type mapping performed by the OCI8 extension, see the datatypes supported by the driver
PARAMETERS
o $statement
-A valid OCI8 statement identifier created by oci_parse(3) and executed by oci_execute(3), or a REF CURSOR statement identifier.
RETURN VALUES
Returns an object. Each attribute of the object corresponds to a column of the row. If there are no more rows in the $statement then FALSE
is returned.
Any LOB columns are returned as LOB descriptors.
DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment vari-
ables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.
Oracle's default, non-case sensitive column names will have uppercase attribute names. Case-sensitive column names will have attribute
names using the exact column case. Use var_dump(3) on the result object to verify the appropriate case for attribute access.
Attribute values will be NULL for any NULL data fields.
EXAMPLES
Example #1
oci_fetch_object(3) example
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description VARCHAR2(30));
INSERT INTO mytab (id, description) values (1, 'Fish and Chips');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_object($stid)) != false) {
// Use upper case attribute names for each standard Oracle column
echo $row->ID . "<br>
";
echo $row->DESCRIPTION . "<br>
";
}
// Output is:
// 1
// Fish and Chips
oci_free_statement($stid);
oci_close($conn);
?>
Example #2
oci_fetch_object(3) with case sensitive column names
<?php
/*
Before running, create the table with a case sensitive column name:
CREATE TABLE mytab (id NUMBER, "MyDescription" VARCHAR2(30));
INSERT INTO mytab (id, "MyDescription") values (1, 'Iced Coffee');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, "MyDescription" FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_object($stid)) != false) {
// Use upper case attribute names for each standard Oracle column
echo $row->ID . "<br>
";
// Use the exact case for the case sensitive column name
echo $row->MyDescription . "<br>
";
}
// Output is:
// 1
// Iced Coffee
oci_free_statement($stid);
oci_close($conn);
?>
Example #3
oci_fetch_object(3) with LOBs
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_object($stid)) != false) {
echo $row->ID . "<br>
";
// The following will output the first 11 bytes from DESCRIPTION
echo $row->DESCRIPTION->read(11) . "<br>
";
}
// Output is:
// 1
// A very long
oci_free_statement($stid);
oci_close($conn);
?>
SEE ALSO
oci_fetch(3), oci_fetch_all(3), oci_fetch_assoc(3), oci_fetch_array(3), oci_fetch_row(3).
PHP Documentation Group OCI_FETCH_OBJECT(3)