| Create table in sqlplus script [message #612297] |
Tue, 15 April 2014 17:31  |
 |
anncao
Messages: 87
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;
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
