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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", 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]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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
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
ALad2005Author 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.