| How to pass unix declared variable into SQL and PL SQL block [message #644711] |
Sun, 15 November 2015 04:51  |
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
|
Member |
|
|
Hi Team,
I have one requirement as part automation. i have declared some variable at unix level in shell script. i want to use variable values in SQL command and PL/SQL blocks. for eg
code:
export owner=`echo $line|cut -d':' -f1`
export tname=`echo $line|cut -d':' -f2`
echo $owner
echo $tname
sqlplus -s '/as sysdba' $owner $tname<<EOF >abc.log
DECLARE
v_owner varchar2(15) := '$1';
v_tname varchar2(25) := '$2';
BEGIN
dbms_output.put_line('Value of Table Name:'||v_tname);
dbms_output.put_line('Value of Table Owner:'||v_owner);
end;
EOF
Please help me on this. how we can use variable inside pl/sql block.
thanks
|
|
|
|
|
|
| Re: How to pass unix declared variable into SQL and PL SQL block [message #644723 is a reply to message #644714] |
Mon, 16 November 2015 03:58  |
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
|
Member |
|
|
Hi Michel,
It's not working.
below is the full code that i have written for automation. I am reading input from a file that store list of table and its owner.
And i am checking the variable that is declared in unix level inside SQL and PL/SQL block.
#!/bin/ksh
while read line; do
owner=`echo $line|cut -d':' -f1`
tname=`echo $line|cut -d':' -f2`
echo $owner
echo $tname
sqlplus -s '/as sysdba'<<EOF >defrag.log
set serveroutput on
set echo on
set feedback on
select owner from dba_tables where table_name='$tname';
DECLARE
v_owner varchar2(15) := '$owner';
v_tname varchar2(25) := '$tname';
BEGIN
dbms_output.put_line('Value of Table Name:'||v_tname);
dbms_output.put_line('Value of Table Owner:'||v_owner);
end;
EOF
done < table_list.txt
on Unix prompt it is displaying values that is stored in $owner and $tname but in abc.log file it is showing below output
no rows selected
and at Unix Prompt
ABC
Table1
ABC
Table2
ABC
Table3
|
|
|
|
|
|
|
|
| Re: How to pass unix declared variable into SQL and PL SQL block [message #644726 is a reply to message #644725] |
Mon, 16 November 2015 04:26 |
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
|
Member |
|
|
Michel,
I changed the code with export but it is not working. the result is same as above.
#!/bin/ksh
while read line; do
export owner=`echo $line|cut -d':' -f1`
export tname=`echo $line|cut -d':' -f2`
echo $owner
echo $tname
sqlplus -s '/as sysdba' <<EOF >defrag.log
set serverout on
set echo on
set feedback on
select owner from dba_tables where table_name='$tname';
DECLARE
v_owner varchar2(15) := '$owner';
v_tname varchar2(25) := '$tname';
BEGIN
dbms_output.put_line('Value of Table Name:'||v_tname);
dbms_output.put_line('Value of Table Owner:'||v_owner);
end;
EOF
done < table_list.txt
and regarding Unix prompt output, all output showing in capital letter. its my typing mistake.
|
|
|
|
|
|
| Re: How to pass unix declared variable into SQL and PL SQL block [message #644731 is a reply to message #644730] |
Mon, 16 November 2015 05:49 |
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
|
Member |
|
|
$cat test.sh
#!/bin/ksh
while read line; do
export owner=`echo $line|cut -d':' -f1`
export tname=`echo $line|cut -d':' -f2`
echo $owner
echo $tname
sqlplus -s '/as sysdba' <<EOF
set serverout on
set echo on
set feedback on
select owner from dba_tables where table_name='$tname';
DECLARE
v_owner varchar2(15) := '$owner';
v_tname varchar2(25) := '$tname';
BEGIN
dbms_output.put_line('Value of Table Name:'||v_tname);
dbms_output.put_line('Value of Table Owner:'||v_owner);
end;
EOF
done < table_list.txt
$cat table_list.txt
MSC:MSC_SR_ASSIGNMENTS
MSC:MSC_FORECAST_UPDATES
MSC:MSC_ITEM_SUPPLIERS
$./test.sh
MSC
MSC_SR_ASSIGNMENTS
OWNER
--------------------------------------------------------------------------------
MSC
1 row selected.
MSC
MSC_FORECAST_UPDATES
OWNER
--------------------------------------------------------------------------------
MSC
1 row selected.
MSC
MSC_ITEM_SUPPLIERS
OWNER
--------------------------------------------------------------------------------
MSC
1 row selected.
no rows selected
|
|
|
|
|
|
|
|
| Re: How to pass unix declared variable into SQL and PL SQL block [message #644734 is a reply to message #644733] |
Mon, 16 November 2015 06:21 |
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
|
Member |
|
|
Thanks Michel, Now i am getting desired output.
$./test.sh
MSC
MSC_SR_ASSIGNMENTS
OWNER
--------------------------------------------------------------------------------
MSC
1 row selected.
Value of Table Name:MSC_SR_ASSIGNMENTS
Value of Table Owner:MSC
PL/SQL procedure successfully completed.
MSC
MSC_FORECAST_UPDATES
OWNER
--------------------------------------------------------------------------------
MSC
1 row selected.
Value of Table Name:MSC_FORECAST_UPDATES
Value of Table Owner:MSC
PL/SQL procedure successfully completed.
MSC
MSC_ITEM_SUPPLIERS
OWNER
--------------------------------------------------------------------------------
MSC
1 row selected.
Value of Table Name:MSC_ITEM_SUPPLIERS
Value of Table Owner:MSC
PL/SQL procedure successfully completed.
no rows selected
Value of Table Name:
Value of Table Owner:
PL/SQL procedure successfully completed.
thanks for your help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
