3 Replies Latest reply on Oct 3, 2013 7:58 AM by Billy~Verreynne

    dynamic execution

    1043556

      Hi all this is Subhanu,

         i have a question please give me code with an example

       

      how to display all the records in a table ,i am passing the table name as in param to the procedure/function

       

      suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.

        • 1. Re: dynamic execution
          ranit B

          Hi Subhanu,

           

          Could you please tell us the business requirement which you are trying to solve?

           

          To just fetch the records from a table, why do you need to pass it to a proc? Please clarify... and also mention your Database version [ select * from v$version ]

           

          -- Ranit

          • 2. Re: dynamic execution
            Karthick2003


            You can use a refcursor to do that. But the issue would be for the client in handling the refcursor. Think about it how will it process it if it does not know the exact projection of the returned cursor. You need to come up with a solution like that of the PRINT command in SQL Plus.

             

            Here is a example

             

            You can simply have a Procedure that will return a  refcursor in a output variable like this.

             

            SQL> create or replace procedure print_table
              2  (
              3    p_table_name in varchar2,
              4    p_output    out sys_refcursor
              5  )
              6  as
              7  begin
              8    open p_output for 'select * from ' || p_table_name;
              9  end;
            10  /

             

            Procedure created.

             

            Now this is the tricky part. In SQL Plus it looks very easy with the PRINT command. In SQL Plus you just create a variable of type REFCURSOR and pass it to the procedure. Then use the PRINT command to print it.

             

            SQL> var rc_output refcursor

             

            SQL> exec print_table('EMP', :rc_output)

             

            PL/SQL procedure successfully completed.

             

            SQL> print rc_output

             

                 EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO         ID
            ---------- ------ --------- ---------- --------- ---------- ---------- ---------- ----------
                  7369 SMITH  CLERK           7902 02-APR-13      12975          0         20
                  7499 ALLEN  SALESMAN        7698 20-FEB-13      11600        300         30
                  7521 WARD   SALESMAN        7698 22-FEB-13      11250        500         30
                  7566 JONES  MANAGER         7839 02-APR-13      12975          0         20
                  7654 MARTIN SALESMAN        7698 28-SEP-13      11250       1400         30
                  7698 BLAKE  MANAGER         7839 01-MAY-13      12850          0         30
                  7782 CLARK  MANAGER         7839 09-JUN-13      12450          0         10
                  7788 SCOTT  ANALYST         7566 19-APR-87      13000          0         20
                  7839 KING   PRESIDENT            17-NOV-13       5000          0         10
                  7844 TURNER SALESMAN        7698 08-SEP-13      11500          0         30
                  7876 ADAMS  CLERK           7788 23-MAY-87      11101          0         20

             

            11 rows selected.

             

            SQL> exec print_table('DEPT', :rc_output)

             

            PL/SQL procedure successfully completed.

             

            SQL> print rc_output

             

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

             

            If you can give more detail on your actual need to do such a thing we could help you better.

            • 3. Re: dynamic execution
              Billy~Verreynne

              f3665bb6-1b11-4d8f-ba83-befd00bfce5e wrote:

               

              i have a question please give me code with an example

               

              how to display all the records in a table ,i am passing the table name as in param to the procedure/function

               

              suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.

               

              A ref cursor is suited for such a requirement. However, the requirement itself (table name is variable) sounds flawed. That is NOT how one designs robust and scalable solutions.

               

              A user that needs to select or enter an actual and physical tablename  to view business information? That is flawed. A user will select a business entity to view. In which case it makes sense for the client s/w to pass that information to PL/SQL, and PL/SQL using this to determine which physical database tables and views to use, in order to construct the information for that business entity.