CODEWITHSUNDEEP
oracle-databaseplsql
Aman
Aman

Reputation: 19

How to use this execute immediate create table to insert values in table

I am using execute immediate to create table and use this table into my code to insert some values when i run this code .it gives me error that table and view does not exist.why?. this code has ability to create table because when i use drop and create table command with existing table by using execute immediate then my code works fine and when i just remove drop table syntax, my code does not work, Please help me to clear my concept about dynamic SQL-----------

SET serveroutput ON
DECLARE
  ddl_table VARCHAR2(200);
  r_emp     SYS.ODCINUMBERLIST   := SYS.ODCINUMBERLIST();
  v_array   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('ACCT_ID',
                                                         'PARENT_ACCT_ID',
                                                         'CUST_ID',
                                                         'ACCT_NAME',
                                                         'BILLING_CYCLE_TYPE',
                                                         'PAID_FLAG',
                                                         'BILL_DELIVER_METHOD');
BEGIN
  ddl_table := 'CREATE TABLE test123(
                                      v_column VARCHAR2(50),
                                      v_count  NUMBER
                                    )';
  EXECUTE IMMEDIATE ddl_table;
  DBMS_OUTPUT.ENABLE;
  FOR i IN 1 .. v_array.COUNT LOOP
    r_emp.EXTEND;
    EXECUTE IMMEDIATE 'SELECT COUNT(*) 
                         FROM account_profile 
                        WHERE NOT REGEXP_LIKE(' ||v_array(i) || ',''[A-Za-z0-9.]'')'
      INTO r_emp(i);

    IF r_emp(i) <> 0 THEN
      DBMS_OUTPUT.PUT_LINE(v_array(i) || ': ' || r_emp(i));
      INSERT INTO test123 VALUES (v_array(i), r_emp(i));
    END IF;
  END LOOP;
END;
/

Error report -

ORA-06550: line 24, column 17:
PL/SQL: ORA-00942: table or view does not exist

Upvotes: 1

Views: 602

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23381

Your problem is that the anonymous block is pre-validated (pre-compiled) before it is valid. Oracle will check all objects in use before executing it. Since you are creating test123 dynamically it doesn't exist so your insert statement fails.

You can instead, use an EXECUTE IMMEDIATE command to also insert the data on your test123 table.

The way you use the EXECUTE IMMEDIATE for an insert command is either concatenating the parameters or preparing them, I prefer the later. Like this

execute immediate 'insert into test123 values (:param1,:param2)' using v_array(i), r_emp(i);

Here is the official documentation for the EXECUTE IMMEDIATE command.

Though it shows how it works and explain the usage of it, it doesn't particularly answer you direct question on the comments. So

can you explain :param1,:param2

Those are called "binding" parameters that will be replaced by the variables used after the using statement. It doesn't matter their name only the order in which they appear on the string. The number of parameters within the dynamic string needs to match the number of parameters after the using statement.

why we use it with : colon and what are these

The colon : there is just to make it easier for the parser to know what to replace and where when using the variables you provided after the using statement

Translating that execute immediate it would be something like this:

... values (:param1,:param2)' using v_array(i), r_emp(i)

Hey "execute immediate" whenever you see :param1 please replace it with the content of the variable I'm providing as v_array(i) and whenever you see :param2 please replace it with the content of the variable I'm providing as r_emp(i)

Upvotes: 1

Related Questions