Home » SQL & PL/SQL » SQL & PL/SQL » value from Procedure
value from Procedure [message #346769] Tue, 09 September 2008 12:20 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Oracle 9.2.0.2
Window Professional 2002

I have insert statement, in which one column value need to come from the output value of one stored procedure..

What would be the best way to do this.. I have tried something like this..

declare
var out_status varchar2(30);
begin
exec sp_tester(value_in, :out_status);
insert into tab1
(col1,col2,col3) values (value1,value2,var);
end;


Any small hint would be appreciated...
Re: value from Procedure [message #346775 is a reply to message #346769] Tue, 09 September 2008 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always COPY AND PASTE your session.

"exec" is a SQL*Plus command not a PL/SQL statement, remove it.
Same thing for "var".

Regards
Michel
Re: value from Procedure [message #346777 is a reply to message #346769] Tue, 09 September 2008 12:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Where do value1 and value2 come from?
Re: value from Procedure [message #346794 is a reply to message #346777] Tue, 09 September 2008 13:14 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

They are hard coded values...
Re: value from Procedure [message #346797 is a reply to message #346794] Tue, 09 September 2008 13:19 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Then this anonymous block will fail.
Re: value from Procedure [message #346798 is a reply to message #346797] Tue, 09 September 2008 13:21 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Can u give small hint towards success...
Re: value from Procedure [message #346802 is a reply to message #346798] Tue, 09 September 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We already give many.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: value from Procedure [message #346814 is a reply to message #346802] Tue, 09 September 2008 14:36 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member


SQL> declare
  2     procedure sp_f_get_user(PAYMENT_HISTORY varchar2) is
  3  begin
  4   sp_f_get_user('PAYMENT_HISTORY');
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06550: line 6, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin function package pragma procedure form


Any help on this Error....
Re: value from Procedure [message #346817 is a reply to message #346769] Tue, 09 September 2008 14:57 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Trying to learn programming by trial & error can be an exercise it futility, because compilers are VERY picky about syntax.

You need to actually read the fine manuals that Michel suggested.
Throw random characters into file won't work most of the time.


http://asktom.oracle.com has many fine working code examples.
Re: value from Procedure [message #347643 is a reply to message #346817] Fri, 12 September 2008 09:17 Go to previous message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Just for the future visitors, who will be looking for the answer for the same kind of error..

DECLARE
    INPUT     varchar2(20);
    RESULT    number;
BEGIN
    INPUT := 'user';    
     sp_f_get_user(INPUT,result);
    DBMS_OUTPUT.put_line (RESULT);
    -- put your insert statement here ..insert
end;


Thanks for everybody valuable time.
Previous Topic: How to get error number for exception_init
Next Topic: Insert multiple records into a table
Goto Forum:
  


Current Time: Mon Dec 05 19:11:17 CST 2016

Total time taken to generate the page: 0.25394 seconds