1 2 Previous Next 21 Replies Latest reply on Nov 27, 2010 11:19 AM by William Robertson

    table name as variable in loop

    the_slk
      Hallo

      I'm trying to use code like:

      DECLARE
      CURSOR cv_table IS SELECT table_name FROM user_tables WHERE table_name LIKE 'TEST%';
      v_table_name CHAR(16);
      BEGIN
      OPEN cv_table;
      LOOP
      EXIT WHEN cv_table%NOTFOUND;
      FETCH cv_table INTO v_table_name;
      select * from v_table_name where rownum<2;
      END LOOP;
      END proc_cs_fast_stats;


      Error report:
      PL/SQL: SQL Statement ignored
      06550. 00000 - "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:

      any ideas? thanks a lot for answer.
        • 1. Re: table name as variable in loop
          21205
          you would need dynamic sql to do that.
          A tablename cannot be bound.
          • 2. Re: table name as variable in loop
            the_slk
            what is it dynamic sql? google says it got something in common with microsoft.
            • 3. Re: table name as variable in loop
              BluShadow
              slkLinuxUser wrote:
              Hallo

              I'm trying to use code like:

              DECLARE
              CURSOR cv_table IS SELECT table_name FROM user_tables WHERE table_name LIKE 'TEST%';
              v_table_name CHAR(16);
              BEGIN
              OPEN cv_table;
              LOOP
              EXIT WHEN cv_table%NOTFOUND;
              FETCH cv_table INTO v_table_name;
              select * from v_table_name where rownum<2;
              a) you can't use variable table names (as mentioned already, you can't bind table names, only values)
              b) Just where is the select statment supposed to put the data it's retrieving? If the table name is dynamic then the resultant columns would be dynamic too.
              END LOOP;
              END proc_cs_fast_stats;
              • 4. Re: table name as variable in loop
                Karthick2003
                I dont find any objective here, Do you?

                Ok lets see your code.
                DECLARE
                  CURSOR cv_table 
                  IS 
                  SELECT table_name 
                    FROM user_tables 
                   WHERE table_name LIKE 'TEST%';
                  
                  v_table_name CHAR(16);
                BEGIN
                  OPEN cv_table;
                  
                  LOOP
                    EXIT WHEN cv_table%NOTFOUND;
                    FETCH cv_table INTO v_table_name;
                  
                    select * 
                      from v_table_name 
                     where rownum<2;
                
                  END LOOP;
                END proc_cs_fast_stats;
                Point 1: When you open a cursor please close it.
                Point 2: In PL/SQL SELECT statement must be followed by INTO. You must store your selected value.
                Point 3: v_table_name is a variable name. So you cannot use it in a static SQL. You must create a dynimic SQL string and execute i using EXECUTE IMMEDIATE.

                And finally ask your self why you came up with such a crazy idea ;)
                • 5. Re: table name as variable in loop
                  BluShadow
                  slkLinuxUser wrote:
                  what is it dynamic sql? google says it got something in common with microsoft.
                  Nothing to do with microsoft. It means that the query is built up at runtime rather than known at compile time.

                  Example:
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  declare
                    2    cursor c_table is
                    3      select table_name
                    4      from user_tables
                    5      where table_name like 'EMP%';
                    6    v_cnt        NUMBER;
                    7  begin
                    8    for t in c_table
                    9    loop
                   10      execute immediate 'select count(*) from '||t.table_name INTO v_cnt;
                   11      DBMS_OUTPUT.PUT_LINE('Table: '||t.table_name||' ('||v_cnt||' rows)');
                   12    end loop;
                   13* end;
                  SQL> /
                  Table: EMP (14 rows)
                  Table: EMP2 (14 rows)
                  Table: EMPLOYEES (4 rows)
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  • 6. Re: table name as variable in loop
                    21205
                    Nothing to do with Microsoft.

                    It has to do with EXECUTE IMMEDIATE, something like this:
                    SQL> DECLARE
                      2     CURSOR cv_table IS SELECT table_name FROM user_tables WHERE table_name LIKE 'TEST%';
                      3     v_table_name varCHAR2(30);
                      4     c number;
                      5  BEGIN
                      6     OPEN cv_table;
                      7     LOOP
                      8        FETCH cv_table INTO v_table_name;
                      9        EXIT WHEN cv_table%NOTFOUND;
                     10        execute immediate 'select count(*) cnt from '||v_table_name ||' where rownum < 2'
                     11         into c;
                     12        dbms_output.put_line('Count in '||v_table_name||': '||to_char (c) );
                     13     END LOOP;
                     14  END proc_cs_fast_stats;
                     15  /
                    Count in TEST_TABLE1: 1
                    
                    PL/SQL procedure successfully completed.
                    But explain what you want to do, then we can provide you with better advise of how to handle this in Oracle.
                    • 7. Re: table name as variable in loop
                      the_slk
                      I got to count few things from couple of tables and put it in summar tables. It has to be done physically.
                      • 8. Re: table name as variable in loop
                        21205
                        slkLinuxUser wrote:
                        It has to be done physically.
                        What does this mean "done physically"
                        If you only want to count things, just use COUNT(*)
                        • 9. Re: table name as variable in loop
                          the_slk
                          ok I can do that but how to get ride of quotation marks?
                          • 10. Re: table name as variable in loop
                            the_slk
                            physically means it has to be in tables, unfortunately
                            • 11. Re: table name as variable in loop
                              21205
                              sorry, don't get it..

                              what needs to be in tables?
                              can do that but how to get ride of quotation marks?
                              which quotation marks are you talking about?
                              • 12. Re: table name as variable in loop
                                BluShadow
                                slkLinuxUser wrote:
                                ok I can do that but how to get ride of quotation marks?
                                The quotation marks are required to make your query dynamic.
                                You want dynamic table names, therefore you need a dyanmic query, therefore you need quotation marks.
                                • 13. Re: table name as variable in loop
                                  the_slk
                                  execute immediate 'INSERT INTO stats SELECT to_date(to_char(current_date-2, 'YYYYMMDD') || ' 00:00:00', 'YYYYMMDD HH24:MI:SS')'

                                  this is an example again

                                  Do you know what I mean?

                                  between query quotation there are to_date function qutation
                                  • 14. Re: table name as variable in loop
                                    the_slk
                                    ok found it


                                    Description

                                    Use this statement to enable dynamically-constructed statements to be executed from within a procedure.
                                    Syntax 1

                                    EXECUTE IMMEDIATE [ execute-option ] string-expression

                                    execute-option:
                                    WITH QUOTES [ ON | OFF ]
                                    | WITH ESCAPES { ON | OFF }
                                    | WITH RESULT SET { ON | OFF }
                                    Syntax 2

                                    EXECUTE ( string-expression )
                                    Parameters

                                    WITH QUOTES When you specify WITH QUOTES or WITH QUOTES ON, any double quotes in the string expression are assumed to delimit an identifier. When you do not specify WITH QUOTES, or specify WITH QUOTES OFF, the treatment of double quotes in the string expression depends on the current setting of the QUOTED_IDENTIFIER option.

                                    WITH QUOTES is useful when an object name that is passed into the stored procedure is used to construct the statement that is to be executed, but the name might require double quotes and the procedure might be called when QUOTED_IDENTIFIER is set to OFF.

                                    For more information, see the QUOTED_IDENTIFIER option [compatibility].

                                    WITH ESCAPES WITH ESCAPES OFF causes any escape sequences (such as \n, \x, or \\) in the string expression to be ignored. For example, two consecutive backslashes remain as two backslashes, rather than being converted to a single backslash. The default setting is equivalent to WITH ESCAPES ON.

                                    One use of WITH ESCAPES OFF is for easier execution of dynamically-constructed statements referencing filenames that contain backslashes.

                                    In some contexts, escape sequences in the string-expression are transformed before the EXECUTE IMMEDIATE statement is executed. For example, compound statements are parsed before being executed, and escape sequences are transformed during this parsing, regardless of the WITH ESCAPES setting. In these contexts, WITH ESCAPES OFF prevents further translations from occurring. For example:

                                    BEGIN
                                    DECLARE String1 LONG VARCHAR;
                                    DECLARE String2 LONG VARCHAR;
                                    EXECUTE IMMEDIATE
                                    'SET String1 = ''One backslash: \\\\ ''';
                                    EXECUTE IMMEDIATE WITH ESCAPES OFF
                                    'SET String2 = ''Two backslashes: \\\\ ''';
                                    SELECT String1, String2
                                    END
                                    1 2 Previous Next