asked on

Oracle script for creating/dropping table

database: Oracle 12c

Hi I need to create pl/sql to drop and create table, so that I don't have to do it manually each time.
Here are the scripts.
can I combine somehow in pl/sql. It will me lot of time to run it manually each time!
Thanks.
--------------
DROP TABLE My_schema.TEMP_Report_1 CASCADE CONSTRAINTS;

CREATE TABLE My_schema.TEMP_Report_1
(
POLICY_NUMBER VARCHAR2(9 BYTE),
SEQUENCE_NUMBER NUMBER(5) NOT NULL,
EFFECTIVE_DATE DATE NOT NULL,
COMPANYID VARCHAR2(1 BYTE),
BRANCHID VARCHAR2(1 BYTE),
NAME VARCHAR2(80 BYTE),
FULL_TERM_PREM NUMBER(12,2),
TXN_DATE DATE,
Report_1 NUMBER(12,2)
)
TABLESPACE SIMLOOK
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 520K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

GRANT DELETE, INSERT, SELECT, UPDATE ON My_schema.TEMP_Report_1 TO BA_SPECIAL_UPD;

GRANT SELECT ON My_schema.TEMP_Report_1 TO CLSERVICEROLE;

GRANT SELECT ON My_schema.TEMP_Report_1 TO EASIAPP;

GRANT SELECT ON My_schema.TEMP_Report_1 TO HELPDESK_ADMIN_ROLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON My_schema.TEMP_Report_1 TO OPERATOR;

Qlemo

You already have your script, so what is the issue? You can combine all lones into a single file. You might have to add a / at the script end for sqlplus to execute.

SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anil Lad

ASKER

1) when I put begin/end it gives me error:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>

2) I want drop only if table exists.
3) I want to create table only if does not exist.

4) This two scripts are to be run everyday couple of times..
Presently I have to drop the table (if it exists)
then run create the table. then do some activity and report result and then
I repeat the process again for different activity.

Hope that helps.
Thx

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Is this not the same question you asked from another account?

https://www.experts-exchange.com/questions/29076233/oracle-pl-sql-script-to-drop-create-table.html

Anil Lad

ASKER

yes. I will close that. It was not active account. I got mixed up. then I realized it posted on the wrong account.
I had to call Expert exchange to give me the right account.
Thanks.

ASKER CERTIFIED SOLUTION

Anil Lad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I also posted this in your other question but I wanted to make sure you read it:
PLEASE don't do this DDL in PL/SQL. It makes no sense. There should be NO REASON to drop and recreate the tables.

>>WHERE TABLE_NAME = 'TEMP_Report_1' AND owner = 'My_schema';

This will ALWAYS return an nCount of 0. By default Oracle creates objects with upper case names. You should NEVER alter this behaviour.

So your WHERE needs to be:
WHERE TABLE_NAME = 'TEMP_REPORT_1' AND owner = 'MY_SCHEMA';

There is also a possible issue using ALL_TABLES to look for it. If the user executing the query oesn't have access to the table it will not appear in ALL_TABLES.

Anil Lad

ASKER

this is what I was looking for.