Oracle & Batch:How to Check Multiple tables are Exists or not

Hi,
        I have a batch script which accepts multiple table names as comma delimited string.I need to check whether these tables exists or not in database.Here is code what i wrote till now.
@ECHO OFF
ECHO Table Names.
  ECHO.
  ECHO Enter Table Names & press ENTER(Seperated by ,):
  SET /p tabnames=
  ECHO.
o/p is :
Enter Table Names:
tab1,tab2,tab3......

my question is how to check this tables whether exists or not, before proceeding to Oracle EXP utility.
If table doesnot exists, proper error message should be displayed to user and that particualr table need to stripped(removed) from string.

tab1,tab2,tab3.........                           -- if tab2 not found in DB,     then new list is    tab1,tab3......

thanks
LVL 14
Deepak KosarajuDevOps EngineerAsked:
Who is Participating?
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.

sdstuberCommented:
change "tab1,tab2,tab3....."
to
"'tab1','tab2','tab3',......"

then use the following query

SELECT     MAX(SUBSTR(SYS_CONNECT_BY_PATH(table_name, ','), 2)) x
      FROM (SELECT table_name, ROW_NUMBER() OVER(ORDER BY table_name) curr,
                   ROW_NUMBER() OVER(ORDER BY table_name) - 1 prev
              FROM dba_tables
             WHERE table_name in (&your_table_list))
START WITH curr = 1
CONNECT BY PRIOR curr = prev

if you want the output tables to have quotes around them too, then use this instead

SELECT     MAX(SUBSTR(SYS_CONNECT_BY_PATH(table_name, ''','''), 3)) || ',' x
      FROM (SELECT table_name, ROW_NUMBER() OVER(ORDER BY table_name) curr,
                   ROW_NUMBER() OVER(ORDER BY table_name) - 1 prev
              FROM dba_tables
             WHERE table_name in (&your_table_list))
START WITH curr = 1
CONNECT BY PRIOR curr = prev
0
Deepak KosarajuDevOps EngineerAuthor Commented:
Hi Stuber,
                  can you make this code to write in Pl/sql? bcos iam confused about this stmt. is it possible to write something like this:

Enter tab names:
tab1,tab2,tab3,
sqlplus uname/pwd@server  @C:\Tablecheck.sql

Tablecheck.sql contains:
-----pl/sql block to check for table existing-->Trap Error if table doesnot exists-->convey this msg to User

it will be great if you can do in this way.
thanks
0
sdstuberCommented:
how are you passing your tab1,tab2,tab3 to tablecheck?

how about this....

sqlplus uname/pwd@server  @C:\Tablecheck.sql tab1,tab2,tab3  

Do you want an error if any table doesn't exist?  An error for each?  One error for all missing tables?

What do you want success to look like?

The above sql statement returns the data you asked for,  that is.  Given "'tab1','tab2','tab3'" as your_table_list  it would return "tab1,tab3" if tab2 didn't exist.
Try running it to see.  replace &your_table_list with a your real table names...

'YOUR_TABLE','YOUR_OTHER_TABLE','ETC'


SELECT     MAX(SUBSTR(SYS_CONNECT_BY_PATH(table_name, ','), 2)) x
      FROM (SELECT table_name, ROW_NUMBER() OVER(ORDER BY table_name) curr,
                   ROW_NUMBER() OVER(ORDER BY table_name) - 1 prev
              FROM dba_tables
             WHERE table_name in (&your_table_list))
START WITH curr = 1
CONNECT BY PRIOR curr = prev

I can wrap it in pl/sql, certainly, but tell me what you expect the output (success, failures and warnings) to be and how you'll pass the inputs in and what they'll look like when I get them.



0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Deepak KosarajuDevOps EngineerAuthor Commented:
Hi Stuber,
                  Let me rephrase my question. here is the scenario:
1) I want to pass table names like this:
sqlplus uname/pwd@server  @C:\Tablecheck.sql  tab1,tab2,tab3  
2) some pl/sql code(Tablecheck.sql) which checks for all tables like tab1,tab2,tab3...
3) if one or more tables not found, i want some error message that "following Tables does not exist:Tab1,tab3"
4)and pl/sql should return control to Batch file to run process from first step i.e
 "enter tables names:"(bcos its meaningless to proceed if atleast one table wont exist)
5)if all tables existed, convey user these are valid table names and return control to batch file, so that next step for EXP proceeds.
6)currently do not worry about stripping of non-existing table names as requiremnt changed that, once tables in list not found, stop process and ask user to enter some valid table names which are existing.

i hope you understood my question, let me know if you need any clarification
thanks
0
Deepak KosarajuDevOps EngineerAuthor Commented:
At last i developed solution: Remind you below pl/sql code is to check whether tables exists in DB. this code gets input from Batch file.format of Input is table1,table2,table3......After checking in DB the control is passed to Batch and displays result.

PL/SQL Code
declare
 v_cnt number :=0;
 l_tbl dbms_utility.uncl_array;
 l_str varchar2(200) := '&1';
 l_cnt binary_integer;
 l_tab varchar2(200);
begin
 if l_str is null then
  return;
 else
  dbms_utility.comma_to_table(l_str, l_cnt, l_tbl);
 end if;
 for i in 1..l_tbl.count-1 loop
  begin
        if l_tbl(i) is not null then
           execute immediate 'select count(*) as v_cnt from '||upper(l_tbl(i));
    end if;
  end;
 end loop;
end;
/
-----------------------------------------------------
Batch Code:
ECHO Enter Table Names,then press ENTER(Seperated by ,):
  SET /p tabnames=
 REM Checking Table(s) existing or not in Database
  ECHO Verifying Table(s) in Source Environment....
  ECHO.>_temp.txt
  ECHO.>>_temp.txt
    TYPE _temp.txt|SQLPLUS  %suname%/%spwd%@%senv% @"G:\tabecopy\tableexists.sql" %tabnames% >err_output.txt 2>&1
  DEL _temp.txt      
   FOR /f %%a IN ('type err_output.txt ^| findstr /i /c:"table or view does not exist"') DO ECHO One or More Table(s) does not Exist in Source %senv% &GOTO:ORAEXP
  ECHO Table(s):%tabnames% are valid at Source %senv%
  DEL err_output.txt

this solution is developed using  my previous posts
thanks
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
Vee_ModCommented:
Closed, 300 points refunded.
Vee_Mod
Community Support Moderator
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.