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,
... View more
