Suppress CURSOR message from sqlplus output

Question

I have a function that returns a refcursor for a given query.

I call the function like this. Although this is a simple query my actual query is more dynamic with other parameters for where clause.

select myfunction('select * from employees') as f  from dual;

and it gives me the output

F                   
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        198 Donald               OConnell                  DOCONNEL                  650.507.9833         20070621 SH_CLERK         2600                       124            50
        199 Douglas              Grant                     DGRANT                    650.507.9844         20080113 SH_CLERK         2600                       124            50
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         20030917 AD_ASST          4400                       101            10
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         20040217 MK_MAN          13000                       100            20
        202 Pat                  Fay                       PFAY                      603.123.6666         20050817 MK_REP           6000                       201            20
        203 Susan                Mavris                    SMAVRIS                   515.123.7777         20020607 HR_REP           6500                       101            40
        204 Hermann              Baer                      HBAER                     515.123.8888         20020607 PR_REP          10000                       101            70

I don't want these lines at the top and only want the cursor's output.

CURSOR STATEMENT : 1

How do I stop sqlplus or SQL developer from displaying it? what commands should I use?

Littlefoot · Accepted Answer · 2018-11-30 20:53:36Z

3

That looks like a function that returns a ref cursor; is it?

SQL> create or replace function f_my (par_select in varchar2)
  2  return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    open l_rc for par_select;
  7    return l_rc;
  8  end;
  9  /

Function created.

Here's what you currently have:

SQL> select f_my('select * From dept') as f from dual;

F
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

So, how to get rid of those CURSOR STATEMENT : 1 lines? It is SQLPlus that's displaying them (some other tool might not do it). But, as you use SQLPlus, here's how:

SQL> var v_rc refcursor
SQL> exec :v_rc := f_my('select * from dept')

PL/SQL procedure successfully completed.

SQL> print v_rc

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

answered Nov 30, 2018 at 20:53

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

itguy Over a year ago

Thanks. I added an answer to include SET FEEDBACK OFF to eliminate "PL/SQL procedure successfully completed." too

itguy · Accepted Answer · 2018-12-01 15:04:59Z

0

Thanks to Littlefoot's answer. I am able to extract only the rows from the table using the following lines in my script.

SET FEEDBACK OFF
SET PAGES 0
VARIABLE c REFCURSOR
spool /mypath/somefile.csv
EXEC :c := myfunction('select * from employees')
PRINT c

answered Dec 1, 2018 at 15:04

itguy

377 bronze badges

Collectives™ on Stack Overflow

Suppress CURSOR message from sqlplus output

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related