OraFAQ Forum: SQL & PL/SQL » HOW To search for a string in the schema/Database ?

Home » SQL & PL/SQL » SQL & PL/SQL » HOW To search for a string in the schema/Database ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HOW To search for a string in the schema/Database ? [message #125307]

Fri, 24 June 2005 06:25

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Good after noon(INDIA)...

Suppose I have a scheme USER1 under Database DB1.

I want to search the Tables in the Schema Or database itself for a string as its column element not as column name.
ie, I would like to seach for 'Accountant' in the USER1 Or DB1 as a column element
Some times seaches for 'Accountant' in the USER1 Or DB1 as an element of column 'Job'

Is there any data dictionary that can be used for seaching a string as column element under a scheme or database.(I dont think So ....).

Then I would like to create a procedure /function for doing the same using User_tab_columns by varrying the input parametres(search_string,column,schema/database...

can anyone helpme.

Rajuvan.

Report message to a moderator

Re: HOW To search for a string in the schema/Database ? [message #125312 is a reply to message #125307]

Fri, 24 June 2005 06:59

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Try something like this code.

DECLARE
    CURSOR c_find_value IS
        SELECT utc.table_name
        ,      utc.column_name
        FROM   sys.user_tab_columns  utc
        WHERE  utc.data_type    = 'VARCHAR2'
        AND    utc.data_length >= 10 -- Length of 'Accountant'
    ;
    l_sql_orig   VARCHAR2(80) := 'SELECT NULL FROM SYS.DUAL WHERE '
                                 || 'EXISTS (SELECT NULL FROM ';
    l_sql           VARCHAR2(2000);
    l_dummy         VARCHAR2(1);
    l_tables        VARCHAR2(32765);
    l_counter       PLS_INTEGER := 0;
    l_found         PLS_INTEGER := 0;
    l_start         NUMBER := DBMS_UTILITY.GET_TIME;
    l_break_at      POSITIVE := 132; -- Must be <= 255
BEGIN
    FOR c IN c_find_value LOOP
        l_counter := l_counter + 1;
        l_sql := l_sql_orig
                 || c.table_name
                 || ' WHERE '
                 || c.column_name
                 || ' = :bv1)';
        BEGIN
            EXECUTE IMMEDIATE l_sql
                         INTO l_dummy
                        USING 'Accountant';
            l_found := l_found + 1;
            l_tables := l_tables
                        || c.table_name
                        || '.'
                        || c.column_name
                        || '|';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(l_counter || ' columns analyzed.');
    DBMS_OUTPUT.PUT_LINE(l_found || ' matches found.');
    IF (l_found > 0) THEN
        FOR j IN 1..CEIL(LENGTH(l_tables) / l_break_at) LOOP
            DBMS_OUTPUT.PUT_LINE(SUBSTR(l_tables
                                 ,      (((j - 1) * l_break_at) + 1)
                                 ,      LEAST(LENGTH(l_tables)
                                              -
                                              ((j - 1) * l_break_at)
                                        ,     l_break_at))
                                 );
        END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Took '
                         || TO_CHAR((DBMS_UTILITY.GET_TIME - l_start) * 0.01)
                         || ' seconds.');
END;
/

Report message to a moderator

Re: HOW To search for a string in the schema/Database ? [message #125329 is a reply to message #125307]

Fri, 24 June 2005 09:10

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

I got the answer .Thank U .
Not Tried ....
But In Your reply.

Quote:

FOR c IN c_find_value LOOP
l_counter := l_counter + 1;
l_sql := l_sql_orig
|| c.table_name
|| ' WHERE '
|| c.column_name
|| ' = :bv1)';
BEGIN
EXECUTE IMMEDIATE l_sql
INTO l_dummy
USING 'Accountant';

Here in this case Using 'Accountant' should be replaced with
Using 'JOB' (c.column_name).

Then 'Accountant' does not come to the light

Then how should it be rewritten ????
Rajuvan

Report message to a moderator

Re: HOW To search for a string in the schema/Database ? [message #125333 is a reply to message #125329]

Fri, 24 June 2005 09:32

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

I'm confused...

Do you want the table name whose column name = 'JOB' and that also has a column value of 'Accountant'? The code I listed gives you a superset of this subset, hence I'm not certain of your requirement here.

But if I understand you correctly, then your cursor would be

SELECT utc.table_name
FROM   sys.user_tab_columns  utc
WHERE  utc.data_type   = 'VARCHAR2'
AND    utc.column_name = 'JOB'

...and your loop would look like....

FOR c IN c_find_value LOOP
    l_counter := l_counter + 1;
    l_sql := l_sql_orig
             || c.table_name
             || ' WHERE job = :bv1)';
    BEGIN
        EXECUTE IMMEDIATE l_sql
                     INTO l_dummy
                    USING 'Accountant';
        l_found := l_found + 1;
        l_tables := l_tables
                    || c.table_name
                    || '.job|';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
    END;
END LOOP;

If I have misunderstood your requirement, please elaborate with an example.

Report message to a moderator

Re: HOW To search for a string in the schema/Database ? [message #125494 is a reply to message #125307]

Mon, 27 June 2005 00:48

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Thanxxx!!!!!!

Frst time U misunderstood 'Accountant' as Columnname. the U gave the real answer i wanted.... Now its working....

SQL> DECLARE
  2      CURSOR c_find_value IS
  3          SELECT utc.table_name
  4     FROM   sys.user_tab_columns  utc
  5     WHERE  utc.data_type   = 'VARCHAR2'
  6     AND    utc.column_name = 'ENAME';
  7      l_sql_orig   VARCHAR2(80) := 'SELECT NULL FROM SYS.DUAL WHERE '
  8                                   || 'EXISTS (SELECT NULL FROM ';
  9      l_sql           VARCHAR2(2000);
 10      l_dummy         VARCHAR2(1);
 11      l_tables        VARCHAR2(32765);
 12      l_counter       PLS_INTEGER := 0;
 13      l_found         PLS_INTEGER := 0;
 14      l_start         NUMBER := DBMS_UTILITY.GET_TIME;
 15      l_break_at      POSITIVE := 132; -- Must be <= 255
 16  BEGIN
 17      FOR c IN c_find_value LOOP
 18          l_counter := l_counter + 1;
 19          l_sql := l_sql_orig
 20               || c.table_name
 21               || ' WHERE ENAME = :bv1)';
 22          BEGIN
 23              EXECUTE IMMEDIATE l_sql
 24                           INTO l_dummy
 25                          USING 'RAJUVAN';
 26              l_found := l_found + 1;
 27              l_tables := l_tables
 28                      || c.table_name
 29                      || '.job|';
 30          EXCEPTION
 31              WHEN NO_DATA_FOUND THEN
 32                  NULL;
 33          END;
 34      END LOOP;
 35      DBMS_OUTPUT.PUT_LINE(l_counter || ' columns analyzed.');
 36      DBMS_OUTPUT.PUT_LINE(l_found || ' matches found.');
 37      IF (l_found > 0) THEN
 38          FOR j IN 1..CEIL(LENGTH(l_tables) / l_break_at) LOOP
 39              DBMS_OUTPUT.PUT_LINE(SUBSTR(l_tables
 40                                   ,      (((j - 1) * l_break_at) + 1)
 41                                   ,      LEAST(LENGTH(l_tables)
 42                                                -
 43                                                ((j - 1) * l_break_at)
 44                                          ,     l_break_at))
 45                                   );
 46          END LOOP;
 47      END IF;
 48      DBMS_OUTPUT.PUT_LINE('Took '
 49                           || TO_CHAR((DBMS_UTILITY.GET_TIME - l_start) * 0.01)
 50                           || ' seconds.');
 51* END;
SQL> /
14 columns analyzed.
5 matches found.
AONE.job|EM.job|EMP.job|EMP_V.job|XYZ.job|
Took .02 seconds.

PL/SQL procedure successfully completed.
SQL>

Thank u once again.

Can U Help meto write generalised procedure that does the same job(that accepts the 2 varying parameters -column element and column name)..Somtimes column name is NULL !!!!! Wink

ie,

Quote:

EXEC search_tab('Accountant','JOB');
EXEC search_tab('Accountant');
EXEC search_tab(col_name =>'JOB');

I am also trying the same.......

Rajuvan

Report message to a moderator

Re: HOW To search for a string in the schema/Database ? [message #125547 is a reply to message #125307]

Mon, 27 June 2005 08:05

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

I have tried a lot and got the resultant procedure as follows :

create or replace procedure search_tab(col_el varchar2,col_nm varchar2 default NULL)
IS
   utc_v varchar2(500);
   TYPE rfcur is ref cursor;
   rf rfcur;
   utctype sys.user_tab_columns%rowtype;
   l_sql_orig   VARCHAR2(80) := 'SELECT NULL FROM SYS.DUAL WHERE '
                                 || 'EXISTS (SELECT NULL FROM ';
    l_var      VARCHAR2(10) :='VARCHAR2';
    l_sql           VARCHAR2(2000);
    l_dummy         VARCHAR2(1);
    l_tables        VARCHAR2(32765);
    l_counter       PLS_INTEGER := 0;
    l_found         PLS_INTEGER := 0;
    l_start         NUMBER := DBMS_UTILITY.GET_TIME;
    l_break_at      POSITIVE := 132;
BEGIN
   utc_v := 'SELECT utc.table_name ,utc.column_name FROM sys.user_tab_columns  utc WHERE 
        utc_v := utc_v|| 'utc.data_type  = '||''''||l_var||'''' ;
IF col_nm is not null then
   utc_v :=utc_v || ' AND utc.column_name = '||''''||col_nm||'''';
End If;
dbms_output.put_line(utc_v);
Open rf for utc_v;
LOOP
 l_counter := l_counter + 1;
 fetch rf into utctype;
 exit when rf%notfound;
 l_sql := l_sql_orig|| utctype.table_name|| ' where '|| utctype.column_name|| ' = '
          ||''''||col_el||''')';
BEGIN
       dbms_output.put_line(l_sql);
            EXECUTE IMMEDIATE l_sql
                         INTO l_dummy;
            l_found := l_found + 1;
            l_tables := l_tables
                        || utctype.table_name
                        || '.'
                        || utctype.column_name
                        || '|';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(l_counter || ' columns analyzed.');
    DBMS_OUTPUT.PUT_LINE(l_found || ' matches found.');
    IF (l_found > 0) THEN
        FOR j IN 1..CEIL(LENGTH(l_tables) / l_break_at) LOOP
            DBMS_OUTPUT.PUT_LINE(SUBSTR(l_tables
                                 ,      (((j - 1) * l_break_at) + 1)
                                 ,      LEAST(LENGTH(l_tables)
                                              -
                                              ((j - 1) * l_break_at)
                                        ,     l_break_at))
                                 );
        END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Took '
                         || TO_CHAR((DBMS_UTILITY.GET_TIME - l_start) * 0.01)
                         || ' seconds.');
end;

Will it be executed nicely?????

Rajuvan

Report message to a moderator

Previous Topic:	Outerjoin - Please help!!
Next Topic:	Interesting....Column names in to rows ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:01:32 CDT 2024