Find Database Objects

[Alan Jacobson]

The Find Database Objects function does not always use bind variables.

If you are not using dba view, there are four SQL statements where is does not use bind variables. They are: (these are some examples)

SELECT table_name
FROM sys.all_constraints
WHERE constraint_name = 'PK_Z_VEHICLE_FUEL' AND
owner = 'RSIPLATE' AND
constraint_type IN ('P', 'U');

SELECT column_name
FROM sys.all_cons_columns
WHERE owner = 'RSIPLATE' AND
table_name = 'Z_SECURITY_LEVEL' AND
constraint_name = 'PK_Z_SECURITY_LEVEL'
ORDER BY position;

SELECT col.*,
com.comments
FROM sys.all_tab_columns col,
sys.all_col_comments com
WHERE col.owner = 'RSIPLATE' AND
col.table_name = 'Z_TRANSACTION_TYPE_SECURITY' AND
com.owner(+) = 'RSIPLATE' AND
com.table_name(+) = 'Z_TRANSACTION_TYPE_SECURITY' AND
com.column_name(+) = col.column_name
ORDER BY col.column_id;

SELECT *
FROM sys.all_constraints
WHERE table_name = 'Z_TRANS_TYPE_AUTO_REVIEW' AND
owner = 'RSIPLATE' AND
constraint_type IN ('P', 'U', 'R', 'C')
ORDER BY decode(constraint_type,'P', 0, 'U', 1, 'R', 2, 3),constraint_name;

If you are using dba_views, then three of the above statements use bind variables but one does not. It is:

SELECT col.*,
com.comments
FROM sys.dba_tab_columns col,
sys.dba_col_comments com
WHERE col.owner = 'RSIPLATE' AND
col.table_name = 'FLEET_RENEWAL_PRINT_COUNT' AND
com.owner(+) = 'RSIPLATE' AND
com.table_name(+) = 'FLEET_RENEWAL_PRINT_COUNT' AND
com.column_name(+) = col.column_name
ORDER BY col.column_id;

Could you change these to all use bind variables?

When you have 1000+ tables, it would be nice if it did not flood the shared pool with one time only SQL statements.

Thanks

 

[Marco Kalter]

I have added this to the list of enhancement requests.