3 Replies Latest reply: Feb 14, 2013 4:40 AM by 991047 RSS

    Help on diasaggregating table - PL/SQL

    991047
      Hello, I'm new to this forum and new to PL/SQL, too. Excuse my bad English.

      I've got a table TBL with 12 blocks of monthly date in each row, about 200 columns in total, like:

      ID, A_1, B_1, ..., Z_1, A_2, B_2, ..., Z_2,... ...,A_12, B_12, ..., Z_12

      Each of the 12 blocks will be treated similarly in an inner loop... ...and I'm looking for selecting the column names dynamically

      I've tried (for columns like A...)
      DECLARE
         SQL_STRING   VARCHAR2 (1000 BYTE);
          A NUMBER;
      BEGIN
         FOR C_ID IN (SELECT ID FROM TBL)
         LOOP
            FOR MONTH IN 1 .. 12
            LOOP
               SQL_STRING :=
                  'SELECT A_' || MONTH || ' INTO A FROM TBL WHERE ID = ' || C_ID.ID;
               EXECUTE IMMEDIATE SQL_STRING;
            --...
            END LOOP;
         --...
         END LOOP;
      END;
      that returns ORA-00905: Missing keyword

      Any idea how to manage it?

      Thanks in advance,
      Claus

      Edited by: user2499588 on 14.02.2013 11:24
        • 1. Re: Help on diasaggregating table - PL/SQL
          Karthick_Arp
          Your table design looks wrong. It would be better if its like this
          -----------------------------------------------------------
          ID     BLOCK_MONTH     A     B     C     D     ...
          -----------------------------------------------------------
          1     1
          1     2
          1     3
          1     4
          ...
          1     12
          -----------------------------------------------------------
          If you have it like this you will not need Dynamic SQL.
          'SELECT A_' || MONTH || ' INTO A FROM TBL WHERE ID = ' || C_ID.ID;
          SELECT .. INTO is a PL/SQL syntax. You cant use it in EXECUTE IMMEDIATE. EXECUTE IMMEDIATE passed the string to SQL engine for processing and SQL Engine has no idea about INTO clause in SELECT.

          So you need to remove the INTO in the dynamic string and use it in EXECUTE IMMEDIATE.

          Something like this
          SQL_STRING := 'SELECT A_' || MONTH || ' FROM TBL WHERE ID = :1';
          EXECUTE IMMEDIATE SQL_STRING INTO A UNING C_ID.ID;
          • 2. Re: Help on diasaggregating table - PL/SQL
            Chanchal Wankhade
            Hi,

            The below might be the issue.
            SQL> select 'A_'||SYSDATE||'DATE' A FROM DUAL;
            
            A
            ---------------
            A_14-FEB-13DATE
            
            SQL>
            As shown above "A_" and date is showing as a single word. as you have posted the query it is also the same. so you will have to add the space after the A_ like shown below
            SQL> select 'A_ '||SYSDATE||'DATE' A FROM DUAL;
            
            A
            ----------------
            A_ 14-FEB-13DATE
            by doing above your into clause will be feasible to oracle and hopefully solve the issue.
            • 3. Re: Help on diasaggregating table - PL/SQL
              991047
              Thank you!

              Of course the table design is wrong... ...but it's the design of a customer ;-)
              SQL_STRING := 'SELECT A_' || MONTH || ' FROM TBL WHERE ID = :1';
              EXECUTE IMMEDIATE SQL_STRING INTO A USING C_ID.ID;
              (adjusted typo)

              works fine :-)