Home » SQL & PL/SQL » SQL & PL/SQL » Create table in sqlplus script (oracle 11g, windows os)
Create table in sqlplus script [message #612297] Tue, 15 April 2014 17:31 Go to next message
anncao
Messages: 74
Registered: August 2013
Member
I need to write a sqlplus script to create a table in oracle database, first to check if it is there, if not then create the table.
This is use execute immediate dynamic query, if it is only table that will easier I just use single quote after execute immediate to wrap the sql statement. but now I have more stuff like constraints and an index.
So I don't know where I should put the end single quote.

Below is the script:
PROMPT Starting Create_SCH_RETENTIONS_Table.sql;
DECLARE cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO cnt
FROM user_tables
WHERE upper(table_name) = 'SCH_RETENTIONS';
IF cnt = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE "DR"."SCH_RETENTIONS"
( "STUDENTID" NUMBER(10,0),
"STUDENT_NUMBER" FLOAT(126),
"RECOMMEND_SCHOOL_YEAR" NUMBER(4,0),
"RECOMMEND_SCHOOL" NUMBER(10,0),
"RECOMMEND_NAME" VARCHAR2(30 BYTE),
"NEXT_YEAR_GRADE" NUMBER(10,0),
"SPRING_GRADE" NUMBER(10,0),
"RETENTION_REASON" NUMBER(1,0),
"RETAIN_OR_PROMOTE" VARCHAR2(1 BYTE),
"CREATE_USER" VARCHAR2(30 BYTE),
"CREATE_DATE" DATE,
"NOTES" VARCHAR2(1000 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUDR 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DR_DATA1"' ;
-- Comments for Columns
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."RECOMMEND_SCHOOL_YEAR" IS 'Next school year at the time Retention or Double Promotion recommended';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."RECOMMEND_SCHOOL" IS 'School making the recommendation (student home school)';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."RECOMMEND_NAME" IS 'Name of the individual making the recommendation';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."NEXT_YEAR_GRADE" IS 'Recommended grade level for recommended school year';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."SPRING_GRADE" IS ' Grade Level at the time recommendation is made';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."RETAIN_OR_PROMOTE" IS 'R=Retain; P=(Double)Promote';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."CREATE_USER" IS 'User inserting this record (making the recommendation)';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."CREATE_DATE" IS 'Date record inserted (date recommendation made)';
COMMENT ON COLUMN "DR"."SCH_RETENTIONS"."NOTES" IS 'Recommendation comments (why recommendation is made)';
COMMENT ON TABLE "DR"."SCH_RETENTIONS" IS 'Retentions and Double Promotions History';
--------------------------------------------------------
-- DDL for Index UK_SCH_RETENTIONS
--------------------------------------------------------
CREATE UNIQUE INDEX "DR"."UK_SCH_RETENTIONS" ON "DR"."SCH_RETENTIONS" ("RECOMMEND_SCHOOL_YEAR", "STUDENTID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUDR 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DR_INDEX1" ;
END IF;
END;
/
PROMPT Completed Create_SCH_RETENTIONS_Table.sql;

Re: Create table in sqlplus script [message #612298 is a reply to message #612297] Tue, 15 April 2014 17:44 Go to previous messageGo to next message
BlackSwan
Messages: 22809
Registered: January 2009
Senior Member
silly, foolish, ugly, & wasteful

just issue static SQL.
if the table already exist, error get thrown & can be ignored
Re: Create table in sqlplus script [message #612299 is a reply to message #612298] Tue, 15 April 2014 17:47 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
This is the template for a script for all the customized table build script. I have to follow this to use dynamic script. We don't want to throw errors, this is built in an automatic deployed script with other table creation scripts.
Re: Create table in sqlplus script [message #612300 is a reply to message #612299] Tue, 15 April 2014 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 22809
Registered: January 2009
Senior Member
The requirements are FLAWED & WRONG!
tables should be created once, using static SQL, & only during software version upgrades.
"dynamic tables" require only dynamic SQL; which does not scale.

Do you think that Oracle uses your proposed approach to deploy it's software?
I don't think so.

There is no legitimate reason to use double quotes within any SQL script.
double quotes should be avoided as 100% superfluous & potentially dangerous.
Re: Create table in sqlplus script [message #612303 is a reply to message #612297] Tue, 15 April 2014 20:20 Go to previous message
EdStevens
Messages: 287
Registered: September 2013
Senior Member
https://community.oracle.com/thread/3548033
Previous Topic: how to use joins in hierarchical queries
Next Topic: Help with PL Packages
Goto Forum:
  


Current Time: Tue Sep 23 17:47:55 CDT 2014

Total time taken to generate the page: 0.09020 seconds