topic Re: How to find a specific value in an Oracle database in SAS Enterprise Guide

How to find a specific value in an Oracle database

alepage — Thu, 15 Jul 2021 15:21:07 GMT

Hello,

Like many organizations, we have many databases which contains many tables and for which we have little documentation. So, in some case, it is difficult to find the good table to carry out some tasks.

So I wonder it there is a way to find a specific value into a table.

For example, imagine that we have the oracle data base named dbase1 which contains many tables and I would like to look into each table and retains only the table name containing a specific value, let's say the value test.

Is there an efficient way to do that in sas.

Regards,

Re: How to find a specific value in an Oracle database

Reeza — Thu, 15 Jul 2021 15:29:53 GMT

Oracle, like SAS, has dictionary tables.
https://docs.oracle.com/cd/B19306_01/server.102/b14220/datadict.htm

You could search all tables for all values but it would be very resource intensive - are you trying to search for a variable or a value?

Re: How to find a specific value in an Oracle database

alepage — Thu, 15 Jul 2021 15:44:10 GMT

I want to look for few specific string value and hopefully those will appears into the same tables. Then I would look at those table to select which one will be more sustainable for my needs.

Re: How to find a specific value in an Oracle database

Reeza — Thu, 15 Jul 2021 15:53:07 GMT

Do you have access to PASS THRU SQL?

https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/

Or you can use a SAS data step + CALL EXECUTE/DOSUBL to generate and run the same code. It will take a long time.

Re: How to find a specific value in an Oracle database

SASKiwi — Thu, 15 Jul 2021 20:04:25 GMT

Surely you have staff in your organisation that are responsible for creating and maintaining the tables in these databases. Why not ask them to help find the best source of the data you are looking for? A data dictionary wont tell you what is the best or most appropriate source for the data you want.

Re: How to find a specific value in an Oracle database

alepage — Fri, 16 Jul 2021 15:39:40 GMT

Yes, I can use PASS THRU SQL

Re: How to find a specific value in an Oracle database

alepage — Thu, 22 Jul 2021 22:37:01 GMT

Hello,

I have try many things. The first table is populated but the table test1 and test2 are empty. Of course due to that factor,

I can't use the table cols for the last script. Moreover, for the last script, I am getting this error:

ERROR: ORACLE execute error: ORA-01008: not all variables bound.

Heres my codes. Please note that some letter are replace by ??? for safety purpose.

Also, I was note able to declare a val variable and to set it at predefined value such as KING, CFNAUL, in my case

**********************

SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /
************************

***********************

%let TblName=list_of_tables;
%let WantedValue='CFNAUL';
%let authdomain=ORACLE_SAS_???_HUBPROD;
%let path=%sysfunc(scan("&authdomain.",-1,"_"));

working .....

proc sql;
connect to oracle(authdomain="&authdomain" path="&path");

create table &TblName. as
select * from connection to oracle
( select (&WantedValue.) as val,
owner,
Table_Name,
Column_Name as Field_Name

from all_tab_columns
)
order by owner, table_name, Field_Name;
quit;

Not working. The cols table as variables but no values.

proc sql;
connect to oracle(authdomain="&authdomain" path="&path");

create table test as
select * from connection to oracle
( select (&WantedValue.) as val,
/* owner,*/
Table_Name,
Column_Name as Field_Name

from cols
)
order by /*owner,*/ table_name, Field_Name;
quit;

Not working. The table cols has variables but no values.

proc sql;
connect to oracle(authdomain="&authdomain" path="&path");

create table test2 as
select * from connection to oracle
( select *

from cols
)
;
quit;

This one , I can't use the table cols and I am getting a bounding error.

proc sql;
connect to oracle(authdomain="&authdomain" path="&path");

create table &TblName. as
select * from connection to oracle
( select (&WantedValue.) as val,

Table_Name,
Column_Name as Field_Name

from all_tab_columns,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
|| column_name
|| ' from '
|| table_name
|| ' where upper('
|| column_name
|| ') like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t

);
quit;

Does someone could help me with those issue such as how to declare variable and assign value to it , while using

the SQL pass through facility.

Also, could I use another table then all_tab_columns because the cols does not have value in it.

Regards,