Stored Procedure Coding Problem

[gaurim]

I am facing the coding proble in the following Stored Procedure wherein I am trying to create a table after deleting it. and then I am trying to insert records from two other tables based on certain condition. I am getting error(s) in it.
Can someone help me in solving it.
Regards

=============== =============== =============== ========
[code=oracle]

CREATE OR REPLACE PROCEDURE TMPDUMP IS

TMPINWARDNO NUMBER(6);
TMPDRN NUMBER(7);
TMPDPID VARCHAR2(8);
TMPCLID NUMBER(8);
TMPCONF_NO NUMBER(7);
TMPCONF_DT VARCHAR2(10);

DECLARE CURSOR C IS
select inwardno, drn, dpid, clid, conf_no, conf_dt from ndl.mstd where conf_dt is not null or conf_dt <> ' '
Union all
select inwardno, drn, dpid, clid, conf_no, conf_dt from cdl.mstd where conf_dt is not null or conf_dt <> ' ';
BEGIN
delete from TMPDT;
dbms_output.put _line('Data is deleted');
COMMIT;
create table TMPDT (INWARDNO NUMBER(6),DRN NUMBER(7),DPID VARCHAR2(8), CLID NUMBER(8), CONF_NO NUMBER(7), CONF_DT VARCHAR2(10));
OPEN C;
LOOP
fetch C into :INWARDNO,:DRN, :DPID, :CLID, :CONF_NO, :CONF_DT;
exit when c%NOTFOUND;
insert into inout.TMPDT values (INWARDNO,DRN,D PID, CLID, CONF_NO, CONF_DT);
END LOOP;
CLOSE C;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put _line(SQLERRM);
ROLLBACK;
END;
/
[/code]

=============== =============== =============== ========

[amitpatel66]

First thing is you CANNOT run DDL statements like that in a Stored program. You need to use Dynamic SQL method to execute your DDL Statement!!

[Saii]

In addition to that, delete statement deletes the rows but does not drop table so you cannot create an already existing table.

First thing is you CANNOT run DDL statements like that in a Stored program. You need to use Dynamic SQL method to execute your DDL Statement!!

[Mala232]

I am facing the coding proble in the following Stored Procedure wherein I am trying to create a table after deleting it. and then I am trying to insert records from two other tables based on certain condition. I am getting error(s) in it.
Can someone help me in solving it.
Regards

=============== =============== =============== ========
[code=oracle]

CREATE OR REPLACE PROCEDURE TMPDUMP IS

TMPINWARDNO NUMBER(6);
TMPDRN NUMBER(7);
TMPDPID VARCHAR2(8);
TMPCLID NUMBER(8);
TMPCONF_NO NUMBER(7);
TMPCONF_DT VARCHAR2(10);

DECLARE CURSOR C IS
select inwardno, drn, dpid, clid, conf_no, conf_dt from ndl.mstd where conf_dt is not null or conf_dt <> ' '
Union all
select inwardno, drn, dpid, clid, conf_no, conf_dt from cdl.mstd where conf_dt is not null or conf_dt <> ' ';
BEGIN
delete from TMPDT;
dbms_output.put _line('Data is deleted');
COMMIT;
create table TMPDT (INWARDNO NUMBER(6),DRN NUMBER(7),DPID VARCHAR2(8), CLID NUMBER(8), CONF_NO NUMBER(7), CONF_DT VARCHAR2(10));
OPEN C;
LOOP
fetch C into :INWARDNO,:DRN, :DPID, :CLID, :CONF_NO, :CONF_DT;
exit when c%NOTFOUND;
insert into inout.TMPDT values (INWARDNO,DRN,D PID, CLID, CONF_NO, CONF_DT);
END LOOP;
CLOSE C;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put _line(SQLERRM);
ROLLBACK;
END;
/
[/code]

=============== =============== =============== ========

hi
you cannot use direct DDL stmts in Procedure instead you have to use dynamic sql stmts

execute immediate' create table a (n number)';

and delete will remove records from the table not the table structure.
so use another
execute immediate ' drop table a' ;

this will work