SQL & PL/SQL :: Instr Versus DBMS_LOB.instr / Search A Pattern Backwards In A CLOB Field?
Mar 10, 2012
I need to search a pattern backwards in a CLOB field.Function DBMS_LOB.instr does not work with '-1' offset (where to start the search) as instr does.
Parameters: instr(text_to_be_searched, pattern, offset, nth)
Example: I want to search 'Hello world' for the first instance of the letter 'o' starting from the end, backwards.As you can see, result for DBMS_LOB.instr is null when entered -1 for offset.
select
DBMS_LOB.instr('Hello world','o',-1,1) lob_i,
instr('Hello world','o',-1,1) std_i
from dual;
View 6 Replies
ADVERTISEMENT
Feb 9, 2010
I have a script that is using the INSTR function to search through a block of data for a specific string (CALL). I am ONLY looking for that string set but unfortunately, there are other words within that block of text that have that string set within it (e.g. CALL_MY_PHONE). Is there any way to make the INSTR search DISTINCT? Below is the code that I am using:
to_number(DECODE(INSTR(ph.block,'CALL'),0,0,1))
View 3 Replies
View Related
Sep 22, 2010
Trying to get a number out of an error backtrack
06512: at "SCHEMA.PROCEDURE", line 4
I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.
The database languages might differ so I can't do this using
v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)
As it would not find any 'line' inside the string.
View 7 Replies
View Related
Aug 27, 2013
I am trying this function
instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0
i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2
View 1 Replies
View Related
Aug 28, 2012
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
View 3 Replies
View Related
Apr 14, 2012
I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.
View 16 Replies
View Related
Nov 28, 2010
I have a table1 with columns serial no and name as below
serial no name
1 john paul
2 john victor
and i have another table1 with columns serialno,firstname and second name as below
serial no firstname secondname
1
2
I want to update my table2 with the name from table1 and divide and insert in first name and second name as below
serialno firstname secondname
1 john paul
2 john victor
i use the query as below
update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)
but the above query not worked.
View 11 Replies
View Related
Aug 28, 2012
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
But its proving a bit tricky to nail it.
View 6 Replies
View Related
Dec 9, 2011
We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .
The procedure builds the string by inserting single quotes in the following manner.
P_BU_LST is the parameter which have comma separated values
'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value
i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';
This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS
ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;
INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577
here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.
But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.
View 8 Replies
View Related
Oct 23, 2009
I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests):
create or replace procedure p_xml_test_1(
p_xml out nocopy clob
) is
begin
p_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
'<test><something>some value</something></test>';
end p_xml_test_1;
But I have access to some other source code that basically does this:
create or replace procedure p_xml_test_2(
p_xml out nocopy clob
) is
lv_xml clob;
begin
dbms_lob.createtemporary(
[code]......
I'm wondering if the first method will cause any problems for me down the road. Is it ok to do it that way? What is the advantage, if any, to the second method?
View 1 Replies
View Related
Jun 20, 2012
I want to search for a pattern and replace with a string. I can easily achieve the same in oracle 10g with REGEXP_REPLACE , I want to get the similar solution in 9i.
Eg.
I have to search for a string pattern 1234 5678 9012 6736 , I want to replace the same with XXXX XXXX XXXX XXXX.
View 8 Replies
View Related
Jul 15, 2012
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
View 7 Replies
View Related
Mar 19, 2010
I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables
View 1 Replies
View Related
Oct 7, 2013
We have oracle 10 g and a table contains a clob field . The table size is getting increased day by day . We have decided to zip all the clob data inside the table except last 1 month records.
Table : GENERIC_MESSAGE
Column:
message_id(number)
received_date(date)
message_xml(clob)
How we can do that keeping in mind this is huge data is present in table and huge transaction happened (10k transaction/hr)?
View 1 Replies
View Related
Oct 15, 2013
Oracle 11.2.0.3 on Linux.
I need to execute this insert as select:
INSERT INTO TAB1 (SQL_ID,SQL_FULLTEXT)
SELECT DISTINCT t.sql_id, t.sql_text FROM DBA_HIST_SQLTEXT;
But I get this error:
ORA-00932: inconsistent datatypes: expected - got clob
This is TAB1:
SQL_ID VARCHAR2(13)
SQL_FULLTEXT CLOB Y
SQL_CUSTOM_HASH VARCHAR2(60) Y
Should I user dbms_lob package? Is so, I do not understand how.
View 2 Replies
View Related
Apr 15, 2010
I have one table with clob field. The data in this field will contain string having transaction record data. Now I want to read data from this clob filed and insert different record in other oracle table.
example ->
Data in clob field will be->
H|12|1233|fff|sss
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table
and records->
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
in detail table.
End of excercise will redult-> 1 record in header and 5 records in detail table.
I need to implement it in my project.
View 7 Replies
View Related
Jul 31, 2012
I am more of a C/C++ guy and relatively amateur in oracle. I have to update a table field from "Long" to "CLOB". I have planned to do a simple alter table, and as far as I know there won't be any issues.
Queries:
1. Although I have triple checked, is there any scenario under which there can be any data loss during the data type change? The data is very critical and no data loss can be entertained.
2. Is there any easy way to update all the related views without having to do so manually?
3. Any particular precautions I should take before introducing the change?
View 2 Replies
View Related
Sep 13, 2010
code to append data of 32k to exisitng CLOB field in an oracle table..
View 1 Replies
View Related
Oct 6, 2011
I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....
The data will be available like this only, The same XML tag is used everywhere.
View 1 Replies
View Related
Jun 18, 2012
I'm loading data from text file separated by TAB and i got the error below for some lines. Event the column is CLOB data type is there a limitation of the size of a CLOB data type. The error is:
Record 74: Rejected - Error on table _TEMP, column DEST.
Field in data file exceeds maximum length
I'm using SQL Loader and the database is oracle 11g r2 on linux Red hat 5. Here are the line causing the error from my data file and my table description for test:
create table TEMP
(
CODE VARCHAR2(100),
DESC VARCHAR2(500),
RATE FLOAT,
INCREASE VARCHAR2(20),
COUNTRY VARCHAR2(500),
DEST CLOB,
[code]........
View 3 Replies
View Related
Oct 17, 2011
I was trying to insert some data in a table from a file. I am using DBMS_LOB for that.
My code looks like below -
--Create table t ( a number(10), b number(20), c varchar2(30), d varchar2(30), e varchar2(60));
set serveroutput on
declare
l_bfile bfile;
[Code]....
But getting error, after first row insertion. Error is -
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 20
It insert the first row successfully, but in the event of second row formatting, its returning error. I found the next time while its picking up the column "A", its adding some new line charecter in it. I tried to replace before assigning it to rec.a, but didn't work.
View 15 Replies
View Related
Oct 26, 2010
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package.
As it stated in Oracle documentation:
QUOTE Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
View 4 Replies
View Related
Mar 15, 2011
I am trying to execute the PL/SQL block below:
DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
View 2 Replies
View Related
Oct 26, 2010
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package. As it stated in Oracle documentation:
Quote:Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
View 1 Replies
View Related
Jul 2, 2013
I am using 11.2.0.3.0 version of oracle. I have not worked on regular expressions. During working on sql injection, I got set of below patterns which is feeded to some JAVA regx classes or utilityto restrict selective Request, based on patterns.below patterns and the characters which will be restricted by this pattern matching utility.
Given below are 3- patterns:
(.*?[sd)'])(?:AND|OR)(?=[s-+(']|.?d)(s*[-+(]?s*(?:[^s!<>=]+?|'.*?')[s)]*(?:s*(?:(?:[-+/*(.]||s*|)s*)+(?:[^s!<>=]+?|'.*?')[s)]*)*s*)(?: <s*>|>s*=|<s*=|!s*=|=|>|<)(.*)
(.*?[sd)'])(?:AND|OR)(?=[s-+(']|.?d)(s*[-+(]?s*(?:[^s]+?|'.*?')[s)]*(?:s*(?:(?:[-+/*(.]||s*|)s*)+(?:[^s]+?|'.*?')[ s)]*)*s*)(? <=[s)']|d.?)(?:LIKE|IN|BETWEEN)([s-+('].*)
.*[sd)'](?:AND|OR)[(+-s]*(?:'.*?'|.?d[ds-+/*().]*)[)s]*(?:<s*>|>s*=|<s*=|!s*=|=|>|<|LIKE|IN|BETWEEN)[(+-s]*(?:(+s*SELECT)?[(+-s]*(?:'.*?'|.?d[ds-+/*().]*).*
View 1 Replies
View Related
Mar 21, 2011
Is there a method or a tool out there that can do a search through an Oracle Schema to find objects ( tables, fields, stored procedures, etc) containing a specified character pattern ? For example : I would like to return all of the tables that contain fields containing the character string "ABC"
View 1 Replies
View Related
Apr 19, 2010
I am trying to write a pl/sql script where i need to check pattern matching numbers.My database is oracle 10g and i will put this logic in a procedure.i will pass no of tel_no to get.
if the no is 3 then i need 2072860126, 2072860127 and 2072860128(i.e all 3 in sequence)
if 2 then 2072860126, 2072860127. as such..the selected nos must be in sequence.
A query returns list od tel nos. in that tel i need to choose which staisfy my criteria.
View 1 Replies
View Related
Jan 22, 2010
I have a requirement which is as follows.A file will be downloaded into a server every day at 2 A.M. The name of the file would be 'BB90170_sysdate_D'. I need to refer to this file everyday since everyday the data changes.How do i identify which is the latest file in the server folder using Forms 6i code. Means while i have tried this
'' in_file :=Text_IO.Fopen ('C:TIESPartprocurementBB90170_'||part_date,'r')". How to use pattern matching in Text_io.fopen. Part_date in refers to sysdate without Timestamp.
View 3 Replies
View Related
Oct 15, 2010
I have a date column, where the date values are not stored in a specific pattern. following are the sample value from the column.
8/10/10 12:00 AM
9/22/2010 1:00AM
01/01/2001
9/1/10 6:00 PM
9/22/2009 1:00AM
i want to convert this to a standard format, 'dd/mm'yyyy'.
View 14 Replies
View Related
Feb 4, 2012
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
SQL>
View 13 Replies
View Related
