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!

Dynamic SQL - reg

Discussion in 'SQL PL/SQL' started by Vikram S, Apr 21, 2014.

  1. Vikram S

    Vikram S Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Hi guys,

    I need to write a stored procedure/function to return the table data where table is dynamic.

    Please help me out.

    Thanks & regards,
    Vikram S
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    You can formulate more precisely your statements of the problem?

    simple example :
    Code (SQL):



    CREATE TABLE test (x INT ,y INT);
    SET SERVEROTPUT ON
    CREATE OR REPLACE FUNCTION listcolumn_of_table(
                        p_table_name IN user_tab_cols.TABLE_NAME%TYPE
                        ) RETURN sys_refcursor AS
                       
     
           v_sql     CLOB;
           v_cursor  BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
           v_execute BINARY_INTEGER;
     
    BEGIN
     
      v_sql := 'SELECT column_name FROM user_tab_cols WHERE table_name = :table_name';
      DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE(v_cursor, 'TABLE_NAME', p_table_name);
      v_execute := DBMS_SQL.EXECUTE(v_cursor);
      RETURN DBMS_SQL.TO_REFCURSOR(v_cursor);
     
    END;
    /

    SET autoprint ON

    VAR rc REFCURSOR;

    BEGIN
       :rc := listcolumn_of_table('TEST');
    END;
    /    

    SQL>
    TABLE created.
    FUNCTION created.
     PL/SQL PROCEDURE successfully completed.

    COLUMN_NAME                  
    ------------------------------
    X                            
    Y                            

    2 ROWS selected.

     

    Example 2 :

    Code (SQL):


    CREATE OR REPLACE FUNCTION listrows_of_dual(
                        p_cnt_rows INT
                        ) RETURN sys.odcivarchar2list
                        AS
    l_chr_rows       sys.odcivarchar2list;
    BEGIN
     
     SELECT to_char(level)
     bulk collect INTO l_chr_rows
     FROM dual CONNECT BY level <= p_cnt_rows
     AND rownum <= 32767;
     RETURN l_chr_rows;
    END;
    /

    SELECT * FROM TABLE(listrows_of_dual(7));

    SQL>
     
    FUNCTION created
     
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    1
    2
    3
    4
    5
    6
    7
     
    7 ROWS selected
     
    SQL>

     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Why? Without prior knowledge of the table structure you can't create a collection type to contain the returned data. You could run EXECUTE IMMEDIATE but that would also require an array to hold the returned data, an array you can't declare because at compile time you have no idea which table is being passed to the procedure. You could, I suppose, create an extremely long and convoluted procedure to test the passed table name to known tables and declare an array based on the match, but why bother?


    More information from you is needed before anyone could suggest a usable answer (if one exists).
     
  4. Vikram S

    Vikram S Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Guys,

    My requirement is to write a procedure or a function to return the table data.

    When we run query ' select * from table_name ' , we get all the rows and columns present in that table.

    Similarly i need to get the same output when i call the function or execute the procedure but the table should be dynamic.

    Thanks & Regards,
    Vikram S
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It isn't that easy; this example is for a four-table schema -- look at the work required to get this to actually return data:


    Code (SQL):

    SQL>
    SQL> CREATE OR REPLACE PROCEDURE select_any_table(p_tab IN varchar2)
      2  AS
      3        TYPE empcoltyp IS TABLE OF emp%rowtype;
      4        TYPE boncoltyp IS TABLE OF bonus%rowtype;
      5        TYPE deptcoltyp IS TABLE OF dept%rowtype;
      6        TYPE salgcoltyp IS TABLE OF salgrade%rowtype;
      7        emp_c empcoltyp;
      8        bonus_c boncoltyp;
      9        dept_c  deptcoltyp;
     10        salg_c  salgcoltyp;
     11  
     12        no_table_found exception;
     13        pragma exception_init(no_table_found, -942);
     14  
     15        tabcur  sys_refcursor;
     16  BEGIN
     17        OPEN tabcur FOR 'select * from '||p_tab;
     18        IF UPPER(p_tab) = 'EMP' THEN
     19         fetch tabcur bulk collect INTO emp_c;
     20  
     21         FOR i IN 1..emp_c.COUNT loop
     22          dbms_output.put_line(emp_c(i).empno||' '||emp_c(i).ename||' '||emp_c(i).job||' '||emp_c(i).sal);
     23         END loop;
     24        elsif UPPER(p_tab) = 'DEPT' THEN
     25         fetch tabcur bulk collect INTO dept_c;
     26  
     27         FOR i IN 1..dept_c.COUNT loop
     28          dbms_output.put_line(dept_c(i).deptno||' '||dept_c(i).dname||' '||dept_c(i).loc);
     29         END loop;
     30        elsif UPPER(p_tab) = 'BONUS' THEN
     31         fetch tabcur bulk collect INTO bonus_c;
     32  
     33         FOR i IN 1..bonus_c.COUNT loop
     34          dbms_output.put_line(bonus_c(i).ename||' '||bonus_c(i).job||' '||bonus_c(i).sal||' '||bonus_c(i).comm);
     35         END loop;
     36        elsif UPPER(p_tab) = 'SALGRADE' THEN
     37         fetch tabcur bulk collect INTO salg_c;
     38  
     39         FOR i IN 1..salg_c.COUNT loop
     40          dbms_output.put_line(salg_c(i).grade||' '||salg_c(i).losal||' '||salg_c(i).hisal);
     41         END loop;
     42        END IF;
     43        close tabcur;
     44  exception
     45        WHEN no_table_found THEN
     46         dbms_output.put_line('Table '||UPPER(p_tab)||' is not available.');
     47  END;
     48  /


    PROCEDURE created.


    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> EXEC select_any_table('emp');
    7369 SMITH CLERK 800                                                            
    7499 ALLEN SALESMAN 1600                                                        
    7521 WARD SALESMAN 1250                                                        
    7566 JONES MANAGER 2975                                                        
    7654 MARTIN SALESMAN 1250                                                      
    7698 BLAKE MANAGER 2850                                                        
    7782 CLARK MANAGER 2450                                                        
    7788 SCOTT ANALYST 3000                                                        
    7839 KING PRESIDENT 5000                                                        
    7844 TURNER SALESMAN 1500                                                      
    7876 ADAMS CLERK 1100                                                          
    7900 JAMES CLERK 950                                                            
    7902 FORD ANALYST 3000                                                          
    7934 MILLER CLERK 1300                                                          
    7939 DUKE CEO 5000                                                              
    7949 PRINCE CFO 5000                                                            
    7959 QUEEN CIO 5000                                                            
    7869 JACK PRESIDENT 5000                                                        


    PL/SQL PROCEDURE successfully completed.


    SQL> EXEC select_any_table('dept');
    10 ACCOUNTING NEW YORK                                                          
    20 RESEARCH DALLAS                                                              
    30 SALES CHICAGO                                                                
    40 OPERATIONS BOSTON                                                            


    PL/SQL PROCEDURE successfully completed.


    SQL> EXEC select_any_table('bonus');


    PL/SQL PROCEDURE successfully completed.


    SQL> EXEC select_any_table('salgrade');
    1 700 1200                                                                      
    2 1201 1400                                                                    
    3 1401 2000                                                                    
    4 2001 3000                                                                    
    5 3001 9999                                                                    


    PL/SQL PROCEDURE successfully completed.


    SQL> EXEC select_any_table('pismo');
    TABLE PISMO IS NOT available.                                                  


    PL/SQL PROCEDURE successfully completed.


    SQL>
     

    Notice that I did not return all columns from some of the tables; you would need to write a complete dbms_output line including all of the table columns to display the entire contents of a given table. Notice also that I created a row type for each table I would consider querying, then set up a collection for each table based on its specific row type. The dynamic cursor is opened and the data is bulk fetched into the collection specific to that table. The long IF statement ensures that the proper collection is associated with the supplied table name and, if a table name is supplied that cannot be queried an exception is thrown and a message generated.


    Again, such a task is not as simple as it sounds.
     
    Vikram S likes this.