Home » SQL & PL/SQL » SQL & PL/SQL » function & procedure
function & procedure [message #296681] Mon, 28 January 2008 11:59 Go to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
Hallo
I'm a PL/SQL newbie and I've some questions for you all.
I have to write a function which gives a cursor as output.
In the body of the function I insert some rows in a temporary table. Before i return the cursor i declare it as follows:

CURSOR c1 IS
SELECT * FROM temporary_table
FOR SELECT;

now:
can I create a procedure instead of a function?
Do I have to open the cursor before I return it?
Do you think it would be better to use SYS_REFCURSOR instead of CURSOR?

Hope you could help me.

Thank you.
Re: function & procedure [message #296682 is a reply to message #296681] Mon, 28 January 2008 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Before i return the cursor i declare it as follows:
CURSOR c1 IS
SELECT * FROM temporary_table
FOR SELECT;

Syntax is wrong.

Quote:
can I create a procedure instead of a function?

Yes

Quote:
Do I have to open the cursor before I return it?

Yes

Quote:
Do you think it would be better to use SYS_REFCURSOR instead of CURSOR?

You can't return a cursor, you return a reference to a cursor.

Regards
Michel
Re: function & procedure [message #296684 is a reply to message #296682] Mon, 28 January 2008 12:18 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
Michel Cadot wrote on Mon, 28 January 2008 19:13
Quote:
Before i return the cursor i declare it as follows:
CURSOR c1 IS
SELECT * FROM temporary_table
FOR SELECT;

Syntax is wrong.

Quote:
can I create a procedure instead of a function?

Yes

Quote:
Do I have to open the cursor before I return it?

Yes

Quote:
Do you think it would be better to use SYS_REFCURSOR instead of CURSOR?

You can't return a cursor, you return a reference to a cursor.

Regards
Michel




thank you.

the right sintax would be
CURSOR c1 IS
SELECT * FROM temporary_table;
?

If i create a procedure can I return a cursor?

So the right steps would be
1) declare the cursor
2) open the cursor
3) return a SYS_REFCURSOR
4) close the cursor
?
Re: function & procedure [message #296686 is a reply to message #296681] Mon, 28 January 2008 12:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
all these questions are answered in the Oracle Manuals, you just need to read the pl/sql manual to find out. But here is a quickie in syntax for you:

create or replace function f1 (date_p in date) return sys_refcursor is
   c1 sys_refcursor;
begin
   open c1 for select * from dual where sysdate > date_p;
   return (c1);
end;
/

create or replace procedure p1 (date_p in date,c1_p out sys_refcursor) is
begin
   open c1_p for select * from dual where sysdate > date_p;
end;
/

These examples answer all the questions you asked.

You really should read the manuals, they are all online. I suppose next you will want to know how to interact with these functions and procedures. Here is some more syntax:

select f1(sysdate-1) from dual;

declare
   rc1 sys_refcursor;
   xv varchar2(1);
begin
   p1(sysdate-1,rc1);
   loop
      fetch rc1 into xv;
      if rc1%notfound then exit; end if;
      dbms_output.put_line(xv);
   end loop;
   close rc1;
end;
/


Good luck, Kevin

[Updated on: Mon, 28 January 2008 12:23]

Report message to a moderator

Re: function & procedure [message #296687 is a reply to message #296686] Mon, 28 January 2008 12:25 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
thanks a lot
Re: function & procedure [message #296688 is a reply to message #296684] Mon, 28 January 2008 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If i create a procedure can I return a cursor?

Yes.

PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Managing Cursors in PL/SQL
Section Using Cursor Variables (REF CURSORs)
Chapter 11 Tuning PL/SQL Applications for Performance
Section Passing Data with Cursor Variables
Etc.

Regards
Michel

Re: function & procedure [message #296689 is a reply to message #296681] Mon, 28 January 2008 12:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Michel, I should have posted these myself. Good to have you watching.

Guess if we are offering up syntax examples, then I should have also posted this one just to make you think:

create or replace procedure p2 (rc1 out sys_refcursor) is
begin
   open rc1 for select a.*,cursor(select b.*,cursor(select c.* from dual c) dual_c from dual b) dual_b from dual a;
end;
/
show errors


declare
   rc_a sys_refcursor;
   rc_b sys_refcursor;
   rc_c sys_refcursor;
   a_v varchar2(1);
   b_v varchar2(1);
   c_v varchar2(1);
begin
   p2 (rc_a);
   loop
      fetch rc_a into a_v,rc_b;
      if rc_a%notfound then exit; end if;
      loop
         fetch rc_b into b_v,rc_c;
         if rc_b%notfound then exit; end if;
         loop
            fetch rc_c into c_v;
            if rc_c%notfound then exit; end if;
         end loop;
         close rc_c;
      end loop;
      close rc_b;
   end loop;
   close rc_a;
end;
/


hehehe

Kevin
Re: function & procedure [message #296690 is a reply to message #296689] Mon, 28 January 2008 12:44 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
Kevin Meade wrote on Mon, 28 January 2008 19:37
Thanks Michel, I should have posted these myself. Good to have you watching.

Guess if we are offering up syntax examples, then I should have also posted this one just to make you think:

create or replace procedure p2 (rc1 out sys_refcursor) is
begin
   open rc1 for select a.*,cursor(select b.*,cursor(select c.* from dual c) dual_c from dual b) dual_b from dual a;
end;
/
show errors


declare
   rc_a sys_refcursor;
   rc_b sys_refcursor;
   rc_c sys_refcursor;
   a_v varchar2(1);
   b_v varchar2(1);
   c_v varchar2(1);
begin
   p2 (rc_a);
   loop
      fetch rc_a into a_v,rc_b;
      if rc_a%notfound then exit; end if;
      loop
         fetch rc_b into b_v,rc_c;
         if rc_b%notfound then exit; end if;
         loop
            fetch rc_c into c_v;
            if rc_c%notfound then exit; end if;
         end loop;
         close rc_c;
      end loop;
      close rc_b;
   end loop;
   close rc_a;
end;
/


hehehe

Kevin



OK
but in this procedure i can't see a RETURN statement.

can I create a procedure like this?

create or replace procedure p2 (rc1 out sys_refcursor) return SYS_REFCURSOR

???
Re: function & procedure [message #296691 is a reply to message #296689] Mon, 28 January 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/917/0/

Regards
Michel

P.S. Didn't see your message before I posted mine as I was searching all the references when you are builting your example.

Re: function & procedure [message #296692 is a reply to message #296691] Mon, 28 January 2008 12:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
just goes to show how thorough you are. I hesitated to post a code sample then figured someone else would post without first suggesting he do some reading, and I wanted to tell him first that he could have found this on his own so I jumped in.

Kevin
Re: function & procedure [message #296694 is a reply to message #296692] Mon, 28 January 2008 12:54 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
SORRY
Re: function & procedure [message #296696 is a reply to message #296681] Mon, 28 January 2008 12:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
sbroken,

try it yourself and find out. read what Michel posted for you and find out. Then get back to us with what you learned. There is a limit after all to what we will offer. We need to see some sweat off your brow before you get any more.

Kevin
Re: function & procedure [message #296697 is a reply to message #296694] Mon, 28 January 2008 12:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Sorry about what? we are glad you found us, glad you asked a question, and glad we could point you in a workable direction.

We would also be glad to see you travel down that road a wee ways and send us a post card when you see something you like.

Good luck, Kevin
Re: function & procedure [message #296791 is a reply to message #296697] Tue, 29 January 2008 01:36 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
Kevin Meade wrote on Mon, 28 January 2008 19:58
Sorry about what? we are glad you found us, glad you asked a question, and glad we could point you in a workable direction.

We would also be glad to see you travel down that road a wee ways and send us a post card when you see something you like.

Good luck, Kevin


I've just started walking down that road.
I'm sure I will need your help soon....

Ciao
Re: function & procedure [message #296913 is a reply to message #296791] Tue, 29 January 2008 08:06 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
Here I am again...
I've created the procedure which gives in output a sys_refcursor, in this way:

CREATE OR REPLACE PROCEDURE my_proc (
structure IN VARCHAR2,
my_cursor OUT SYS_REFCURSOR)
BEGIN
....

Then I open the cursor

OPEN my_cursor FOR SELECT * FROM my_table;

then i catch the exceptions
and in the end...

END

Invoking the procedure, could I retrieve the cursor?
Or do I have to write some other?
Re: function & procedure [message #296916 is a reply to message #296913] Tue, 29 January 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please format your post: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

You open the cursor with the output variable, you don't have to do anything more.

Regards
Michel
Re: function & procedure [message #296918 is a reply to message #296916] Tue, 29 January 2008 08:33 Go to previous messageGo to next message
sbroken
Messages: 14
Registered: January 2008
Junior Member
ok.
Thank you
Re: function & procedure [message #296937 is a reply to message #296681] Tue, 29 January 2008 09:40 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
review my previous posts, I gave an example of this.

Kevin
Previous Topic: ORA-06533
Next Topic: Oracle Bug - PLSQL variable names
Goto Forum:
  


Current Time: Mon Dec 05 05:08:56 CST 2016

Total time taken to generate the page: 0.09959 seconds