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,
