Home » Infrastructure » Unix » passing unix vairable to a PL SQL block
passing unix vairable to a PL SQL block [message #361022] Mon, 24 November 2008 21:02 Go to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member

This PL SQL block is in shell script
filename and F_DATE is a variable in unix shell script. When I pass to PL/SQL block in shell script. It does not get pass, it becomes null. How do we pass it. It works fine in sql script but not in PL SQL block. Please help
filename = abcd
F_DATE=2008-12-20

${ORACLE_HOME}/bin/sqlplus -s -s -s -s -s -s -s -s -s -s -s -s -s -s -s -s -s -s -s -s userid/password@db<<-EOF
WHENEVER SQLERROR EXIT 1
SET HEADING OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 LINESIZE 120 SERVEROUTPUT ON SIZE 1000000
DECLARE
v_count number;
v_status varchar2(50);
v_str varchar2(255);

BEGIN

select param_string1 into v_status from tableA
where cust='000001'
and name='DATA';

IF v_status = 'Y'
THEN
UPDATE tableB
SET status = 'GOOD',
edate=SYSDATE
WHERE lower(filename) = lower('${filename}')
and to_char(adate,'YYYY-MM-DD')='$F_DATE';
COMMIT;
ELSE
UPDATE tableB
SET status = 'FAILED'
WHERE lower(filename) = lower('${filename}')
and to_char(adate,'YYYY-MM-DD')='$F_DATE';
COMMIT;
END IF;
end;
/
EOF
Re: passing unix vairable to a PL SQL block [message #361027 is a reply to message #361022] Mon, 24 November 2008 21:36 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
After 113 posts, you really should know by now how to format your posts.

As for your script, you should remove the spaces around the '=' when you define filename (ie. filename=abcd). In addition, try to escape the '$' when referencing it in the SQL (ie. lower('\\${filename}') ). Depending on your shell, it may only be one backslash (ie. lower('\${filename}') ).
Previous Topic: how to find Forms version installed on Unix box?
Next Topic: capture PL/SQL o/p in unix script
Goto Forum:
  


Current Time: Thu Mar 28 06:50:40 CDT 2024