CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperref-cursor
Esteban Rincon
Esteban Rincon

Reputation: 2110

How to print a cursor in a PL/SQL block?

I can't seem to print cursor, what am I doing wrong?

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
  v_Return ON24MASTER.WEBCAST_REPORTS.ResultSetCursor;
BEGIN
  LEADEMAIL := '[email protected]';
  CLIENTID := 22921;

  v_Return := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

    DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
--    :v_Return := v_Return;

END;

I get the following error:

Error report -
ORA-06550: line 14, column 26:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 14, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Most of the code was taken directly from running the function from SQL developer.

This is the package function:

  FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor IS
    resultSet ResultSetCursor;
    email VARCHAR2(1000);
    webcastEngagement NUMBER(10,1);
    videoEngagement NUMBER(10,1);
    documentEngagement NUMBER(10,1);
    totalEngagement NUMBER(10,1);
    --averageEngagement NUMBER(4,1);
    totalWebcastSeconds NUMBER(10);
    engagementMinutes NUMBER(10, 1);
    last30DaysEM NUMBER(10, 1);
    last60DaysEM NUMBER(10, 1);
    fromDate DATE;
    engagementPrediction NUMBER(10);
  BEGIN...

Also, I can't print the result using a select statement because the function has DML as well.

Upvotes: 1

Views: 8298

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17944

In Oracle 12c, you can use DBMS_SQL.RETURN_RESULT. I.e.,

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
  v_Return ON24MASTER.WEBCAST_REPORTS.ResultSetCursor;
BEGIN
  LEADEMAIL := '[email protected]';
  CLIENTID := 22921;

  v_Return := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

    DBMS_SQL.RETURN_RESULT(v_Return); 

END;

SQL*Developer will print the results.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191560

You can't print a cursor like that; it would have to implicitly convert the rows and columns to strings, and that's too much to expect. The dbms_output.put_line() procedure only accepts a string argument - or anything that can be implicitly converted to a string. A cursor cannot.

You would have to loop over the cursor result set, fetching into a suitable record type; and then have a dbms_output call within that loop which concatenates all the column values from the result set (formatted and possibly padded if you're trying to emulate a select) into a single string.

Without know exactly how ON24MASTER.WEBCAST_REPORTS.ResultSetCursor is defined (presumably TYPE ResultSetCursor IS REF CURSOR), or what the query that populates it within your procedure is returning - which column names - it's hard to be more specific.

But since you've tagged this for SQL Developer you can use its built-in handling for ref cursor variables, which is handy:

variable rc refcursor;

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
BEGIN
  LEADEMAIL := '[email protected]';
  CLIENTID := 22921;

  :rc := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

END;
/

print rc

Before the block a bind variable rc is declared with the variable command. Inside the block that is used instead of a local v_Return, so that doesn't even need to be declared locally. (Note the colon before :rc in the assignment from the function - that denotes a bind variable). And then after the block the client lets you print the ref cursor. (Those doc links are for SQL*Plus, but they are among the the many commands SQL Developer supports.)

With a dummy package:

create or replace package WEBCAST_REPORTS AS
  TYPE ResultSetCursor IS ref cursor;
  FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor;
end WEBCAST_REPORTS;
/

create or replace package body WEBCAST_REPORTS AS
  FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor IS
    resultSet ResultSetCursor;
  BEGIN
    OPEN resultSet FOR select * from dual;
    RETURN resultSet;
  END LEAD_BASIC_INFO;
end WEBCAST_REPORTS;
/

then the code I showed above, run as a script, shows this in the script output window:

PL/SQL procedure successfully completed.


D
-
X

Upvotes: 2

Related Questions