Home » SQL & PL/SQL » SQL & PL/SQL » Find a value in more than a table (10g R2)
Find a value in more than a table [message #404107] Wed, 20 May 2009 07:10 Go to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
Hello
How please to find a specific value in more than one table and different columns
like " find the value 12345 in all HR schema tables and their colums not a specific column"

is that possible
thanks
Re: Find a value in more than a table [message #404112 is a reply to message #404107] Wed, 20 May 2009 07:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
maoro wrote on Wed, 20 May 2009 08:10

is that possible



Sure, if you write some code for it. You'll need to parse all the table names and column names and use where clauses naming every column.

You can start by looking at user_tab_columns.
Re: Find a value in more than a table [message #404116 is a reply to message #404107] Wed, 20 May 2009 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "find_string" function here and on asktom.

Regards
Michel
Re: Find a value in more than a table [message #404124 is a reply to message #404107] Wed, 20 May 2009 08:31 Go to previous messageGo to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
thanks for kind help
i tried using the find_string procedure
but it dos not work
this is the output of the sqlplus session

**********************
Procedure created.

SQL> show errors;
No errors.
SQL> exec find_string('clara');

PL/SQL procedure successfully completed.

SQL> exec find_string('Clara');

PL/SQL procedure successfully completed.

SQL> exec find_string('%lar%');

PL/SQL procedure successfully completed.

SQL> exec find_string('%');

PL/SQL procedure successfully completed.
*****************

it just returns
PL/SQL procedure successfully completed
please help
Re: Find a value in more than a table [message #404126 is a reply to message #404124] Wed, 20 May 2009 08:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what happens if you put 'SET SERVEROUTPUT ON SIZE 20000' before the EXEC, so that you can actually see the output it produces?
Re: Find a value in more than a table [message #404229 is a reply to message #404126] Thu, 21 May 2009 01:03 Go to previous message
maoro
Messages: 310
Registered: May 2005
Senior Member
you are awesome
it works like a charm
many thanks to you
Previous Topic: How to extract CLOB data into text file?
Next Topic: Cartesian Join
Goto Forum:
  


Current Time: Fri Dec 02 16:38:34 CST 2016

Total time taken to generate the page: 0.21352 seconds