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]...
View 6 Replies
ADVERTISEMENT
Sep 9, 2013
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.
View 4 Replies
View Related
Jul 3, 2012
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]....
View 6 Replies
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
Oct 18, 2011
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;
View 2 Replies
View Related
Apr 16, 2010
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.
View 5 Replies
View Related
Jun 15, 2010
I have two tables emp and dept
sql>select emp_id from emp;
emp_id
------
123
345
6782
32
[code].....
I would like to search all the employee id's which are present in description column of dept.
View 32 Replies
View Related
Apr 30, 2013
TEMP1--ORINIGAL TABLE NAME
DESC IS:
SNO NUMBER;
SNAME VARCHAR2(100);
BACK UP TABLES ARE
TEMP1_1;
TEMP1_2;
TEMP1_3;
.
.
.
.
.
TEMP1_10
IN THE ABOVE 11 TABLES SNAME COLUMN HAVING THE DATA AS 'NARESH'
IN SOME OF THE TABLES AND NOT IN OTHERS.
REQUIRMENT:
I WANT THE TABLES ONLY HAVING THE
SNAME COLUMN 'NARESH'.
View 7 Replies
View Related
Mar 5, 2010
i want to know the use of index for searching data in table...tell answer to my query with table example....
View 2 Replies
View Related
Jul 22, 2013
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
View 4 Replies
View Related
Jul 3, 2012
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)
View 3 Replies
View Related
Nov 26, 2012
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.
View 1 Replies
View Related
Apr 30, 2010
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
View 2 Replies
View Related
Feb 6, 2012
This procedure is not working properly.
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
View 1 Replies
View Related
Sep 4, 2012
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
View 4 Replies
View Related
Oct 31, 2013
I would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception
.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";
View 0 Replies
View Related
Mar 14, 2013
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
View 3 Replies
View Related
May 9, 2008
show an ex to use string buffer for select statemnt
View 1 Replies
View Related
May 10, 2010
I have a string like below:
string = 'HEADER||MEAL||15'
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?
View 9 Replies
View Related
May 22, 2009
I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.
View 1 Replies
View Related
Oct 12, 2007
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!
View 6 Replies
View Related
Oct 9, 2008
say there is astring "mumbojumbo "i need the count of given string in it
ex:when o is given count shuld be 2 when m is given count shuld be 3
is there any pre defined function for counting a given string ...
View 2 Replies
View Related
Aug 22, 2013
how to get first word from string.example i have string like:-
Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some
i am expecting first word as out put like :-
jack
mark
Raj
Deniel
Some
in the same maner if i want should get two words also.
View 36 Replies
View Related
Dec 12, 2010
how to get last 4 characters in a string. But i don't know the length , for example the string is
abcdefghij
i want only ghij.
View 5 Replies
View Related
Aug 30, 2010
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 )
View 8 Replies
View Related
Apr 6, 2012
i have a one table with name as sms_tbl having one field name as sms_text and it contains text messages like
sample_text:Welcome to I-Care, your TPA for your<Insurer> health policy Your ID no is <I-Care ID>
in above text i need to insert records from two tables in the place of <Insurer> and <I-Care ID>.
where <Insurer> and <I-Care ID> records are in two different tables
where i have mapping for this records and there is no mapping for sms_tbl and how to insert these two records in above sample_text
View 3 Replies
View Related
Dec 21, 2011
I have strings like
1) ICE_10001 ICE_10002 ICE_10003
2) ICE_10005 ICE_10006
i want to split above strings like
1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006
If it is possible in oracle sql
View 15 Replies
View Related
Oct 31, 2013
I want to convert the follow string to date: 2013-12-04 11:35:54.89
View 7 Replies
View Related
Jul 12, 2010
I have the following test case:
create table t(
col1 varchar2(10),
col2 varchar2(100))
[Code]...
I need to spit col2 like this
P0001 aaa
P0001 ddsd
P0001 sds
.
.
.
.
.
delimeter between individual may be #, ' ',',' or';'
How can I achieve this using PL/SQL
View 13 Replies
View Related
Jun 26, 2012
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.
Eg.
Input String :
Ticket no 12343 , 1234567891234567 , origin-dxb , dest-lhr , 1234 5678 9012 2345 , address - rose wood
bldg 2444 , downtown ,london-33 .
Output string :
Ticket no 12343 , XXXXXXXXXXXXXXXX , origin-dxb , dest-lhr , XXXX XXXX XXXX XXXX , address - rose wood
bldg 2444 , downtown
View 6 Replies
View Related
