1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Search string all tables

Discussion in 'SQL PL/SQL' started by treze17, Jul 28, 2016.

  1. treze17

    treze17 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Roma
    Hello,
    as a string you can search through all tables of a database ?
    Thanks
     
  2. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    Do you need to search a value from all tables?


    If u want to display information about all the tables then select * from all_tables..
     
    Last edited: Jul 28, 2016
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    what means "as a string you can search through all tables of a database " ?

    You will need one of options :
    1)
    ALL_TABLES

    Code (SQL):
    SELECT * FROM all_tables


    2)
    ALL_TAB_COLUMNS
    Code (SQL):
    SELECT c.OWNER,c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE
    FROM all_tab_columns c
    WHERE c.DATA_TYPE IN ('CHAR','VARCHAR2','VARCHAR','CLOB')
     
    Last edited: Jul 28, 2016
  4. treze17

    treze17 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Roma

    I have to search for a string in all records of all tables.
    it's possible?
     
  5. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    Could You please give us an example of that?
     
    Last edited: Jul 28, 2016
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What do you want to make?
    Can you provide an example of your task?
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hope U're looking for this..

    Code (SQL):
    SET serveroutput ON;
    DECLARE
    lcount NUMBER;
    lquery varchar2(200);
    BEGIN
    FOR DATA IN( SELECT * FROM user_tab_columns)
    loop
      lquery:= 'select count(*) from '||DATA.TABLE_NAME;--||' where '||data.column_name||' = '||' ';
       EXECUTE immediate lquery INTO lcount;
    IF lcount >0 THEN
    dbms_output.put_line(DATA.column_name||'-----'||DATA.TABLE_NAME);
    END IF;
    END loop;
    END;
    Try using Where Clause for String Search.

    For Reference: https://community.oracle.com/message/10589317#10589317
     
  8. treze17

    treze17 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Roma
    Example: I would like to know in which tables the string is contained 'hello'
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Unfortunately that's not what the original question was asking for as there is no 'string' to search the columns for. This should work:

    Code (SQL):
    SQL> SET echo ON serveroutput ON SIZE 1000000 verify off
    SQL>
    SQL> DECLARE
      2          v_string_in varchar2(4000):='&srchstrg';
      3          v_found NUMBER:=NULL;
      4          cursor get_tab_cols IS
      5          SELECT TABLE_NAME, column_name
      6          FROM user_tab_columns
      7          WHERE data_type LIKE '%CHAR%';
      8          v_sqltxt        varchar2(32767);
      9          no_privs        exception;
    10          pragma exception_init(no_privs, -1031);
    11  BEGIN
    12          FOR tabcolrec IN get_tab_cols loop
    13                  v_sqltxt:='select 1 from '||tabcolrec.TABLE_NAME||' where '||tabcolrec.column_name||' = '''||v_stri
    ng_in||'''';
    14                  BEGIN
    15                  EXECUTE immediate v_sqltxt INTO v_found;
    16                  IF v_found = 1 THEN
    17                          dbms_output.put_line('******** '''||v_string_in||''' found in '||tabcolrec.TABLE_NAME||'.'|
    |tabcolrec.column_name);
    18                  END IF;
    19                  exception
    20                  WHEN NO_DATA_FOUND THEN
    21                          dbms_output.put_line(''''||v_string_in||''' not found in '||tabcolrec.TABLE_NAME||'.'||tabc
    olrec.column_name);
    22                  WHEN no_privs THEN NULL;
    23                  END;
    24          END loop;
    25
    26  END;
    27  /
    Enter VALUE FOR srchstrg: FORD
    'FORD' NOT found IN DEPT.LOC
    'FORD' NOT found IN DEPT.DNAME
    'FORD' NOT found IN T1.COL1
    'FORD' NOT found IN BONUS.ENAME
    'FORD' NOT found IN EMP.JOB
    ******** 'FORD' found IN EMP.ENAME
    'FORD' NOT found IN T2.COL2
    'FORD' NOT found IN BONUS.JOB

    PL/SQL PROCEDURE successfully completed.

    SQL>
    Notice this is actually what the poster wants.
     
    Siddhartha likes this.