Home » SQL & PL/SQL » SQL & PL/SQL » Execute Procedure (10g)
Execute Procedure [message #427122] Wed, 21 October 2009 04:34 Go to next message
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
Member
create or replace procedure test123(id in varchar2,pwd in varchar2,fcl_yr in varchar2,resultset out types.cursorType)
as
begin
open resultset for
select l.loginid,l.fulldetails,l.employeeid,e.firstname||' '||e.lastname,l.groupid,l.password,l.email,l.accessblocked,e.locationid,lo.locationname,lo.typeid,lt.typename,fcl_yr
from login l,employees e,locations lo,locationtype lt
where l.employeeid=e.employeeid
and e.locationid=lo.locationid
and lo.typeid=lt.typeid
and l.loginid=id
and l.password=pwd;

exception
when others then
dbms_output.put_line('No such user exist');
end;
/



exec getuserprofile('asd','asd','12/23',:result)

returns error:::: variable result not declared....
what shud i do....
Re: exexute procedure [message #427123 is a reply to message #427122] Wed, 21 October 2009 04:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Declare a variable for the out parameter to go into.

This procedure doesn't look like it's designed to be called as a standalone from SQL*Plus.
Re: exexute procedure [message #427125 is a reply to message #427123] Wed, 21 October 2009 04:50 Go to previous messageGo to next message
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
Member
how to show me an example
Re: exexute procedure [message #427126 is a reply to message #427125] Wed, 21 October 2009 04:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DECLARE
  v_resultset  types.cursorType;
BEGIN
  getuserprofile('asd','asd','12/23',v_resultset);
END;
/


This doesn't actually do anything with the results, but you've given us no idea of what you're trying to achieve, so I don't feel terribly guilty about that.
Re: exexute procedure [message #427127 is a reply to message #427122] Wed, 21 October 2009 05:00 Go to previous messageGo to next message
tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka
Senior Member
Is your Procedure Compiled?
please check is first. then reply again.

Tamzidul Amin.
Re: exexute procedure [message #427128 is a reply to message #427126] Wed, 21 October 2009 05:02 Go to previous messageGo to next message
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
Member
i want to return the cursor to the front end and work on the record in the cursor from front end ....i need to pass 3 values and return the record in the cursor....
Re: exexute procedure [message #427129 is a reply to message #427128] Wed, 21 October 2009 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In which case you just call this procedure from your front end. What you pass in to the call as the OUT parameter will depend entirely on the front end that you're using.
Re: Execute Procedure [message #427130 is a reply to message #427122] Wed, 21 October 2009 05:07 Go to previous messageGo to next message
tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka
Senior Member
please put your code sqlplus & create a spool file.
After that u will post those spool file.
Re: Execute Procedure [message #427143 is a reply to message #427130] Wed, 21 October 2009 05:40 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given that he's not going to be calling the procedure from SQL*Plus, and the error is caused by trying to do so, I don't think this would be a productive use of the OP's time.
Previous Topic: Same query returns differenent result sets in oracle 9i and 10g
Next Topic: How to insert a txt file into a Blob Column?
Goto Forum:
  


Current Time: Fri Dec 09 06:15:00 CST 2016

Total time taken to generate the page: 0.14667 seconds