| Help required in finding Attribute Name [message #196479] |
Thu, 05 October 2006 09:16  |
rravindhran
Messages: 7
Registered: March 2005
Location: India
|
Junior Member |
|
|
Hi:
In a table the value say 'A' can be stored in any column, now I have to figure out in which column 'A' is stored and I have to get the Column name so that now I have to navigate to the next column and get the value stored in the column.
I am able to find out using a function, is their any other way available.
Thanks in advance,
Ravindhran R
|
|
|
|
| Re: Help required in finding Attribute Name [message #196484 is a reply to message #196479] |
Thu, 05 October 2006 09:41  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
What sort of data model has values randomly inserted into different columns and you have to go searching to find out which one it is ?
You might be able to use some sort of case statement e.g.
case when column_1 = 'X' then column2
when column_2 = 'X' then column3
...
else null
But it's such a bizarre requirement that some sort of PL/SQL function is probably the best option.
[Updated on: Thu, 05 October 2006 09:41] Report message to a moderator |
|
|
|
| Re: Help required in finding Attribute Name [message #199585 is a reply to message #196479] |
Wed, 25 October 2006 03:41 |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
Try to use this stored procedure. It will definetly helps you.
It will search all the tables in the present schema and tells in which table and in which columns the Value is being present.
CREATE OR REPLACE PROCEDURE SEARCH_ALL_TABLES(v_datatype IN varchar2,v_search_str IN varchar2)
AS
CURSOR c_find IS
SELECT utc.table_name,utc.column_name FROM sys.user_tab_columns utc WHERE utc.data_type like upper(v_datatype);
l_sql_orig VARCHAR2(70) := 'SELECT NULL FROM SYS.DUAL WHERE '|| 'EXISTS (SELECT NULL FROM ';
l_sql VARCHAR2(32000);
l_dummy VARCHAR2(1);
l_tables VARCHAR2(255);
l_counter PLS_INTEGER := 0;
l_start DATE;
BEGIN
l_start := SYSDATE;
FOR c IN c_find LOOP
l_counter := l_counter + 1;
l_sql := l_sql_orig|| c.table_name|| ' WHERE '|| c.column_name|| ' = :bind_var)';
BEGIN
EXECUTE IMMEDIATE l_sql INTO l_dummy USING v_search_str;-- This is case sensitive. each value will gets substituted in :bind_var
l_tables := l_tables || c.table_name || '.' || c.column_name|| '|';
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
DBMS_OUTPUT.PUT_LINE('Error during '|| c.table_name|| '.'|| c.column_name);
EXIT;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(l_tables);
END;
/
SQL> exec search_all_tables('varchar2','SMITH')
EMP.ENAME|
PL/SQL procedure successfully completed.
SQL> exec search_all_tables('number','10')
DEPARTMENTS.DEPTNO|DEPT.DEPTNO|EMP.DEPTNO|EMP_ORG.RIGHT|
PL/SQL procedure successfully completed.
SQL> exec search_all_tables('date','17-DEC-80')
EMP.HIREDATE|
PL/SQL procedure successfully completed.
SQL> exec search_all_tables('date','17-DEC-1980')
EMP.HIREDATE|
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
