Solved

Search Database in PL/SQL in Oracle

Posted on 2008-10-06
34
352 Views
Last Modified: 2013-12-19
I want to write a function to search the entire database and show a list of table name, field name that contains record which match my query string...I have an compilation error, anyone can help me?
CREATE OR REPLACE FUNCTION Search_Database(

	pt_string IN VARCHAR2)

IS
 

	mt_table_name VARCHAR2;

	mt_column_name VARCHAR2;

	is_found NUMBER;
 

	CURSOR get_table_column_list()

	IS

		SELECT table_name, column_name 

		FROM user_tab_columns

		WHERE table_name IN (

			SELECT table_name

			FROM 

user_tables

		);
 

	CURSOR find_record(

		pt_table_name IN  VARCHAR2,

		pt_column_name IN VARCHAR2,

		pt_string IN VARCHAR2

	)

	IS

		SELECT COUNT(*) FROM pt_table_name WHERE pt_column_name LIKE '%pt_string%';

BEGIN

	OPEN get_table_column_list;
 

		FETCH get_table_column_list INTO mt_table_name, mt_column_name;

		

		OPEN find_record(mt_table_name, mt_column_name, pt_string);
 

			FETCH find_record INTO is_found;
 

			IF is_found > 0 THEN

				SELECT mt_table_name, mt_column_name FROM dual;

			END IF;

		

		CLOSE find_record;
 

	CLOSE get_table_column_list;
 

END Search_Database;

/

Open in new window

0
Comment
Question by:mawingho
  • 15
  • 11
  • 6
  • +2
34 Comments
 
LVL 22

Assisted Solution

by:Ivo Stoykov
Ivo Stoykov earned 25 total points
ID: 22647879
Hi mawingho
your query string, so quoted sould be string, right? What abount numbers and dates?
Would you quote some sample query strings?
I
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 25 total points
ID: 22647939
why not use a stored procedure and an out reference cursor ?

you will need to put it all in a package
CREATE PACKAGE pkg_search AS

   TYPE GenCurTyp IS REF CURSOR;

   PROCEDURE search (search_text in VARCHAR2, cur_results OUT GenCurTyp);

END pkg_search;
 

CREATE PACKAGE BODY pkg_search AS

   PROCEDURE search (search_text in VARCHAR2, cur_results OUT GenCurTyp) IS

   BEGIN

     OPEN cur_results FOR 

       SELECT table_name, column_name 

       FROM user_tab_columns 

       WHERE upper(column_name) like '%' || upper(search_text) || '%'; 

   END;

END pkg_search;

Open in new window

0
 

Author Comment

by:mawingho
ID: 22647976
How to run it?
0
 

Author Comment

by:mawingho
ID: 22647982
Hey hey, I am not searching column name, I'm serach each record, and return the table name and column name if record found.
0
 

Author Comment

by:mawingho
ID: 22648075
I need it in function...anyone can help?
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 175 total points
ID: 22648146


CREATE OR REPLACE FUNCTION Search_Database(
pt_string IN VARCHAR2) return VARCHAR2
IS

mt_table_name user_tab_columns.TABLE_NAME%type;
mt_column_name user_tab_columns.column_NAME%type;
is_found NUMBER;
mt_sql varchar2(4000);
ret_sql VARCHAR2(4000);

CURSOR get_table_column_list
IS
SELECT distinct table_name, column_name
FROM user_tab_columns WHERE TABLE_NAME IN ('MDL','CRRNCY') ORDER BY TABLE_NAME;




BEGIN
OPEN get_table_column_list;
loop

FETCH get_table_column_list INTO mt_table_name, mt_column_name;
EXIT WHEN get_table_column_list%NOTFOUND;
is_found:=0;


mt_sql:= 'SELECT COUNT(*) FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39);

execute immediate mt_sql into is_found;

if is_found>0 then
ret_sql:= ret_sql||chr(13)||chr(10)||'Table: '||mt_table_name||' Col: '|| mt_column_name;
end if;

end loop;

CLOSE get_table_column_list;

RETURN ret_sql;

END Search_Database;
/
0
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 175 total points
ID: 22648176
Caution: searching for a keyword in all columns entire schema is not a good idea as u may not have indexes on all the columns
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 175 total points
ID: 22648194
there is a filter in above function pl ignore it

