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;
ALad2005Asked:
Who is Participating?
 
ALad2005Connect With a Mentor Author Commented:
thank you guys.
Here's my revised script
 
DECLARE
   nCurUSExposure   NUMBER := 0;
   nCount           NUMBER := 0;
BEGIN
   --**************************************************************************
   --* 1. Drop temp tables CCL/AR
   --**************************************************************************
   -- 1a  drop CCL table
   BEGIN
      EXECUTE IMMEDIATE
         'DROP TABLE My_schema.TEMP_Report_1 CASCADE CONSTRAINTS';
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   -- 1a  drop AR table
   BEGIN
      EXECUTE IMMEDIATE
         'DROP TABLE My_schema.TEMP_Report_1 CASCADE CONSTRAINTS';
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   --**************************************************************************
   --* 2. Create CCL/AR Tables
   --**************************************************************************
   BEGIN
      --2a Create CCL Table
      SELECT COUNT (*)
        INTO nCount
        FROM ALL_TABLES
       WHERE TABLE_NAME = 'TEMP_Report_1' AND owner = 'My_schema';

      IF nCount = 0
      THEN
         EXECUTE IMMEDIATE
            '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,
                                  US_Exposure                      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';

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

         EXECUTE IMMEDIATE
            'GRANT SELECT ON My_schema.TEMP_Report_1 TO CLSERVICEROLE';

         EXECUTE IMMEDIATE
            'GRANT SELECT ON My_schema.TEMP_Report_1 TO EASIAPP';

         EXECUTE IMMEDIATE
            'GRANT SELECT ON My_schema.TEMP_Report_1 TO HELPDESK_ADMIN_ROLE';

         EXECUTE IMMEDIATE
            'GRANT DELETE, INSERT, SELECT, UPDATE ON My_schema.TEMP_Report_1';
      END IF;
   end;
   
   end;
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
I totally agree with Qlemo. Actually we do not really know your specs or what you want to achieve?!?
Btw: you also may call DDL from within PL/SQL with "EXECUTE IMMEDIATE"...
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ALad2005Author Commented:
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
0
 
awking00Connect With a Mentor Commented:
I'm not 100% clear on what you need to accomplish, but perhaps something like the following might work for you.
First, create your TEMP_REPORT_1 table with the desired privilege grants. Then run PL/SQL script similar to -
DECLARE
v_cnt number;
begin
select count(*) into v_cnt from temp_report_1;
if v_cnt > 0 then
 execute immediate 'truncate table temp_report_1';
end if;
<Do what you need to do with the temp_report_1 table>
end;
/
0
 
slightwv (䄆 Netminder) Commented:
0
 
ALad2005Author Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
ALad2005Author Commented:
this is what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.