Home » Infrastructure » Unix » Passing Parameter from PL/SQL to Unix
Passing Parameter from PL/SQL to Unix [message #281277] Fri, 16 November 2007 05:42 Go to next message
veerendranayak
Messages: 5
Registered: November 2007
Location: Pune
Junior Member

I am not able to catch the out variable from procedure back to Unix.
Only out variable should be assigned to the Unix variable and not the things printed by DBMS_OUTPUT in procedure.
Can you please help me in this.

Thanx in advance
Re: Passing Parameter from PL/SQL to Unix [message #281286 is a reply to message #281277] Fri, 16 November 2007 06:01 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That's not not easily possible with the shell-script-backtick approach.

There might be a roundabout way to write stuff to spool-files or grepping just specific lines from the output, but I would suggest having a look at using a scripting language with database support, like PERL instead.

Examples

It will make things easier in the long run.
Re: Passing Parameter from PL/SQL to Unix [message #281329 is a reply to message #281286] Fri, 16 November 2007 07:33 Go to previous messageGo to next message
veerendranayak
Messages: 5
Registered: November 2007
Location: Pune
Junior Member

Thanx for the reply.

I feel that is an overhead to get Perl in scripts.

Is any other way to achieve the same

Re: Passing Parameter from PL/SQL to Unix [message #281336 is a reply to message #281329] Fri, 16 November 2007 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
grep, sed, awk... parse the whole result.

Regards
Michel
Re: Passing Parameter from PL/SQL to Unix [message #281337 is a reply to message #281329] Fri, 16 November 2007 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or turn out the ouput and put the dbms_output in a spool then print it.

Regards
Michel
Re: Passing Parameter from PL/SQL to Unix [message #281341 is a reply to message #281337] Fri, 16 November 2007 08:05 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Probably better to do it in pearl, but here is an option

Create a function
SQL>  CREATE OR REPLACE FUNCTION get_number112 RETURN INTEGER
  2  IS
  3  BEGIN
  4  return 112;
  5  END;
  6  /
Funktion skapad


Create Shell script
CONNSTRING=user/pwd@instance

value=`sqlplus -S $CONNSTRING <<END
  select get_number112 from dual;
  exit;
EOF`
echo "my number is:" $value


Execute Shell script
[/export/home/hpl]. test.sh
my number is: GET_NUMBER112 ------------- 112


[Updated on: Fri, 16 November 2007 08:06]

Report message to a moderator

Re: Passing Parameter from PL/SQL to Unix [message #281342 is a reply to message #281341] Fri, 16 November 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reread OP post: the problem is what if there is dbms_output in addition and you don't want dbms_output output just function result in the variable.

Regards
Michel
Re: Passing Parameter from PL/SQL to Unix [message #281449 is a reply to message #281342] Sat, 17 November 2007 00:01 Go to previous messageGo to next message
veerendranayak
Messages: 5
Registered: November 2007
Location: Pune
Junior Member

Thats exactly what I want..I want to avoid the result coming from dbms_output but at the same time I want to catch the OUT variable in Unix. Now If I use the approach suggested by tahpush, I will end up assinging the result printed by DBMS_OUTPUT to the variable "value".

Re: Passing Parameter from PL/SQL to Unix [message #281460 is a reply to message #281449] Sat, 17 November 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I already answered in my 2 previous posts.

Regards
Michel
Re: Passing Parameter from PL/SQL to Unix [message #281463 is a reply to message #281460] Sat, 17 November 2007 01:29 Go to previous messageGo to next message
veerendranayak
Messages: 5
Registered: November 2007
Location: Pune
Junior Member

Thanx for the reply

Spooling will result into everything(DBMS_OUTPUT) in the spooled file.

Then by using grep I will get desired value of OUT variable, but is this the only way to do it??..
Re: Passing Parameter from PL/SQL to Unix [message #281465 is a reply to message #281463] Sat, 17 November 2007 01:35 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I give you another way: set termout off during execution containing the dbms_output then turn termout on and display final the result.
Then you will have all in the spool file and only the result in the variable.

Regards
Michel
Previous Topic: [Telnet] Unable to start/complete the build
Next Topic: create unix id with write permission & right owner
Goto Forum:
  


Current Time: Thu Mar 28 11:39:21 CDT 2024