Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009

    Question Unanswered: output sql query result in plsql block

    Can any one tell me how can we print the result of sql query
    "select * from <table_name>" using plsql code block, without putting the result into any rowtype or any other type of variable.

    I am using oracle 10g at the backend;

    Thanks !!

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    SQL> create or replace procedure print_rec (par_rec out sys_refcursor)
      2  is
      3  begin
      4    open par_rec for 'select * from dept';
      5  end;
      6  /
    Procedure created.
    SQL> var rc refcursor;
    SQL> exec print_rec(:rc)
    PL/SQL procedure successfully completed.
    SQL> print :rc
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

  3. #3
    Join Date
    Oct 2009
    Hi LF

    Thnaks for the help !!

    But my requirement is to have procedure like the one given below, and when I execute the procedure it should print the select query result.

    create or replace PROCEDURE RESEARCH AS
    r sys_refcursor;

    open r for 'select * from asset';

    I tried your logic in above code it doesn't print nything. Plz help !!!

  4. #4
    Join Date
    Dec 2003
    Oracle doesn't work like SQL Server. There are ways to invoke a PACKAGE via SQL, though, casting the results of the PACKAGE call into a TABLE. Well, we use PACKAGEs to store the TYPE variables like you'll see below, but here's the same concept performed with 2 TYPE variables and a FUNCTION (the PACKAGE is nice because you can bundle the TYPEs into the PACKAGE spec)

    SQL> create or replace type return_rec_obj 
      2  as object (
      3   fname varchar2(10),
      4   lname varchar2(15)
      5  );
      6  /
    Type created.
    SQL> create or replace type return_rec_tab as table of return_rec_obj;
      3  /
    Type created.
    SQL> CREATE OR REPLACE function return_rec_f(p_input_val number) return return_rec_tab PIPELINED
      2  is
      3    cursor cur0 
      4    is
      5      select 'Chuck','Forbes' from dual where 1 = p_input_val
      6      union all
      7      select 'Vijay','Alagrisamy' from dual where 2 = p_input_val
      8      union all
      9      select 'Christian','Turri' from dual where 1 = p_input_val;
     11    out_rec return_rec_obj := return_rec_obj(NULL,NULL);
     12  begin
     13    open cur0; 
     14    loop
     15   fetch cur0 into out_rec.fname, out_rec.lname;
     16   exit when cur0%NOTFOUND;
     17   PIPE ROW (out_rec);
     18    end loop;
     19    close cur0;
     21    return;
     22  end;
     23  /
    SQL> select * from TABLE(return_rec_f(1));
    FNAME      LNAME
    ---------- ---------------
    Chuck      Forbes
    Christian  Turri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts