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!

How to read sql (inside the column) from a table

Discussion in 'SQL PL/SQL' started by bmsreddy.100, Oct 25, 2013.

  1. bmsreddy.100

    bmsreddy.100 Guest

    Hi Experts,

    we have an requirement asking by client about

    How to read sql (inside the column) from a table and insert into another table.

    Currently we have 500 records in this table in future it will grow (dynamic)

    Read

    Ex: Table 1 Columns: Navigate NO (PK), Employee, SQL
    ROW 1 : 1 Elbert SELECT COUNT(*) FROM DIM_FUND
    ROW 2 : 2 Gary SELECT COUNT(*) FROM DIM_FY
    ROW 3 : 3 George SELECT COUNT(*) FROM DIM_APD

    Insert Table1 values

    Table 2 Columns: Navigate No (PK), Execution Date, Value (SQL count)
    ROW 1: 1 23-OCT-13 320
    ROW 2: 2 24-OCT-13 950
    ROW 3: 3 25-OCT-13 1220

    Any suggestion to make automate this execution.

    Thanks,
    -Madu
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need PL/SQL to do this:

    Code (SQL):
    SQL> CREATE TABLE user_sql(
      2  navigate_no        NUMBER,
      3  employee        varchar2(40),
      4  emp_sql         varchar2(4000),
      5  CONSTRAINT user_sql_pk PRIMARY KEY(navigate_no));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE user_cts(
      2  navigate_no        NUMBER,
      3  execute_dt DATE,
      4  ct_value        NUMBER,
      5  CONSTRAINT user_cts_pk PRIMARY KEY (navigate_no));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE dim_fund(
      2  stuff      NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE dim_fy(
      2  more_stuff NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE dim_apd(
      2  lotsa_stuff        NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO user_sql
      3  VALUES(1,'Elbert','SELECT COUNT(*) FROM DIM_FUND')
      4  INTO user_sql
      5  VALUES(2,'Gary','SELECT COUNT(*) FROM DIM_FY')
      6  INTO user_sql
      7  VALUES(3,'George','SELECT COUNT(*) FROM DIM_APD')
      8  SELECT * FROM dual;
     
    3 ROWS created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  IF MOD(i,17)=0 THEN
      4                          INSERT INTO dim_fund VALUES(i);
      5                  elsif MOD(i,37)= 0 THEN
      6                          INSERT INTO dim_fy VALUES(i);
      7                  ELSE
      8                          INSERT INTO dim_apd VALUES(i);
      9                  END IF;
     10          END loop;
     11
     12  END;
     13  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM user_sql;
     
    NAVIGATE_NO EMPLOYEE
    ----------- ----------------------------------------
    EMP_SQL
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------
              1 Elbert
    SELECT COUNT(*) FROM DIM_FUND
              2 Gary
    SELECT COUNT(*) FROM DIM_FY
              3 George
    SELECT COUNT(*) FROM DIM_APD

    SQL>
    SQL> DECLARE
      2          cursor c1 IS
      3          SELECT navigate_no, employee, emp_sql
      4          FROM user_sql;
      5
      6          v_sqlstmt       varchar2(32767);
      7  BEGIN
      8          FOR c1c IN c1 loop
      9          v_sqlstmt:='insert into user_cts (navigate_no, execute_dt) values ('||c1c.navigate_no||',sysdate)';
     10          EXECUTE immediate v_sqlstmt;
     11          v_sqlstmt:='update user_cts set ct_value =('||c1c.emp_sql||') where navigate_no = '||c1c.navigate_no;
     12          EXECUTE immediate v_sqlstmt;
     13          END loop;
     14
     15          commit;
     16
     17  END;
     18  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM user_cts;
     
    NAVIGATE_NO EXECUTE_D   CT_VALUE
    ----------- --------- ----------
              1 25-OCT-13         58
              2 25-OCT-13         26
              3 25-OCT-13        916
     
    SQL>