CREATE OR REPLACE FUNCTION Search_Database(
pt_string IN VARCHAR2) return VARCHAR2
IS

mt_table_name user_tab_columns.TABLE_NAME%type;
mt_column_name user_tab_columns.column_NAME%type;
is_found NUMBER;
mt_sql varchar2(4000);
ret_sql VARCHAR2(4000);

CURSOR get_table_column_list
IS
SELECT distinct table_name, column_name
FROM user_tab_columns ORDER BY TABLE_NAME;




BEGIN
OPEN get_table_column_list;
loop

FETCH get_table_column_list INTO mt_table_name, mt_column_name;
EXIT WHEN get_table_column_list%NOTFOUND;
is_found:=0;


mt_sql:= 'SELECT COUNT(*) FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39);

execute immediate mt_sql into is_found;

if is_found>0 then
ret_sql:= ret_sql||chr(13)||chr(10)||'Table: '||mt_table_name||' Col: '|| mt_column_name;
end if;

end loop;

CLOSE get_table_column_list;

RETURN ret_sql;

END Search_Database;
/
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22657092
mt_sql:= 'SELECT COUNT(*) FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39);

I think the below is much better than the above single sql using count(*) because for us count(*) does not matter and we are trying to just check whether we have matching values or not.

mt_sql:= 'SELECT 1 FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39) || ' and rownum = 1' ;
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 175 total points
ID: 22657309
u r right there is no relevence in counting the rows
 
0
 

Author Comment

by:mawingho
ID: 22665316
when I run it, I got the following error...

SQL> select Search_Database('abc') from dual;
select Search_Database('abc') from dual
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
ORA-06512: at "QAD.SEARCH_DATABASE", line 28
0
 

Author Comment

by:mawingho
ID: 22665366
nav_kum_v,

    your mt_sql:= 'SELECT 1 FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39) || ' and rownum = 1' ;
is doesn't work...
0
 

Author Comment

by:mawingho
ID: 22665470
If I do this
select ROWNUM, CUST_NAME from CUSTOMERS;
I actually got
    ROWNUM CUST_NAME
---------- -----------------------------------
     85306 DUMMYF1169
     85307 DUMMYF1170
     85308 DUMMYF1171
     85309 DUMMYF1172
     85310 DUMMYF1173

See, if I use the following query, it returns nothing.

SQL> select ROWNUM, CUST_NAME from CUSTOMERS WHERE ROWNUM = 85306;
no rows selected
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22665748
rownum will work only with <= or < only.

It will not work with = , > , >= , <> etc....
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22665768
mawingho, my update was to shajukg so that he can fine tune his code before you use it.

You might be getting the error because the code is trying to scan through all the columns in the user_tab_columns which may not be right in all the cases like if you have long , date , number data types etc... Code needs to be modified to take date, number data types accordingly into account otherwise you will get those errors.
0
 

Author Comment

by:mawingho
ID: 22665777
your code
mt_sql:= 'SELECT 1 FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39) || ' and rownum = 1' ;

used rownum=1....so it doesn't work........as u said
rownum will work only with <= or < only.
It will not work with = , > , >= , <> etc....
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22665800
mawingho, i meant to say

ROWNUM = 85306
ROWNUM <> 85306
ROWNUM > 85306
ROWNUM >= 85306 will not work but

rownum <=2
rownum < 2
rownum = 1 will work. why dont you try with some sample queries for some dummy table like EMP for you to understand it better.

just to let you know rownum <> 2 will work but it will not get all rows whose rownum <> 2 instead it gets only the first row.  A lot more to read and understand about rownum....

So to conclude rownum = 1 will work but there are some other issues with the code which you are using.





0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mawingho
ID: 22665806
If I use DriverManager.registerDriver("oracle.jdbc.driver.OracleDriver");, it will result in error, but if I use Class.forName("oracle.jdbc.driver.OracleDriver");, then no error.

C:\Documents and Settings\msp064646\My Documents\Development>javac -cp .;C:\ojdbc14.jar Test.java
Test.java:12: registerDriver(java.sql.Driver) in java.sql.DriverManager cannot be applied to (java.lang.String)
                        DriverManager.registerDriver("oracle.jdbc.driver.OracleDriver");
                                     ^
