Recently I had to check over and over again, if tables, which I needed for testing are existing in an Oracle database.
Since checking for existance and completion repeatedly involved the same tasks I decided to implement little PL SQL script, which does the task for me.
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/* ------------------------------------------------------------------------ *//*@AuthorAlexander Bolte@ChangeDate2014-07-14@DescriptionChecking, if a table is existing under provided identifier / table name.@Param tab_namea VarChar2 providing a valid identifier for a searched table.@ReturnA Number(1) indicating, if a table is existing (=1) or not (=0).*/Functionis_Table_Existing(tab_NameInVarChar2)ReturnNumberIsis_existingNumber(1):=0;aTableVarChar2(256);BeginselecttnameintoaTablefromtabwheretname=upper(tab_name);IfNot(aTableisNull)Thenis_existing:=1;EndIf;Returnis_existing;ExceptionWhenOthersThenReturnis_existing;End;
The script is searching in a system table for the given name of a table.
Table names are stored in capital characters in this table, which is why 'Upper()' is applied.
In case you want to check the existance of several tables you loop through a collection, as outlined in below example.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ProceduretestIs/* A dynamic collection holding Strings up to 256 characters with an index of type Bynary_Integer. */TypetextCollectionIsTableOfVarChar2(256Char)IndexByBinary_Integer;tab_namestextCollection;Begintab_names(1):='sod_category_app';tab_names(2):='sod_cat_app_cat';tab_names(3):='sod_category_ent';tab_names(4):='sod_cat_ent_cat';ForrIn1..tab_names.CountLoopIfis_Table_Existing(tab_names(r))=1Thendbms_output.put_line(LOCALTIMESTAMP||' - '||unit_test||' - Success '||tab_names(r)||'.');EndIf;EndLoop;End;