Home » SQL & PL/SQL » SQL & PL/SQL » Create Table if not Exists.
Create Table if not Exists. [message #39089] Fri, 14 June 2002 03:25 Go to next message
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;
/
Re: Create Table if not Exists. [message #39092 is a reply to message #39089] Fri, 14 June 2002 07:08 Go to previous messageGo to next message
Suresh
Messages: 189
Registered: December 1998
Senior Member
I think execute_immediate is available only from 8i and for 8.0.5 you need to user DBMS_SQL.
Re: Create Table if not Exists. [message #39099 is a reply to message #39092] Sun, 16 June 2002 03:25 Go to previous message
M.P.KIRAN KUMAR
Messages: 9
Registered: May 2002
Junior Member
i hope if u use user_objects instead of user_tables it will work for all objects...

bye!
Previous Topic: executing a string in oracle......
Next Topic: select data which does not in join condition
Goto Forum:
  


Current Time: Thu Oct 18 03:59:57 CDT 2018