1 error
0
 

Author Comment

by:mawingho
ID: 22665820
sorry post on the wrong forum...
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22665822
is this java error related to the question which you had asked ? i am a bit confused about this error as this is not related to SQL.
0
 

Author Comment

by:mawingho
ID: 22665824
thanks!!!..........I have no knowledge in PL/SQL.. in fact....
0
 

Author Comment

by:mawingho
ID: 22665877
If you say you are right, the column PT__QAD12 and PT_LAST_ECO are in type NUMBER and Date respectively, when I run the query, it return no result but no an error....so my program should be ok, isn't it?

SQL> select * from PT_MSTR where PT__QAD12 like '%ABC%';

no rows selected

SQL> SELECT * FROM pt_mstr where pt_last_eco like '%abc%';

no rows selected
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22666008
You are right in saying that but the call as given below

select Search_Database('abc') from dual;

will execute the below select as the base to get the list of table names and column names...
SELECT distinct table_name, column_name
FROM user_tab_columns ORDER BY TABLE_NAME;

and hence why dont you run the below query manually and just browse through the output to see whether some data type like clob, long, blob , bfile etc are there.....

SELECT *
FROM user_tab_columns ORDER BY TABLE_NAME;


0
 

Author Comment

by:mawingho
ID: 22666053
but the datatype should not affect the query....
I am too new to Oracle....no experience, used oracle for few days only...
ABD_DET	ABD_BOOK	VARCHAR2

ABD_DET	ABD_ASSET	VARCHAR2

ABD_DET	ABD_TYPE	VARCHAR2

ABD_DET	ABD_ACTIVE	NUMBER

ABD_DET	ABD_COST	NUMBER

ABD_DET	ABD_CURR_COST	NUMBER

ABD_DET	ABD_EX_RATE	NUMBER

ABD_DET	ABD_ENT_EX	NUMBER

ABD_DET	ABD_LIFE_YR	NUMBER

ABD_DET	ABD_LIFE_MNTH	NUMBER

ABD_DET	ABD_REM_YR	NUMBER

ABD_DET	ABD_REM_MNTH	NUMBER

ABD_DET	ABD_METHOD	VARCHAR2

ABD_DET	ABD_SALVAGE	NUMBER

ABD_DET	ABD_DTD	NUMBER

ABD_DET	ABD_YTD	NUMBER

ABD_DET	ABD_PD_DEPR	NUMBER

ABD_DET	ABD_LAST_DEPR	DATE

ABD_DET	ABD_EXPENSE	NUMBER

ABD_DET	ABD_BONUS	NUMBER

ABD_DET	ABD_CREDIT	NUMBER

ABD_DET	ABD_CR_AMT	NUMBER

ABD_DET	ABD_CONV	VARCHAR2

ABD_DET	ABD_DB_PCT	NUMBER

ABD_DET	ABD_UINT1	NUMBER

ABD_DET	ABD_UINT2	NUMBER

ABD_DET	ABD_UINT3	NUMBER

ABD_DET	ABD_UDEC1	NUMBER

ABD_DET	ABD_UDEC2	NUMBER

ABD_DET	ABD_UDEC3	NUMBER

ABD_DET	ABD_PRIOR_AST	NUMBER

ABD_DET	ABD_USER1	VARCHAR2

ABD_DET	ABD_USER2	VARCHAR2

ABD_DET	ABD__QAD01	VARCHAR2

ABD_DET	ABD__QAD02	VARCHAR2

ABD_DET	ABD_REP_PCT	NUMBER

ABD_DET	ABD_DATE	DATE

ABD_DET	ABD_PERIODS	NUMBER

ABD_DET	ABD_YTD_TOTAL	NUMBER

ABD_DET	ABD_FIRST_MNTH	NUMBER

ABD_DET	ABD_RT_DATE	DATE

ABD_DET	ABD__QAD03	VARCHAR2

ABD_DET	ABD_SCHED_DEPR	NUMBER

ABD_DET	ABD_UDEC4	NUMBER

ABD_DET	ABD_EX_RATE2	NUMBER

ABD_DET	ABD_EX_RATETYPE	VARCHAR2

ABD_DET	ABD_EXRU_SEQ	NUMBER

ABD_DET	OID_ABD_DET	NUMBER

