i want to pass the value of a unix variable to a pl/sql block. I dont know how to go about it.
My code is
for all the files in the directory, ( say $i as the file name )
sqlplus -s << eof
no number:= 0;
no := run_owb_mapping('CMDP_RDEV','LOC_PROC_FLOW','PROCESS','PROCESS_HOTEL_DATA',CUSTOM_PARAMS='FILE_NAME=$i');
In the above example, i want the file name ( if x.txt ) to be passed to the procedure call as :
no := run_owb_mapping('CMDP_RDEV','LOC_PROC_FLOW','PROCESS','PROCESS_HOTEL_DATA',CUSTOM_PARAMS='FILE_NAME=x.txt');
I think you're on the right track. But instead, try something like the following:
'FILE_NAME=''' || $i || '');
(That's 3 single quotes afer the = sign, and 2 single quotes before the closing parenthesis). (i.e. the $i isn't inside of any single quotes)
thank you for the reply..
well that will not work.. since values concatenated to strings cannot be passed as parameters.
for the file Oct_2005.csv, the following was the output
no := run_owb_mapping('CMDP_RDEV','LOC_PROC_FLOW','PROCESS','PROCESS_HOTEL_DATA'
ERROR at line 4:
ORA-06550: line 4, column 115:
PLS-00201: identifier 'OCT_2005.CSV' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
Another approach is to take all your SQL and have Unix generate it on the fly and spool it to a file (and at that time it does the var substitution).
for $i ...
echo "select sysdate as $i from dual;" >mysql.sql
echo "exit;" >>mysql.sql
sqlplus -s u/p@i @mysql.sql
not plsql block but executed single sql statement -
echo "enter name of table whose tablespace needs to be found"
sqlplus -S woodward/woodward<< EOF
set heading off feedback off verify off
select tablespace_name from tabs where table_name=upper('$a');