Click to See Complete Forum and Search --> : create table or insert into table


dsd7038
09-18-2003, 12:37 PM
How can I check if table exists and if not create one else insert into table. Please advise. See example below as create command fails as expects end etc..
DECLARE

counter INTEGER := 0;
mytotal INTEGER := 0;

BEGIN

SELECT
COUNT( 1 )
INTO
counter
FROM
user_tables
WHERE
table_name like 'testing';

mytotal := mytotal + counter;


IF ( mytotal != 0 ) THEN
INSERT
INTO testing (implemented)
VALUES ('testing123');
Else
CREATE TABLE testing (
implemented varchar2 (40),
date_run date
)
TABLESPACE mytblspace;



END;
/

DaPi
09-18-2003, 01:16 PM
You will need to use dynamic SQL for DDL i.e. the Create statement. Look up EXECUTE IMMEDIATE in the doc.