| passing unix vairable to a PL SQL block [message #361022] |
Mon, 24 November 2008 21:02  |
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  |
 |
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}') ).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
