Home » SQL & PL/SQL » SQL & PL/SQL » Help required in finding Attribute Name
Help required in finding Attribute Name [message #196479] Thu, 05 October 2006 09:16 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.

Previous Topic: Err LPX-00216
Next Topic: History tables
Goto Forum:
  


Current Time: Mon Dec 05 11:12:53 CST 2016

Total time taken to generate the page: 0.14569 seconds