OraFAQ Forum: SQL & PL/SQL » Create Table if not Exists.

Home » SQL & PL/SQL » SQL & PL/SQL » Create Table if not Exists.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create Table if not Exists. [message #39089]

Fri, 14 June 2002 03:25

Bhagavan
Messages: 4
Registered: June 2002

Junior Member

I want to check if the table does not exist then create the table using the below stored procedure, but this works only in 8i, and gives error in 8.0.5
Can anyone help ?

Thanks in advance
Bhagavan.

--------------------------

set serveroutput on
declare
vResult VARCHAR(5);
lstrsql VARCHAR2(500);
begin
SELECT DECODE(count(*),0,'False','True') INTO vResult FROM user_tables WHERE table_name = 'TBLAPPLIEDSCRIPTS';
if vResult = 'False' then
lstrsql:='CREATE TABLE TBLAPPLIEDSCRIPTS (DATEAPPLIED DATE DEFAULT (sysdate), APPLIEDBY VARCHAR2 (40) DEFAULT (user), SCRIPTNAME VARCHAR2 (50), SCRIPTAUTHOR VARCHAR2 (4), SCRIPTVERSION VARCHAR2 (8), SCRIPTDESCRIPTION VARCHAR2 (100) )';
EXECUTE_IMMEDIATE lstrsql;
dbms_output.put_line('Table Created');
end if;
end;
/

Report message to a moderator