ABD_DET	PROGRESS_RECID	NUMBER

ABSCC_DET	ABSCC_ABS_ID	VARCHAR2

ABSCC_DET	ABSCC_ABS_SHIPFROM	VARCHAR2

Open in new window

0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22666132
is the total output of that query gave only 51 records ??
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22666321
i think there is a bug in the code in the select which we are using, so can you just replace the select with the below and give it a try :

mt_sql:= 'SELECT 1 FROM '||mt_table_name||' WHERE '||mt_column_name||
' LIKE ' || '%' ||pt_string|| '%' || ' and rownum = 1' ;
0
 

Author Comment

by:mawingho
ID: 22666519
I try it later
0
 

Author Comment

by:mawingho
ID: 22666522
those code I shown above are the datatype of the first few columns, not the results
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22666558
ok..try it later and let me know the feedback about it.
0
 

Author Comment

by:mawingho
ID: 22666681
Why you delete CHR(39)? isn't CHR(39) a single quote?
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22666710
we are actually searching for the string ... in my below example LIMIT is the string we are searching for...Just execute the below selects and see the output

select CHR(39)||'%'||'LIMIT'||'%' || chr(39)
from dual ; -- if you chr(39) if puts a quote and if you use it we will not get output even if data  
                  -- pattern is there because of the quotes getting padded to it before and after

select '%'||'LIMIT'||'%'
from dual ; -- this is the right string which we are searching for....
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 275 total points
ID: 22666724
OR EVEN the other way to make that query work is as shown below :

'SELECT 1 FROM '||mt_table_name||' WHERE '|| '''' || mt_column_name || '''' || ' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39) || ' and rownum = 1' ;

here i am explicitly padding the column values with before and after quotes to ensure that our search returns values when patterns are found because we have used chr(39) which i think is not required.
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 175 total points
ID: 22667265
First Create the function

create or replace function getlong( p_tname in varchar2,
p_cname in varchar2,
p_rowid in rowid ) return varchar2
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor,
'select ' || p_cname || ' from ' || p_tname ||
' where rowid = :x',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_rowid );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getlong;
/
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 175 total points
ID: 22667281
then RE-CREATE THE FUNCTION  WHICH WILL able to search in following dataype columns  ' CHAR','VARCHAR2','RAW','LONG'


CREATE OR REPLACE FUNCTION Search_Database(
pt_string IN VARCHAR2) return VARCHAR2
IS
mt_table_name user_tab_columns.TABLE_NAME%type;
mt_column_name user_tab_columns.column_NAME%type;
mt_data_type user_tab_columns.data_type%type;
is_found NUMBER;
mt_sql varchar2(4000);
ret_sql VARCHAR2(4000);
P_STRING1 VARCHAR2(4000);
 
CURSOR get_table_column_list
IS
SELECT distinct table_name, column_name ,data_type
FROM user_tab_columns WHERE data_type in (' CHAR','VARCHAR2','RAW','LONG')
ORDER BY TABLE_NAME ;
 
 
 
BEGIN
OPEN get_table_column_list;
loop
FETCH get_table_column_list INTO mt_table_name, mt_column_name,mt_data_type;
EXIT WHEN get_table_column_list%NOTFOUND;
is_found:=0;
 
 
if mt_data_type='RAW' THEN
mt_sql:= 'SELECT COUNT(*) FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||RAWTOHEX(utl_raw.cast_to_raw(pt_string))||'%'||CHR(39);

ELSIF mt_data_type='LONG' THEN
mt_sql:= 'SELECT COUNT(*) FROM '||mt_table_name||' WHERE getlong('||CHR(39)||mt_table_name||CHR(39)||','||CHR(39)||mt_column_name||CHR(39)||','||'ROWID)'||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39);
ELSE

mt_sql:= 'SELECT COUNT(*) FROM '||mt_table_name||' WHERE '||mt_column_name||' LIKE '||CHR(39)||'%'||pt_string||'%'||CHR(39);

END IF;
execute immediate mt_sql into is_found;
if is_found>0 then
ret_sql:= ret_sql||chr(13)||chr(10)||'Table: '||mt_table_name||' Col: '|| mt_column_name;
end if;
end loop;
CLOSE get_table_column_list;
RETURN ret_sql;
END Search_Database;  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now