Home » SQL & PL/SQL » SQL & PL/SQL » executing a procedure (oracle 10g)
executing a procedure [message #381936] Tue, 20 January 2009 06:23 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how to execute a procedure if it is having ref cursor as data type,
also with in out parameter.can anyone guide me.it is as follows


CREATE OR REPLACE PROCEDURE VWUser_Add (Params varchar2,RC1 IN OUT ViewWisePackage.ViewWise_CurTyp) AS
PUserName VARCHAR2(50);
PUserID NUMBER(10,0);
PRINCIPALID NUMBER(10,0);
Re: executing a procedure [message #381940 is a reply to message #381936] Tue, 20 January 2009 06:42 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example; is this what you are looking for?
SQL> create or replace procedure prc_oraf
  2    (par_rc in out sys_refcursor)
  3    is
  4  begin
  5    open par_rc for select dname, loc from dept;
  6  end;
  7  /

Procedure created.

SQL> var rc refcursor;
SQL> exec prc_oraf(:rc);

PL/SQL procedure successfully completed.

SQL> print rc;

DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

SQL> 
Re: executing a procedure [message #381943 is a reply to message #381936] Tue, 20 January 2009 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once more read OraFAQ Forum Guide
"How to format your post?" section, make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: executing a procedure [message #381946 is a reply to message #381943] Tue, 20 January 2009 07:02 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel
Once more read OraFAQ Forum Guide
Again? I lost my eyes reading it!

Michel
"How to format your post?" section, make sure that lines of code do not exceed 80 characters when you format.
Yes, I believe they don't exceed 80 characters.

Michel
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Actually, I did that manually.

Michel
Use the "Preview Message" button to verify.
Well, I prefer "Spell-check Message". It shows both formatting and checks my spelling.

Michel
Also always post your Oracle version (4 decimals).
Ooops! Forgot to mention that! Sorry!


Ooops #2! It wasn't me you were replying to!

[Updated on: Tue, 20 January 2009 07:04]

Report message to a moderator

Re: executing a procedure [message #381947 is a reply to message #381940] Tue, 20 January 2009 07:04 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i have to execute both simultaneously
Re: executing a procedure [message #381948 is a reply to message #381947] Tue, 20 January 2009 07:05 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Both? Both what?
Re: executing a procedure [message #381949 is a reply to message #381946] Tue, 20 January 2009 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
yes but you missed a point, I answered to OP not to you. Laughing

Quote:
Well, I prefer "Spell-check Message". It shows both formatting and checks my spelling.

I will change my message! Laughing

Regards
Michel



Ooops! you already saw it.

[Updated on: Tue, 20 January 2009 07:07]

Report message to a moderator

Re: executing a procedure [message #381950 is a reply to message #381948] Tue, 20 January 2009 07:06 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
both params and ref cursor can you show me how to proceed for that
Re: executing a procedure [message #381952 is a reply to message #381950] Tue, 20 January 2009 07:09 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are unable to extend Littlefoot example to more than one parameter, I think you should go back to study documentation before anything.
What is your problem? Post what you tried.

Regards
Michel

[Updated on: Tue, 20 January 2009 07:11]

Report message to a moderator

Previous Topic: SQL
Next Topic: creating index on view
Goto Forum:
  


Current Time: Mon Dec 05 18:50:42 CST 2016

Total time taken to generate the page: 0.10048 seconds