Link to home
Start Free TrialLog in
Avatar of chuang4630
chuang4630

asked on

How to Oracle stored procedure with output parameters

How do I run an Oracle stored proc with output parameters:

PROCEDURE GetCollectorInfo
(
  v_organizationId IN NUMBER DEFAULT NULL ,
  v_collectorId IN NUMBER DEFAULT NULL ,
  cv_1 OUT SYS_REFCURSOR,
  cv_2 OUT SYS_REFCURSOR,
  cv_3 OUT SYS_REFCURSOR,
  cv_4 OUT SYS_REFCURSOR
)

I need to manually run it in Oracle SQL Developer.

Thanks in advance
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chuang4630
chuang4630

ASKER

I did this:
var mycv1 sys_refcursor
var mycv2 sys_refcursor
var mycv3 sys_refcursor
var mycv4 sys_refcursor

exec  PROCEDURE GetCollectorInfo  (1,2,:mycv1,:mycv2,:mycv3,:mycv4);
print mycv1
print mycv2
print mycv3
print mycv4

error:
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]
When I did this:
var mycv1 refcursor
var mycv2 refcursor
var mycv3 refcursor
var mycv4 refcursor

exec  PROCEDURE GetCollectorInfo  (1,2,:mycv1,:mycv2,:mycv3,:mycv4);
print mycv1
print mycv2
print mycv3
print mycv4

No results
Bind Variable "MYCV4" is NOT DECLARED
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you try this:
(enable DBMS output window)

DECLARE
  mycv1 sys_refcursor;
  mycv2 sys_refcursor;
  mycv3 sys_refcursor;
  mycv4 sys_refcursor;
BEGIN
  GetCollectorInfo(1,2,mycv1, mycv2, mycv3, mycv4);
  dbms_output.put_line(mycv1);
  dbms_output.put_line(mycv2);
  dbms_output.put_line(mycv3);
  dbms_output.put_line(mycv4);
END;
>>No results

I just provided 1 and 2 for v_organizationId and v_collectorId.

You'll need to provide correct numbers.

>>THis one works

Do you need further assistance?  If not, don't forget to close the question.
I've requested that this question be closed as follows:

Accepted answer: 250 points for gerwinjansen's comment #a40099529
Assisted answer: 250 points for slightwv's comment #a40099400
Assisted answer: 0 points for chuang4630's comment #a40099492

for the following reason:

I have supplied the correct solution.
I'm afraid I'll have to object to accepting http:#a40099529 as part of the solution.

That code as posted will not work.  In fact if you try it you should receive an error.

Below is an example showing this.  The example based on my post works.

The other one returns the error:
 dbms_output.put_line(mycv1);
  *
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored



drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
insert into tab1 values('b');
commit;

create or replace procedure myproc(p_outcur out sys_refcursor)
is
begin
	open p_outcur for select * from tab1;
end;
/

show errors

var mycv1 refcursor

exec  myproc  (:mycv1);
print mycv1



DECLARE
  mycv1 sys_refcursor;
BEGIN
  myproc(mycv1);
  dbms_output.put_line(mycv1);
END; 
/

Open in new window