SQL & PL/SQL :: Searching For A String In Whole Database
Aug 16, 2012
I've tried to write the procedure for search the string from whole database. If user give the string as an input the output will be tablename and column in which the string contained in the table,But it's showing an error .
CREATE OR REPLACE procedure sample.pr_search_table (p_search varchar )
is
type tb_type is table of varchar(30);
tb_table tb_type:= tb_type();
tb_column tb_type := tb_type();
v_temp varchar(30);
[code]...
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
I have a situation where I need to find the number of occurrences of a string that is present in a table having comma separated values. To explain with an example:
create table test_data ( textfield varchar2(100)); insert into test_data values('DM,HM'); insert into test_data values('EM'); insert into test_data values('AM,CA,CD,FM,ST'); insert into test_data values('LS'); insert into test_data values('TQ,SP,AM,FM,ST,CA,CD'); insert into test_data values('TQ,SP,AM,FM,ST,CA,CD,LS'); insert into test_data values('DM,HM,LS');
The data in the table test_data looks like DM,HMEMAM,CA,CD,FM,STLSTQ,SP,AM,FM,ST,CA,CDTQ,SP,AM,FM,ST,CA,CD,LSDM,HM,LS Now I need to search"LS" in the table test_data. Basically, I need to find if "LS" is present at least once in any of the rows or not. I want to avoid looping here.
i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows
first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.
Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.
Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.
We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.
CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2 (30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30)); INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO'); insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO'); insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL); INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL); SQL> SELECT * FROM OM_MC_MASTER; [code]....
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE CURSOR c_type_id_usg IS
[Code]....
-- get the list of sms type id usg values OPEN c_type_id_usg; FETCH c_type_id_usg bulk collect into l_type_ids; CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310) then dbms_output.put_line('I have found an entry '); else dbms_output.put_line('I have NOT found an entry ' ); end if; END;
I want to write a SQL statement to search valid city name from address field. Valid city names are in one table and address column is in another table.
I needed to search for some specific text in the DDL of each of the views in a particular schema. The text column of the user_views is LONG, and I looked at some old Tom's threads for converting long2clob, but found these processes to be really cumbersome, so I just opted to use DBMS_METADATA.GET_DDL instead even if it is a little slow.
One area I do not have a lot of experience with is searching clob fields. I tried this but not sure what would be the appropriate function to use for something like this:
SELECT * FROM (
[Code]....
WHERE contains(object_text, 'WHERE t.policy NOT LIKE') > 0; -- Show the names of all views that contain the matching text
My application runs a batch procedure weekly once for searching 'A_Text' from a column in Table1 in a clob column in Table2 and inserts accordingly into another Table3.
code snippet is like this -
--- CURSOR cr_sn IS SELECT serial_number
[Code]....
TABLE1 will have at least 1.1 Million rows but not significantly more than this.
This procedure takes 24+ hours to complete. I tried -
1. putting parallel hint ( INSERT /*+ PARALLEL*/INTO Table3)
2. partitioning TABLE2 based on last_update_date and putting a where clause in the above query last_update_date ( last_update_date between date1 and date2)
I have created searching tool using oracle forms 6i. while searching the cursor move from text field (A) to other block query the data according to the field (A) and come back again to field (A). Everything is working fine but the problem is, when the cursor move back to the field (A) the existing text is highlighted and when user right something in it. It's overwriting the existing text.
I want when the cursor moving bank to the field (A) the text should not be highlighted and when the user writes something that will be added to the existing text.
I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -
Account Management Active Directory Adobe Acrobat Reader NT Account Application Security
[code]....
I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'. I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.
Expected Output:
Account Management NT Account WebSite Account HP Laptop
create or replace procedure bank_search_sp ( p_tablename in varchar2, p_searchname in varchar2, p_bankcode out varchar2, p_bankname out varchar2, p_dist_code out number ) as v_tem varchar2(5000); begin v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || ' where bankname like '''|| p_searchname||''; execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ; commit; end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14 ORA-06512: at line 1
I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns? Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table", substr (t.column_value.getstringval (), 1, 50) "Column/Value" from all_cons_columns, table (xmlsequence (dbms_xmlgen.getxmltype ('select ' || column_name || ' from ' || table_name || ' where upper(' || column_name || ') like upper(''%' || 'fra' || '%'')' ).extract ('ROWSET/ROW/*') ) ) t order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_XMLGEN", line 288 ORA-06512: at line 1 19202. 00000 - "Error occurred in XML processing%s" *Cause: An error occurred when processing the XML function *Action: Check the given error message and fix the appropriate problem
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).
I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.
Select max(term_id),atm_location from (Select (SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location from (select term_id , atm_location from ATM_TABLE order by term_id asc ) Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by ATM_LOCATION )
I want to replace numeric values of a specific format with 'X' , find the below example and note that the string in the example only for sample values and the strings may be different.
