3 Replies Latest reply: Mar 28, 2013 9:06 AM by Billy~Verreynne RSS

    PLSQL table in EXECUTE IMMEDIATE

    YLN
      Hi All,

      This is just a sample code.Table name may be changed in next run.

      when I run this program ,getting error in EXECUTE IMMEDIATE -" V_FIELDS invalid identifier",Also the way I am doing,that's right?

      create table TEMP
      (
      col1 VARCHAR2(200),
      col2 VARCHAR2(200),
      col3 DATE )
      /

      DECLARE
      v_fields dbms_sql.varchar2a;
      v_col_str VARCHAR2(200);
      v_insert VARCHAR2(200);
      v_idx INTEGER := 0;
      v_tab VARCHAR2(30) := 'TEMP';

      BEGIN
      FOR i IN (SELECT s.column_name, s.data_type
      FROM user_tab_cols s
      WHERE s.table_name = v_tab
      ORDER BY s.column_id)
      LOOP
      v_col_str := v_col_str || i.column_name || ',';
      IF i.data_type = 'DATE'
      THEN
      v_insert := v_insert || 'TO_DATE(v_fields(' || v_idx || '),' ||
      '''MM/DD/YYYY''' || '),';
      ELSE
      v_insert := v_insert || 'v_fields(' || v_idx || '),';
      END IF;
      v_idx := v_idx + 1;
      END LOOP;
      v_insert := '( ' || RTRIM(v_insert, ',') || ' )';
      v_col_str := 'INSERT INTO ' || v_tab || ' ( ' || RTRIM(v_col_str, ',') ||
      ' ) VALUES ';
      dbms_output.put_line(v_col_str || v_insert);

      v_fields(0) := 1;
      v_fields(1) := 'AB';
      v_fields(2) := SYSDATE;

      EXECUTE IMMEDIATE v_col_str || v_insert;
      EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line('Err: ' || SQLERRM);
      END;


      Thanks,
        • 1. Re: PLSQL table in EXECUTE IMMEDIATE
          BluShadow
          SQL statements cannot access variables or types declared inside PL/SQL code, they can only access types declared on the database itself.
          In 12c it's rumoured that this limitation will no longer be the case, but in the meantime, you need to ensure that the SQL can 'see' the types you expect it to use.
          • 2. Re: PLSQL table in EXECUTE IMMEDIATE
            Karthick_Arp
            The dynamic SQL generated is
             
            INSERT INTO TEMP ( COL1,COL2,COL3 ) VALUES ( v_fields(0),v_fields(1),TO_DATE(v_fields(2),'MM/DD/YYYY') ) 
            PL/SQL engine sends this SQL to SQL engine. And guess what, SQL Engine has no clue what v_fields is. Why? because v_fields a private variable defined in a PL/SQL block for which SQL Engine has zero access.

            Try this
             
            DECLARE 
                v_fields  dbms_sql.varchar2a; 
                v_col_str VARCHAR2(200); 
                v_insert  VARCHAR2(200); 
                v_idx     INTEGER := 0; 
                v_tab     VARCHAR2(30) := 'TEMP'; 
            
            BEGIN 
                FOR i IN (SELECT s.column_name, s.data_type 
                            FROM user_tab_cols s 
                           WHERE s.table_name = v_tab 
                           ORDER BY s.column_id) 
                LOOP 
                    v_col_str := v_col_str || i.column_name || ','; 
                    IF i.data_type = 'DATE' 
                    THEN 
                        v_insert := v_insert || 'TO_DATE(:' || v_idx || ',' || 
                                    '''MM/DD/YYYY''' || '),'; 
                    ELSE 
                        v_insert := v_insert || ':' || v_idx || ','; 
                    END IF; 
                    v_idx := v_idx + 1; 
                END LOOP; 
                v_insert  := '( ' || RTRIM(v_insert, ',') || ' )'; 
                v_col_str := 'INSERT INTO ' || v_tab || ' ( ' || RTRIM(v_col_str, ',') || 
                             ' ) VALUES '; 
            
                v_fields(0) := 1; 
                v_fields(1) := 'AB'; 
                v_fields(2) := to_char(SYSDATE, 'MM/DD/YYYY'); 
                 
                execute immediate v_col_str || v_insert using v_fields(0), v_fields(1), v_fields(2); 
            END; 
            / 
            I personally don't encourage dynamic SQL. Dynamic SQL is always a pain and a result of bad design.

            And the most worst thing, the thing that is terrible than dynamic SQL is this
             
            WHEN OTHERS THEN 
            dbms_output.put_line('Err: ' || SQLERRM); 
            END; 
            Whats the logic and possible benefit behind this code? Its NONE. Don't do that, NEVER. WHEN OTHERS without a RAISE is a bug in your code. So never do that. And DBMS_OUTPUT is not the correct way to render error message to user.
            • 3. Re: PLSQL table in EXECUTE IMMEDIATE
              Billy~Verreynne
              YLN wrote:

              Also the way I am doing,that's right?
              Nope, I would say it is horrible wrong.

              Technically, you need to use DBMS_SQL interface, and not execute immediate, in order to use dynamic binding.

              Perhaps if you tell us the problem you are trying to solve, we can offer suggestions. But the broken solution you've posted sample code of, is broken. Even if you managed to get it to compile without errors.

              Also consider using proper coding standards - like Ada 95 Quality and Style Guide standards. After all, PL/SQL is an implementation of the Ada language. Writing reserved words in upper case is not a standard. It is just silly.