How to drop and create table again and again-CodePudding

when i use this code first time ,it create the table but i want to use this code again and again but second time when i use it ,it will drop the table but does not create table again . kindly help me to correct the code according to my requirement.

    set serverout on
DECLARE
 table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist,-00942);
  ddl_qry     VARCHAR2 (200);
  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_qry:='Drop Table Accnt_Profile_Spcl';
   EXECUTE IMMEDIATE ddl_qry; 
  exception
   when table_or_view_does_not_exist then
   dbms_output.put_line('There is no error');
   GOTO end_point;
   <<end_point>>
   ddl_table := 'create table Accnt_Profile_Spcl(
                        column_name varchar2(50),
                        spcl_char_count number)';
    EXECUTE IMMEDIATE ddl_table;
dbms_output.put_line('Table has been created');
  ---------DBMS_OUTPUT.ENABLE;
  FOR i IN 1..v_array.COUNT LOOP
    r_emp.EXTEND;
    EXECUTE IMMEDIATE
       'SELECT /* parallel(16)*/ 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));
    execute immediate 'insert into Accnt_Profile_Spcl values (:param1,:param2)' using v_array(i), r_emp(i);
end if;
  END LOOP;
  END;

CodePudding user response：

That's bad practice. You should really avoid dropping and creating tables that way, that's not how Oracle is supposed to work. Create table once, at SQL level, and reuse it as many times as you want. Just remove its contents, if you want / have to.

Anyway: here's how you could do that:

SQL> declare
  2    l_cnt number;
  3  begin
  4    select count(*) into l_cnt
  5      from user_tables
  6      where table_name = 'ACCNT_PROFILE_SPCL';
  7
  8    if l_cnt = 1 then
  9       execute immediate 'drop table ACCNT_PROFILE_SPCL';
 10
 11       dbms_output.put_line('Table dropped');
 12    end if;
 13
 14    execute immediate 'create table ACCNT_PROFILE_SPCL ' ||
 15                      '  (column_name       varchar2(50),' ||
 16                      '   spcl_char_count   number)';
 17
 18    dbms_output.put_line('Table created');
 19  end;
 20  /
Table dropped
Table created

PL/SQL procedure successfully completed.

SQL> /
Table dropped
Table created

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Use a nested block to handle the exception and not the main block:

DECLARE
  ddl_qry     VARCHAR2 (200);
  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
  DECLARE
    table_or_view_does_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_or_view_does_not_exist,-00942);
  BEGIN
    ddl_qry:='Drop Table Accnt_Profile_Spcl';
    EXECUTE IMMEDIATE ddl_qry; 
  EXCEPTION
    WHEN table_or_view_does_not_exist THEN
      dbms_output.put_line('There is no error');
  END;

  ddl_table := 'create table Accnt_Profile_Spcl(
                column_name varchar2(50),
                spcl_char_count number)';
  EXECUTE IMMEDIATE ddl_table;
  dbms_output.put_line('Table has been created');

  FOR i IN 1..v_array.COUNT LOOP
    r_emp.EXTEND;
    EXECUTE IMMEDIATE
       'SELECT /* parallel(16)*/ 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
      execute immediate 'insert into Accnt_Profile_Spcl values (:param1,:param2)' using v_array(i), r_emp(i);
    end if;
  END LOOP;
END;
/

fiddle