3 Replies Latest reply: Jan 1, 2013 3:36 AM by Etbin RSS

    Trouble with Dynamic SQL in Cursor

    Brent Grech
      Hello, my last question of the year ----building on my last question, I would like to create a new table that writes create and update history to a new table. First it reads USER_TAB_COLS to get the tables that I want, then creates a table based on the query. If I run the query standalone with a fixed table name, it works fine, however, when run this way, I get the following error which indicates it got the first row from my query.

      Error report:
      ORA-00904: "ADM_APPS_USERS": invalid identifier
      ORA-06512: at line 12
      00904. 00000 - "%s: invalid identifier"
      *Cause:   
      *Action:

      Much thanks and Happy New Year!
      -Brent


      DECLARE
      v_tname VARCHAR2(1000);
      cursor aud_tables_cur is
      select DISTINCT TABLE_NAME from user_tab_cols where column_name = 'LAST_UPDATE_DATE' order by 1;

      BEGIN
      open aud_tables_cur;
      LOOP
      fetch aud_tables_cur into v_tname;
      EXIT WHEN aud_tables_cur%NOTFOUND;

      execute immediate '
      create table BG_BUILD_AUDIT as WITH     created          AS
      (
           SELECT     TRUNC (CREATION_DATE)          AS date_created
           ,     COUNT (*)               AS rows_created FROM     '||v_tname||' GROUP BY TRUNC (CREATION_DATE)
      )
      ,     updated          AS
      (
           SELECT     TRUNC (last_update_date)     AS date_updated
           ,     COUNT (*)     AS rows_updated
           FROM '||v_tname||' GROUP BY TRUNC (last_update_date)
      )
      SELECT "'||v_tname||'" Table_Name, NVL ( c.date_created
           , u.date_updated)          AS DATA_DATE
      ,     NVL (c.rows_created, 0)      AS num_rows_created
      ,     NVL (u.rows_updated, 0)     AS num_rows_updated FROM created c
      FULL OUTER JOIN     updated u ON u.date_updated = c.date_created
      ORDER BY DATA_DATE';
      End Loop;
      close aud_tables_cur;
      Commit;
      END;
        • 1. Re: Trouble with Dynamic SQL in Cursor
          sb92075
          privilege acquired via ROLE do not apply within named PL/SQL procedures
          • 2. Re: Trouble with Dynamic SQL in Cursor
            rp0428
            >
            Hello, my last question of the year ----building on my last question, I would like to create a new table that writes create and update history to a new table. First it reads USER_TAB_COLS to get the tables that I want, then creates a table based on the query. If I run the query standalone with a fixed table name, it works fine, however, when run this way, I get the following error which indicates it got the first row from my query.

            Error report:
            ORA-00904: "ADM_APPS_USERS": invalid identifier
            ORA-06512: at line 12
            00904. 00000 - "%s: invalid identifier"
            *Cause:
            *Action:
            >
            You need to enclose the table name in single quotes, not double quotes.

            You are using this code with double quotes around 'v_tname'.
            SELECT "'||v_tname||'" Table_Name, NVL ( c.date_created
            Use single quotes
            SELECT '''||v_tname||''' Table_Name, NVL ( c.date_created
            The above may LOOK the same but it isn't.

            The standard way to write and test dynamic sql is to capture the query to be executed in a VARCHAR2 variable so you can print it out and test it separately.
            v_query VARCHAR2(4000);
            . . .
            v_query := 'create table BG_BUILD_AUDIT as WITH created AS . . .';
            execute immediate v_query;
            That makes it a lot easier to get the syntax correct.
            • 3. Re: Trouble with Dynamic SQL in Cursor
              Etbin
              Using [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF00218]Text Literals notation is another way to deal with quotes within quotes.

              Regards

              Etbin