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_bind_array_by_name
OCI_BIND_ARRAY_BY_NAME(3) OCI_BIND_ARRAY_BY_NAME(3)
oci_bind_array_by_name - Binds a PHP array to an Oracle PL/SQL array parameter
SYNOPSIS
bool oci_bind_array_by_name (resource $statement, string $name, array &$var_array, int $max_table_length, [int $max_item_length = -1],
[int $type = SQLT_AFC])
DESCRIPTION
Binds the PHP array $var_array to the Oracle placeholder $name, which points to an Oracle PL/SQL array. Whether it will be used for input
or output will be determined at run-time.
PARAMETERS
o $statement
- A valid OCI statement identifier.
o $name
- The Oracle placeholder.
o $var_array
- An array.
o $max_table_length
- Sets the maximum length both for incoming and result arrays.
o $max_item_length
- Sets maximum length for array items. If not specified or equals to -1, oci_bind_array_by_name(3) will find the longest element
in the incoming array and will use it as the maximum length.
o $type
- Should be used to set the type of PL/SQL array items. See list of available types below:
o SQLT_NUM - for arrays of NUMBER.
o SQLT_INT - for arrays of INTEGER (Note: INTEGER it is actually a synonym for NUMBER(38), but SQLT_NUM type won't work in
this case even though they are synonyms).
o SQLT_FLT - for arrays of FLOAT.
o SQLT_AFC - for arrays of CHAR.
o SQLT_CHR - for arrays of VARCHAR2.
o SQLT_VCS - for arrays of VARCHAR.
o SQLT_AVC - for arrays of CHARZ.
o SQLT_STR - for arrays of STRING.
o SQLT_LVC - for arrays of LONG VARCHAR.
o SQLT_ODT - for arrays of DATE.
RETURN VALUES
Returns TRUE on success or FALSE on failure.
EXAMPLES
Example #1
oci_bind_array_by_name(3) example
<?php
$conn = oci_connect("hr", "hrpwd", "localhost/XE");
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$create = "CREATE TABLE bind_example(name VARCHAR(20))";
$stid = oci_parse($conn, $create);
oci_execute($stid);
$create_pkg = "
CREATE OR REPLACE PACKAGE ARRAYBINDPKG1 AS
TYPE ARRTYPE IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
PROCEDURE iobind(c1 IN OUT ARRTYPE);
END ARRAYBINDPKG1;";
$stid = oci_parse($conn, $create_pkg);
oci_execute($stid);
$create_pkg_body = "
CREATE OR REPLACE PACKAGE BODY ARRAYBINDPKG1 AS
CURSOR CUR IS SELECT name FROM bind_example;
PROCEDURE iobind(c1 IN OUT ARRTYPE) IS
BEGIN
-- Bulk Insert
FORALL i IN INDICES OF c1
INSERT INTO bind_example VALUES (c1(i));
-- Fetch and reverse
IF NOT CUR%ISOPEN THEN
OPEN CUR;
END IF;
FOR i IN REVERSE 1..5 LOOP
FETCH CUR INTO c1(i);
IF CUR%NOTFOUND THEN
CLOSE CUR;
EXIT;
END IF;
END LOOP;
END iobind;
END ARRAYBINDPKG1;";
$stid = oci_parse($conn, $create_pkg_body);
oci_execute($stid);
$stid = oci_parse($conn, "BEGIN arraybindpkg1.iobind(:c1); END;");
$array = array("one", "two", "three", "four", "five");
oci_bind_array_by_name($stid, ":c1", $array, 5, -1, SQLT_CHR);
oci_execute($stid);
var_dump($array);
?>
PHP Documentation Group OCI_BIND_ARRAY_BY_NAME(3)