OraFAQ Forum: SQL & PL/SQL » Searching through entire table.

Home » SQL & PL/SQL » SQL & PL/SQL » Searching through entire table.

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

Searching through entire table. [message #643826]

Mon, 19 October 2015 10:48

jimjohn
Messages: 12
Registered: July 2014

Junior Member

Hi All,
I have a table with n columns in it. Is it possible to have a query to search entire table to find out a particular text apart from using PLSQL.
Ex: Table A is having columns like Name, Address, Comment etc.
Is it possible to have a query to search though all these columns and find the occurrence of word 'oracle' in it?

Report message to a moderator

Re: Searching through entire table. [message #643827 is a reply to message #643826]

Mon, 19 October 2015 10:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

jimjohn wrote on Mon, 19 October 2015 08:48

Hi All,
I have a table with n columns in it. Is it possible to have a query to search entire table to find out a particular text apart from using PLSQL.
Ex: Table A is having columns like Name, Address, Comment etc.
Is it possible to have a query to search though all these columns and find the occurrence of word 'oracle' in it?

yes, it is possible, but it make sense to exclude columns which are datatype NUMBER, DATE, etc.

should the search be CaSe SenSiTiVe?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Searching through entire table. [message #643828 is a reply to message #643827]

Mon, 19 October 2015 11:03

jimjohn
Messages: 12
Registered: July 2014

Junior Member

Yes we can avoid data and integer columns.
But how can we instruct we need to select all varchar columns in the table.
I mean what should be the select query to dynamically fetching all the text columns.

Report message to a moderator

Re: Searching through entire table. [message #643829 is a reply to message #643828]

Mon, 19 October 2015 11:05

jimjohn
Messages: 12
Registered: July 2014

Junior Member

Sorry to ask is there any tolls for achieving the result.

Basically we need to audit DB and find out like personal data resides in the table.

Report message to a moderator

Re: Searching through entire table. [message #643830 is a reply to message #643826]

Mon, 19 October 2015 11:16

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Search for "find_string" function on AskTom.

[Updated on: Mon, 19 October 2015 11:16]

Report message to a moderator

Re: Searching through entire table. [message #643831 is a reply to message #643828]

Mon, 19 October 2015 11:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

jimjohn wrote on Mon, 19 October 2015 09:03

Yes we can avoid data and integer columns.
But how can we instruct we need to select all varchar columns in the table.
I mean what should be the select query to dynamically fetching all the text columns.

what about CLOB & LONG columns?

>Basically we need to audit DB and find out like personal data resides in the table.
programatically how to differentiate "personal data" from non-personal data?

Report message to a moderator

Re: Searching through entire table. [message #643838 is a reply to message #643829]

Mon, 19 October 2015 16:33

John Watson
Messages: 9003
Registered: January 2010
Location: Global Village

Senior Member

Cloud Control has a facility that lets you set up searches for columns that might control PID data. I came across it when looking at the redaction capability,
https://docs.oracle.com/cloud/latest/db121/ASOAG/redaction_em.htm#ASOAG10629
but I think it is more extensive than that. Is that what you are trying to do? Locate columns that contain sensitive data?

Report message to a moderator

Re: Searching through entire table. [message #643848 is a reply to message #643838]

Tue, 20 October 2015 02:54

jimjohn
Messages: 12
Registered: July 2014

Junior Member

Yes you are absolutely right.I am looking for sensitive data with tables.

The challenge is
1)We don't know which columns contains sensitive data.
2)We don't know know how many tables in the database.

Report message to a moderator

Re: Searching through entire table. [message #643849 is a reply to message #643848]

Tue, 20 October 2015 03:10

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 19 October 2015 18:16

Search for "find_string" function on AskTom.

Report message to a moderator

Re: Searching through entire table. [message #643962 is a reply to message #643849]

Fri, 23 October 2015 14:01

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If anyone is using toad, it has a search option against any object/schema in the database.
Use the
Search/Object Search tab in the toad menu. To use it, you need dba privileges to search across schemas

[Updated on: Fri, 23 October 2015 14:02]

Report message to a moderator

Re: Searching through entire table. [message #644030 is a reply to message #643826]

Mon, 26 October 2015 06:50

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

jimjohn wrote on Mon, 19 October 2015 10:48

Hi All,
I have a table with n columns in it. Is it possible to have a query to search entire table to find out a particular text apart from using PLSQL.

Why the restriction against PL/SQL?

Report message to a moderator

Previous Topic:	remote database migration
Next Topic:	How to keep only one branch after connect by

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 20 23:52:02 CDT 2026