Home » Infrastructure » Unix » How to pass unix declared variable into SQL and PL SQL block (Oracle Database 11.2.0.4,Operating System: AIX 7)
How to pass unix declared variable into SQL and PL SQL block [message #644711] Sun, 15 November 2015 04:51 Go to next message
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 #644714 is a reply to message #644711] Sun, 15 November 2015 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.
Indent the code.

Try this
sqlplus -s '/as sysdba' <<EOF >abc.log
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

[Updated on: Sun, 15 November 2015 13:20]

Report message to a moderator

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 Go to previous messageGo to next message
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 #644724 is a reply to message #644723] Mon, 16 November 2015 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You removed "export" so it can't work.

Re: How to pass unix declared variable into SQL and PL SQL block [message #644725 is a reply to message #644723] Mon, 16 November 2015 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
it is showing below output

no rows selected

and at Unix Prompt

ABC
Table1
ABC
Table2
ABC
Table3


Oracle accounts and are usually in UPPER case.

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 Go to previous messageGo to next message
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 #644730 is a reply to message #644726] Mon, 16 November 2015 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Copy and paste your session, I am not behind your shoulders to see what happens on your screen.
Remove ">defrag.log" and add "cat table_list.txt'.

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 Go to previous messageGo to next message
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 #644732 is a reply to message #644731] Mon, 16 November 2015 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something wrong in your session.
You should have some messages starting with "Value of Table ".

Re: How to pass unix declared variable into SQL and PL SQL block [message #644733 is a reply to message #644732] Mon, 16 November 2015 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ok I see.
The PL/SQL block should end with a "/" alone in a line otherwise it is not executed.

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 Go to previous message
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.
Previous Topic: Solaris version that uses libm.so.2
Next Topic: oracle function invoked using shell script
Goto Forum:
  


Current Time: Thu Mar 28 03:50:39 CDT 2024