0

I am new to Linux, I want to check if a table does not exist in Oracle than create a new table using Linux shell script to write the code. I probably need to do an if else statement?

Here is my code for creating table in Oracle

Create table traffic_profile_external
(
  TRAFFIC_PROFILE_ID    NUMBER,     
  PE_INGRESS_FLAG       VARCHAR2(1),
  PE_EGRESS_FLAG        VARCHAR2(1),
  CE_INGRESS_FLAG       VARCHAR2(1),
  CE_EGRESS_FLAG        VARCHAR2(1),
  COS_PROFILE_TYPE      VARCHAR2(10)
);
user5071535
  • 1,312
  • 8
  • 25
  • 42
yesco1
  • 371
  • 2
  • 7
  • 22

2 Answers2

0

You can see whether a table exists by querying dba_|all_|user_tables depending on your privileges, whether you're potentially creating tables in other schemas, etc. So you can do something like

select count(*)
  from all_tables
 where owner = <<schema that owns the table>>
   and table_name = 'TRAFFIC_PROFILE_EXTERNAL'

and then based on the result that is returned decide whether to execute the CREATE TABLE statement. You can do that in an anonymous PL/SQL block but then you'd have to use dynamic SQL (via the EXECUTE IMMEDIATE statement) in order to run the CREATE TABLE statement. You could also fetch the result of the SELECT statement into a variable in your shell script and branch based on that.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
-1

Try this:

Create table IF NOT EXISTS traffic_profile_external
(
   TRAFFIC_PROFILE_ID    NUMBER,     
   PE_INGRESS_FLAG       VARCHAR2(1),
   PE_EGRESS_FLAG        VARCHAR2(1),
   CE_INGRESS_FLAG       VARCHAR2(1),
   CE_EGRESS_FLAG        VARCHAR2(1),
   COS_PROFILE_TYPE      VARCHAR2(10)
);
Hatem Jaber
  • 2,341
  • 2
  • 22
  • 38