| Stored Procedure for Search [message #369852] |
Tue, 24 October 2000 13:55  |
Manas Patro
Messages: 2
Registered: October 2000
|
Junior Member |
|
|
|
i want to write a stored procedure for search that will search for the keyword in the databases (all the records) and the url table. this is to done by intermedia. try it any way u can.
|
|
|
|
| Re: Stored Procedure for Search [message #369853 is a reply to message #369852] |
Tue, 24 October 2000 14:19  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Use this as a starting point...
rem #####################################################################
rem
rem Name: text_search.sql
rem Author: Bernard van Aalst
rem Date: 24 Jan 2000
rem Purpose: Perform a full text search in a schema.
rem The table MY$FULLSEARCH will contain the table_name and
rem ROWID of every record that has a text field matching the
rem search criteria.
rem Options: Case insensitive search
rem wildcards (%)
rem
rem #####################################################################
SET SERVEROUT ON ECHO OFF
EXEC dbms_output.enable(1000000)
PROMPT Dropping table MY$FULLSEARCH:
DROP TABLE MY$FULLSEARCH;
PROMPT Creating table MY$FULLSEARCH:
CREATE TABLE MY$FULLSEARCH
( row_id ROWID
, owner VARCHAR2(30)
, table_name VARCHAR2(30)
);
DESC MY$FULLSEARCH
ACC owner PROMPT "Enter owner : "
ACC search PROMPT "Enter search string: "
ACC case_sensitive DEF 'Y' PROMPT "Case sensitive? Y: "
PROMPT Searching in &owner for &search....
DECLARE
c_owner VARCHAR2(30) := UPPER('&owner');
c_search VARCHAR2(2000) := '&search';
c_case_sensitive VARCHAR2(1) := UPPER('&case_sensitive');
CURSOR c_tab IS
SELECT table_name
FROM dba_tables t
WHERE owner = c_owner
AND EXISTS
( SELECT NULL
FROM dba_tab_columns tc
WHERE tc.owner = t.owner
AND tc.table_name = t.table_name
AND tc.data_type IN ( 'CHAR', 'VARCHAR', 'VARCHAR2')
);
CURSOR c_col (p_table_name dba_tables.table_name%TYPE) IS
SELECT column_name
FROM dba_tab_columns tc
WHERE tc.owner = c_owner
AND tc.table_name = p_table_name
AND tc.data_type IN ( 'CHAR', 'VARCHAR', 'VARCHAR2')
;
v_stmt VARCHAR2(2000); /* Dynamic SQL statement */
v_line VARCHAR2(80); /* Line in dynamic SQL statement */
v_cursor PLS_INTEGER;
v_retval PLS_INTEGER;
BEGIN
IF LENGTH(c_search) IS NULL /* No argument given */
THEN
dbms_output.put_line('Invalid search string.');
ELSE
v_cursor := dbms_sql.open_cursor;
FOR r IN c_tab
LOOP
BEGIN
v_stmt :=
'INSERT INTO MY$FULLSEARCH(row_id, owner, table_name)' ||CHR(10)||
'SELECT ROWID, '''||c_owner||''', '''||r.table_name||''''||CHR(10)||
'FROM ' || c_owner || '.' || r.table_name ||CHR(10)||
'WHERE 0=1' ||CHR(10);
FOR s IN c_col(r.table_name)
LOOP
IF c_case_sensitive = 'Y'
THEN
v_line :=
'OR '||s.column_name||
' LIKE '''||c_search||''''||CHR(10);
ELSE
v_line :=
'OR UPPER('||s.column_name||
') LIKE UPPER('''||c_search||''')'||CHR(10);
END IF;
v_stmt := v_stmt || v_line;
END LOOP;
dbms_sql.parse(v_cursor, v_stmt, dbms_sql.v7);
v_retval := dbms_sql.execute(v_cursor);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line
('Error when querying ' || c_owner || '.'||r.table_name ||':');
dbms_output.put_line(SQLERRM);
END;
END LOOP;
dbms_sql.close_cursor( v_cursor);
END IF;
END;
/
PROMPT Records found:
SELECT owner, table_name, COUNT(*)
FROM MY$FULLSEARCH
GROUP BY owner, table_name;
PROMPT You can find the ROWIDs in MY$FULLSEARCH.
PROMPT Remember to drop this table afterwards!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
