Stored procedure by date to create a new table, and delete the previous data, compile, stored procedure to test the DBMS have statements output, but there is no new table,

The create or replace procedure P_ZT_CREATE_102 as

V_crdate varchar2 (20);
V_dedate varchar2 (20);
V_sql varchar2 (4000);
V_table1 varchar2 (20);
V_table2 varchar2 (20);

The begin
V_crdate:=substr (to_char ((sysdate), 'yyyymmdd'), 3);
V_dedate:=substr (to_char ((sysdate - 5), 'yyyymmdd'), 3);
V_table1:='order_list_' | | v_crdate;
V_table2:='order_list_' | | v_dedate;

V_sql:='create table' | | v_table1 | | 'as select * from order_list';
Dbms_output. Put_line (v_sql);

V_sql:='drop table' | | v_table2;
Dbms_output. Put_line (v_sql);

The EXCEPTION
The WHEN OTHERS THEN
DBMS_OUTPUT. PUT_LINE (' create unsuccessful ');


End P_ZT_CREATE_102;

CodePudding user response:

How do you didn't commit to submit, new?

CodePudding user response:

You don't have to perform to build table statements,

Dbms_output. Put_line (v_sql);
The execute immediate v_sql; - add this sentence,

CodePudding user response:

V_sql:='create table' | | v_table1 | | 'as select * from order_list';
Dbms_output. Put_line (v_sql);
You just print your v_sql no perform (immediate) wow