SQLPlus Check if table exist in ShellScript

Question

I'm trying to create a ShellScript that connects to sqlplus and verify that a table exists or not, in case there is no exist the script will create it.

Table can be empty.

I tried this:

#!/bin/bash

sqlplus -s user/pass> tabs << EOF


SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
   e_not_exist EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_not_exist, -942);
   tab_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO tab_count
   FROM table_name;

   DBMS_OUTPUT.PUT_LINE(tab_count);
EXCEPTION
  when e_not_exist then
    dbms_output.put_line('Table or view does not exists');

END;
/
EXIT
EOF

tabcount=`cat tabs`
echo You have $tabcount tables.

Ouput:

You have 0 tables, when table exist. (empty)
You have N tables, when table exist. (not empty)
You have FROM table_name; ERROR at line 7: ORA-06550: line 7, column 9: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 6, column 4: PL/SQL: SQL Statement ignored tables.

chrisis chrisis · Accepted Answer · 2019-01-30T13:49:48

Try SELECT table_name FROM all_tables WHERE table_name = 'your_table_name'

all_tables is a dictionary view that does what its name implies.

SQLPlus Check if table exist in ShellScript

1 Answers