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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
var mycv1 refcursor
var mycv2 refcursor
var mycv3 refcursor
var mycv4 refcursor
exec PROCEDURE GetCollectorInfo (1,2,:mycv1,:mycv2,:mycv3,
print mycv1
print mycv2
print mycv3
print mycv4
No results
ASKER
Bind Variable "MYCV4" is NOT DECLARED
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
(enable DBMS output window)
DECLARE
mycv1 sys_refcursor;
mycv2 sys_refcursor;
mycv3 sys_refcursor;
mycv4 sys_refcursor;
BEGIN
GetCollectorInfo(1,2,mycv1
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 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.
ASKER
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.
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:
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;
/
ASKER
var mycv1 sys_refcursor
var mycv2 sys_refcursor
var mycv3 sys_refcursor
var mycv4 sys_refcursor
exec PROCEDURE GetCollectorInfo (1,2,:mycv1,:mycv2,:mycv3,
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 ] ]