Toad World® Forums

DBMS_OUTPUT.PUT_LINE

Hello ORACLE community.
I can not see the output of the function.
This is my script.

– FUNCTION customers( cgetID IN VARCHAR2) RETURN SYS_REFCURSOR;

SET SERVEROUTPUT ON SIZE 30000;
DECLARE
vSRef SYS_REFCURSOR;
BEGIN
vSRef:=PACKAGE_CLIENTS.customers(‘130’);
DBMS_OUTPUT.PUT_LINE(vSRef);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR:’||sqlerrm);
END;

I have the error:
PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’

Could you help me please. Thanks a lot!

:slight_smile:

I think there are 2 issues

SET SERVEROUTPUT ON

begin

DBMS_OUTPUT.ENABLE(1000000);

end:

On Wed, Jul 19, 2017 at 4:03 PM, antoine_mc1 bounce-antoine_mc1@toadworld.com wrote:

DBMS_OUTPUT.PUT_LINE

Thread created by antoine_mc1
Hello ORACLE community.
I can not see the output of the function.
This is my script.

– FUNCTION customers( cgetID IN VARCHAR2) RETURN SYS_REFCURSOR;

SET SERVEROUTPUT ON SIZE 30000;
DECLARE
vSRef SYS_REFCURSOR;
BEGIN
vSRef:=PACKAGE_CLIENTS.customers(‘130’);
DBMS_OUTPUT.PUT_LINE(vSRef);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR:’||sqlerrm);
END;

I have the error:
PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’

Could you help me please. Thanks a lot!

:slight_smile:

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Code Tester for Oracle Forum notifications altogether.

Code Tester for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

Thank you Gene.

Hello!

SET SERVEROUTPUT ON
DECLARE
vSRef SYS_REFCURSOR;
BEGIN
vSRef:=PACKAGE_CLIENTS.customers(‘130’);
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(vSRef);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR:’||sqlerrm);
END;

I have the same error:
PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’
PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’
Could you help me please. Thanks a lot!

:slight_smile:

Check the input/output specifications on your procedure.

it either asked for 2 and you only supplied 1, or something like that.

On Thu, Jul 20, 2017 at 2:34 PM, antoine_mc1 bounce-antoine_mc1@toadworld.com wrote:

RE: DBMS_OUTPUT.PUT_LINE

Reply by antoine_mc1
Thank you Gene.

Hello!

SET SERVEROUTPUT ON
DECLARE
vSRef SYS_REFCURSOR;
BEGIN
vSRef:=PACKAGE_CLIENTS.customers(‘130’);
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(vSRef);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR:’||sqlerrm);
END;

I have the same error:
PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’
PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’
Could you help me please. Thanks a lot!

:slight_smile:

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

Thanks a lot Gene.

Just ask for one parameter.

This is the function:

FUNCTION customers( cgetID IN VARCHAR2) RETURN SYS_REFCURSOR;

:slight_smile:

Can you show the create or replace code for the function?

On Thu, Jul 20, 2017 at 7:23 PM, antoine_mc1 bounce-antoine_mc1@toadworld.com wrote:

RE: DBMS_OUTPUT.PUT_LINE

Reply by antoine_mc1
Thanks a lot Gene.

Just ask for one parameter.

This is the function:

FUNCTION customers( cgetID IN VARCHAR2) RETURN SYS_REFCURSOR;

:slight_smile:

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

Thank you again.

Okay, this is the function:

FUNCTION customers
( cgetID IN VARCHAR2 ) RETURN SYS_REFCURSOR
IS
vcli SYS_REFCURSOR;
vClt varchar2(150);
BEGIN
BEGIN
SELECT des.Dm
INTO vClt
FROM p1 z1 ,ps p ,ct c ,tdo t ,mn des ,rr o
WHERE p1.igo = cgetID
AND p1.ps = BO.ps AND p1.code2 = BO.code2
AND p1.code = BO.code AND p1.mp = BO.mp
AND p1.code2 = des.code2 AND p1.code = des.code
AND p1.mp = des.mp AND p1.ps = c.ps AND p1.ps = t.ps;
END;
OPEN vcli FOR
SELECT DISTINCT o.cbb ,o.dbr ,des.mp ,des.Dm ,c.code ,t.code2
FROM p1 ZP ,ps p ,ct c ,tdo t ,mn des ,rr o
WHERE p1.igo = cgetID
AND p1.ps = c.ps AND p1.code2 = c.code2
AND p1.code = c.code AND p1.ps = t.ps
AND p1.code2 = t.code2;
RETURN vcli;
END customers;

It looks fine at first glance from here.

I will try to recreate something like that using my own data (LOL)

And see what happens…Anybody else out there see anything? GUys, gals… Hello???

On Fri, Jul 21, 2017 at 2:05 PM, antoine_mc1 bounce-antoine_mc1@toadworld.com wrote:

RE: DBMS_OUTPUT.PUT_LINE

Reply by antoine_mc1
Thank you again.

Okay, this is the function:

FUNCTION customers
( cgetID IN VARCHAR2 ) RETURN SYS_REFCURSOR
IS
vcli SYS_REFCURSOR;
vClt varchar2(150);
BEGIN
BEGIN
SELECT des.Dm
INTO vClt
FROM p1 z1 ,ps p ,ct c ,tdo t ,mn des ,rr o
WHERE p1.igo = cgetID
AND p1.ps = BO.ps AND p1.code2 = BO.code2
AND p1.code = BO.code AND p1.mp = BO.mp
AND p1.code2 = des.code2 AND p1.code = des.code
AND p1.mp = des.mp AND p1.ps = c.ps AND p1.ps = t.ps;
END;
OPEN vcli FOR
SELECT DISTINCT o.cbb ,o.dbr ,des.mp ,des.Dm ,c.code ,t.code2
FROM p1 ZP ,ps p ,ct c ,tdo t ,mn des ,rr o
WHERE p1.igo = cgetID
AND p1.ps = c.ps AND p1.code2 = c.code2
AND p1.code = c.code AND p1.ps = t.ps
AND p1.code2 = t.code2;
RETURN vcli;
END customers;

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

The problem is that you’re trying to use DBMS_OUTPUT.PUT_LINE to display a variable with a SYS_REFCURSOR type, which it can’t do. Here’s a simplification of your example:

DECLARE
v_sysref SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.put_line(v_sysref);
END;
/

This anonymous block should produce the same error you’re seeing. If you want to display the values of the columns referenced by your cursor, you’ll need to deal with each row separately. A quick search brought up https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets (and other Toad-specific ones that may also help).

Hope this helps! Good luck!
Rich

hey Rich,
Teamwork makes the dream work!!!

On Fri, Jul 21, 2017 at 2:40 PM, Rich J. bounce-rjesse@toadworld.com wrote:

RE: DBMS_OUTPUT.PUT_LINE

Reply by Rich J.
The problem is that you’re trying to use DBMS_OUTPUT.PUT_LINE to display a variable with a SYS_REFCURSOR type, which it can’t do. Here’s a simplification of your example:

DECLARE
v_sysref SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.put_line(v_sysref);
END;
/

This anonymous block should produce the same error you’re seeing. If you want to display the values of the columns referenced by your cursor, you’ll need to deal with each row separately. A quick search brought up https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets (and other Toad-specific ones that may also help).

Hope this helps! Good luck!
Rich

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.