1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sys_refcursor.,

Discussion in 'SQL PL/SQL' started by Vicky, Feb 25, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Can any1 tell me how to execute the procedure below to display the output.,.???!!!

    CREATE OR REPLACE PROCEDURE p1(TABLE_NAME IN varchar2, o OUT sys_refcursor)
    AS
    sqlstmt VARCHAR2(300);
    BEGIN
    sqlstmt := ' select * from '|| TABLE_NAME;
    EXECUTE IMMEDIATE sqlstmt INTO o ;
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can't; a refcursor isn't a variable to select output into. You need to open the refcursor for a query:


    Code (SQL):

    CREATE OR REPLACE PROCEDURE p1(TABLE_NAME IN varchar2, o OUT sys_refcursor)
     AS
     sqlstmt VARCHAR2(300);
     BEGIN
     sqlstmt := ' select * from '|| TABLE_NAME;
     OPEN o FOR sqlstmt;
     END;
    /

     

    Executing the procedure and displaying the output is then a fairly simple task:


    Code (SQL):

    SQL> EXEC p1('ORDERS', :r)


    PL/SQL PROCEDURE successfully completed.


    SQL> print :r


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2458 16-AUG-07 02.34.12.234359 PM                                                direct        101               0     78279.6          153
          101
          2397 19-NOV-07 03.41.54.696211 PM                                                direct        102               1     42283.2          154
          102
          2454 02-OCT-07 04.49.34.678340 PM                                                direct        103               1      6653.4          154
          103
          2354 14-JUL-08 05.18.23.234567 PM                                                direct        104               0       46257          155
          104
          2358 08-JAN-08 06.03.12.654278 PM                                                direct        105               2        7826          155
          105
          2381 14-MAY-08 07.59.08.843679 PM                                                direct        106               3     23034.6          156
          106
          2440 31-AUG-07 08.53.06.008765 PM                                                direct        107               3     70576.9          156
          107
          2357 08-JAN-06 09.19.44.123456 PM                                                direct        108               5     59872.4          158
          108
          2394 10-FEB-08 10.22.35.564789 PM                                                direct        109               5       21863          158
          109
          2435 02-SEP-07 10.22.53.134567 PM                                                direct        144               6       62303          159
          144
          2455 20-SEP-07 10.34.11.456789 AM                                                direct        145               7     14087.5          160
          145


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2379 16-MAY-07 01.22.24.234567 AM                                                direct        146               8     17848.2          161
          146
          2396 02-FEB-06 02.34.56.345678 AM                                                direct        147               8       34930          161
          147
          2406 29-JUN-07 03.41.20.098765 AM                                                direct        148               8      2854.2          161
          148
          2434 13-SEP-07 04.49.30.647893 AM                                                direct        149               8    268651.8          161
          149
          2436 02-SEP-07 05.18.04.378034 AM                                                direct        116               8      6394.8          161
          116
          2446 27-JUL-07 06.03.08.302945 AM                                                direct        117               8    103679.3          161
          117
          2447 27-JUL-08 07.59.10.223344 AM                                                direct        101               8     33893.6          161
          101
          2432 14-SEP-07 08.53.40.223345 AM                                                direct        102              10       10523          163
          102
          2433 13-SEP-07 09.19.00.654279 AM                                                direct        103              10          78          163
          103
          2355 26-JAN-06 10.22.51.962632 AM                                                online        104               8     94513.5
          104
          2356 26-JAN-08 10.22.41.934562 AM                                                online        105               5     29473.8
          105


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2359 08-JAN-06 10.34.13.112233 PM                                                online        106               9      5543.1
          106
          2360 14-NOV-07 01.22.31.223344 PM                                                online        107               4       990.4
          107
          2361 13-NOV-07 02.34.21.986210 PM                                                online        108               8    120131.3
          108
          2362 13-NOV-07 03.41.10.619477 PM                                                online        109               4     92829.4
          109
          2363 23-OCT-07 04.49.56.346122 PM                                                online        144               0     10082.3
          144
          2364 28-AUG-07 05.18.45.942399 PM                                                online        145               4        9500
          145
          2365 28-AUG-07 06.03.34.003399 PM                                                online        146               9     27455.3
          146
          2366 28-AUG-07 07.59.23.144778 PM                                                online        147               5     37319.4
          147
          2367 27-JUN-08 08.53.32.335522 PM                                                online        148              10    144054.8
          148
          2368 26-JUN-08 09.19.43.190089 PM                                                online        149              10       60065
          149
          2369 26-JUN-07 10.22.54.009932 PM                                                online        116               0     11097.4
          116


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2370 26-JUN-08 11.22.11.647398 PM                                                online        117               4         126
          117
          2371 16-MAY-07 12.34.56.113356 AM                                                online        118               6     79405.6
          118
          2372 27-FEB-07 01.22.33.356789 AM                                                online        119               9     16447.2
          119
          2373 27-FEB-08 02.34.51.220065 AM                                                online        120               4         416
          120
          2374 27-FEB-08 03.41.45.109654 AM                                                online        121               0        4797
          121
          2375 26-FEB-07 04.49.50.459233 AM                                                online        122               2    103834.4
          122
          2376 07-JUN-07 05.18.08.883310 AM                                                online        123               6     11006.2
          123
          2377 07-JUN-07 06.03.01.001100 AM                                                online        141               5     38017.8
          141
          2378 24-MAY-07 07.59.10.010101 AM                                                online        142               5     25691.3
          142
          2380 16-MAY-07 08.53.02.909090 AM                                                online        143               3     27132.6
          143
          2382 14-MAY-08 09.19.03.828321 AM                                                online        144               8       71173
          144


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2383 12-MAY-08 10.22.30.545103 AM                                                online        145               8     36374.7
          145
          2384 12-MAY-08 11.22.34.525972 AM                                                online        146               3     29249.1
          146
          2385 08-DEC-07 12.34.11.331392 PM                                                online        147               4      295892
          147
          2386 06-DEC-07 01.22.34.225609 PM                                                online        148              10     21116.9
          148
          2387 11-MAR-07 02.34.56.536966 PM                                                online        149               5     52758.9
          149
          2388 04-JUN-07 03.41.12.554435 PM                                                online        150               4    282694.3
          150
          2389 04-JUN-08 04.49.43.546954 PM                                                online        151               4       17620
          151
          2390 18-NOV-07 05.18.50.546851 PM                                                online        152               9      7616.8
          152
          2391 27-FEB-06 06.03.03.828330 PM                                                direct        153               2     48070.6          156
          153
          2392 21-JUL-07 07.59.57.571057 PM                                                direct        154               9       26632          161
          154
          2393 10-FEB-08 08.53.19.528202 PM                                                direct        155               4     23431.9          161
          155


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2395 02-FEB-06 09.19.11.227550 PM                                                direct        156               3       68501          163
          156
          2398 19-NOV-07 10.22.53.224175 PM                                                direct        157               9      7110.3          163
          157
          2399 19-NOV-07 11.22.38.340990 PM                                                direct        158               0     25270.3          161
          158
          2400 10-JUL-07 12.34.29.559387 AM                                                direct        159               2     69286.4          161
          159
          2401 10-JUL-07 01.22.53.554822 AM                                                direct        160               3       969.2          163
          160
          2402 02-JUL-07 02.34.44.665170 AM                                                direct        161               8         600          154
          161
          2403 01-JUL-07 03.49.13.615512 PM                                                direct        162               0         220          154
          162
          2404 01-JUL-07 03.49.13.664085 PM                                                direct        163               6         510          158
          163
          2405 01-JUL-07 03.49.13.678123 PM                                                direct        164               5        1233          159
          164
          2407 29-JUN-07 06.03.21.526005 AM                                                direct        165               9        2519          155
          165
          2408 29-JUN-07 07.59.31.333617 AM                                                direct        166               1         309          158
          166


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2409 29-JUN-07 08.53.41.984501 AM                                                direct        167               2          48          154
          167
          2410 24-MAY-08 09.19.51.985501 AM                                                direct        168               6       45175          156
          168
          2411 24-MAY-07 10.22.10.548639 AM                                                direct        169               8     15760.5          156
          169
          2412 29-MAR-06 11.22.09.509801 AM                                                direct        170               9       66816          158
          170
          2413 29-MAR-08 12.34.04.525934 PM                                                direct        101               5       48552          161
          101
          2414 29-MAR-07 01.22.40.536996 PM                                                direct        102               8     10794.6          153
          102
          2415 29-MAR-06 02.34.50.545196 PM                                                direct        103               6         310          161
          103
          2416 29-MAR-07 03.41.20.945676 PM                                                direct        104               6         384          160
          104
          2417 20-MAR-07 04.49.10.974352 PM                                                direct        105               5      1926.6          163
          105
          2418 20-MAR-04 05.18.21.862632 PM                                                direct        106               4      5546.6          163
          106
          2419 20-MAR-07 06.03.32.764632 PM                                                direct        107               3       31574          160
          107


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2420 13-MAR-07 07.59.43.666320 PM                                                direct        108               2       29750          160
          108
          2421 12-MAR-07 08.53.54.562432 PM                                                direct        109               1       72836
          109
          2422 16-DEC-07 09.19.55.462332 PM                                                direct        144               2     11188.5          153
          144
          2423 21-NOV-07 11.22.33.362632 AM                                                direct        145               3     10367.7          160
          145
          2424 21-NOV-07 11.22.33.263332 AM                                                direct        146               4       13824          153
          146
          2425 17-NOV-06 12.34.22.162552 AM                                                direct        147               5      1500.8          163
          147
          2426 17-NOV-06 01.22.11.262552 AM                                                direct        148               6        7200
          148
          2427 10-NOV-07 02.34.22.362124 AM                                                direct        149               7        9055          163
          149
          2428 10-NOV-07 03.41.34.463567 AM                                                direct        116               8     14685.8
          116
          2429 10-NOV-07 04.49.25.526321 AM                                                direct        117               9       50125          154
          117
          2430 02-OCT-07 05.18.36.663332 AM                                                direct        101               8     29669.9          159
          101


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2431 14-SEP-06 06.03.04.763452 AM                                                direct        102               1      5610.6          163
          102
          2437 01-SEP-06 07.59.15.826132 AM                                                direct        103               4       13550          163
          103
          2438 01-SEP-07 08.53.26.934626 AM                                                direct        104               0        5451          154
          104
          2439 31-AUG-07 09.19.37.811132 AM                                                direct        105               1     22150.1          159
          105
          2441 01-AUG-08 10.22.48.734526 AM                                                direct        106               5      2075.2          160
          106
          2442 27-JUL-06 11.22.59.662632 AM                                                direct        107               9     52471.9          154
          107
          2443 27-JUL-06 12.34.16.562632 PM                                                direct        108               0        3646          154
          108
          2444 27-JUL-07 01.22.27.462632 PM                                                direct        109               1     77727.2          155
          109
          2445 27-JUL-06 02.34.38.362632 PM                                                direct        144               8      5537.8          158
          144
          2448 18-JUN-07 03.41.49.262632 PM                                                direct        145               5        1388          158
          145
          2449 13-JUN-07 04.49.07.162632 PM                                                direct        146               6          86          155
          146


      ORDER_ID ORDER_DATE                                                                  ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION
    _ID        CUSTID
    ---------- --------------------------------------------------------------------------- -------- ----------- ------------ ----------- ------------ ---------
    --- ----------
          2450 11-APR-07 05.18.10.362632 PM                                                direct        147               3        1636          159
          147
          2451 17-DEC-07 06.03.52.562632 PM                                                direct        148               7     10474.6          154
          148
          2452 06-OCT-07 07.59.43.462632 PM                                                direct        149               5       12589          159
          149
          2453 04-OCT-07 08.53.34.362632 PM                                                direct        116               0         129          153
          116
          2456 07-NOV-06 08.53.25.989889 PM                                                direct        117               0      3878.4          163
          117
          2457 31-OCT-07 10.22.16.162632 PM                                                direct        118               5     21586.2          159
          118


    105 ROWS selected.


    SQL>
     

    I hope this helps.
     
    Vicky and jagadekara like this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks, David. Can you tell me how to execute this procedure in applications other than sql plus like sql developer, isql plus., I tried to execute the procedure as below:

    declare
    x sys_refcursor;
    begin
    P1('emp', :x);
    end;

    But I'm not getting the output.., I'm getting the error as below:

    ORA-06550: line 4, column 2:
    PLS-00306: wrong number or types of arguments in call to 'P1'
    ORA-06550: line 4, column 2:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    declare
    x sys_refcursor;
    begin
    P1('emp', x);
    end;
    /
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks David, but I'm not getting any o/p.,.

    CREATE OR REPLACE PROCEDURE p1(TABLE_NAME IN varchar2, o OUT sys_refcursor)
    AS
    sqlstmt VARCHAR2(300);
    BEGIN
    sqlstmt := ' select * from '|| TABLE_NAME;
    open O for SQLSTMT;
    END;


    declare
    x sys_refcursor;
    begin
    P1('emp', X);
    end;


    I'm just getting :-
    /

    PROCEDURE P1 compiled

    anonymous block completed


    Is der anythng wrong with the procedure..,.?!!
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No but you're not fetching from the refcursor to get the data:


    Code (SQL):

    SQL>  DECLARE
      2   x sys_refcursor;
      3   trec emp%rowtype;
      4   BEGIN
      5   P1('emp', x);
      6   loop
      7          fetch x INTO trec;
      8          exit WHEN x%notfound;
      9          dbms_output.put_line(trec.empno);
     10   END loop;
     11   END;
     12  /
    7369
    7499
    7521
    7566
    7654
    7698
    7782
    7788
    7839
    7844
    7876
    7900
    7902
    7934
    7939
    7949
    7959
    7869


    PL/SQL PROCEDURE successfully completed.


    SQL>
     
     
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    If you are using SQL Developer you have to remember to open a DBMS Output window in order to see anything. It is not set to open by default.