Check, if table is existing

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
/* ------------------------------------------------------------------------ */
/*
@Author
	Alexander Bolte
 
@ChangeDate
	2014-07-14
 
@Description
	Checking, if a table is existing under provided identifier / table name.
 
@Param tab_name
	a VarChar2 providing a valid identifier for a searched table.
 
@Return
	A Number(1) indicating, if a table is existing (=1) or not (=0).
*/
Function is_Table_Existing(tab_Name In VarChar2) Return Number Is
	is_existing Number(1) := 0;
	aTable VarChar2(256);
Begin
	select tname into aTable from tab where tname = upper(tab_name);
	If Not (aTable is Null) Then
		is_existing := 1;
	End If;
 
	Return is_existing;
 
Exception
	When Others Then
		Return is_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
Procedure test Is
	/* A dynamic collection holding Strings up to 256 characters with an index of type Bynary_Integer. */
	Type textCollection Is Table Of VarChar2(256 Char) Index By Binary_Integer;
	tab_names textCollection;
Begin
tab_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';
 
For r In 1 .. tab_names.Count
Loop
	If is_Table_Existing(tab_names(r)) = 1 Then
		dbms_output.put_line(
			LOCALTIMESTAMP || ' - ' || unit_test 
			|| ' - Success ' || tab_names(r) || '.'
		);
	End If;
End Loop;
End;

 

Hits: 5683