Home » SQL & PL/SQL » SQL & PL/SQL » Clarification regarding REGEXP functions
Clarification regarding REGEXP functions [message #289489] Sat, 22 December 2007 06:59 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I am using Oracle 11.1.0.6 database .
I am having code fragments similar to this

INSERT /*+ APPEND */ INTO 
	$$T:LOCATIONS[1]$$($$C:LOCATIONS[1]:LOCATION_ID[1]$$,$$C:LOCATIONS[1]:LOCATION_CODE[1]$$,$$C:LOCATIONS[1]:ADDRESS[1]$$)
		SELECT $$S:LOCATIONS_S[1]$$.nextval,UNIQ_LOCATION_CODE,US FROM ( 
				SELECT DISTINCT $$C:DEPT[1]:LOCATION_CODE[1]$$uniq_location_code FROM   
					$$T:DEPT[1]$$ dpt WHERE  
						$$C:DEPT[1]:LOCATION_CODE[1]$$ NOT IN ( 
							SELECT $$C:LOCATIONS[1]:LOCATION_CODE[1]$$ FROM   
								$$T:LOCATIONS[1]$$ loc2)) uniq_loc


Here I have to identify the table name and column names which are represented inside $$.
For ex : LOCATIONS is the table name and LOCATION_ID is the column name.
I have to extract these values .

Is there any easy way to do this using regexp . Since substr and instr will make things look more complex and performance may decrease .

In the case of
$$T:LOCATIONS$$
I need only the string LOCATIONS present between ':' and '['
and in the case of
$$C:LOCATIONS:LOCATION_ID$$
I need only the string LOCATION_ID present between second ':' second '['

Now this function will find the same if the string is parsed in the reverse manner ,
REXEXP_SUBSTR('$$C:LOCATIONS:LOCATION_CODE$$','\[[[:alnum:]]*')
If this works then the o/p would be
[LOCATION_CODE .
Here '[' needs to be suppressed . Can it be done while displaying the result itself ?
I don't know whether I can parse the string in the reverse direction using REGEXP .

Let me know what can be done to keep the code simple and effective.
Re: Doubt regarding regexp_substr [message #289523 is a reply to message #289489] Sun, 23 December 2007 09:36 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can try the following:

SQL> select regexp_replace(str, '\$\$.*?(\w+)\[\d\]\$\$','\1') from t1;

REGEXP_REPLACE(STR,'\$\$.*?(\W+)\[\D\]\$\$','\1')
--------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO
        LOCATIONS(LOCATION_ID,LOCATION_CODE,ADDRESS)
                SELECT LOCATIONS_S.nextval,UNIQ_LOCATION_CODE,US FROM (
                                SELECT DISTINCT LOCATION_CODEuniq_location_code FROM
                                        DEPT dpt WHERE
                                                LOCATION_CODE NOT IN (
                                                        SELECT LOCATION_CODE FROM
                                                                LOCATIONS loc2)) uniq_loc

[Updated on: Sun, 23 December 2007 18:22]

Report message to a moderator

Clarification regading the performance of REGEXP functions [message #289618 is a reply to message #289489] Mon, 24 December 2007 06:03 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I was asked to do a performance comparison between the REGEXP functions in perl and the one in oracle database .

I am using Oracle 11.1.0.6 version database for the same .

Here is the sample code that I used to replace fragements in a string with appropriate values .
"code" is the string and I'm replacing sub-strings inside it which are wrapped within $$ symblos with the appropriate values("repl_value") from the Associative array ("name_value").


code VARCHAR2(4000) := 'INSERT /*+ APPEND */ INTO $$T:LOCATIONS[1]$$($$C:LOCATIONS[1]:LOCATION_ID[1]$$,$$C:LOCATIONS[1]:LOCATION_CODE[1]$$,$$C:LOCATIONS[1]:ADDRESS[1]$$)SELECT $$S:LOCATIONS_S[1]$$.nextval,UNIQ_LOCATION_CODE,US FROM ( SELECT DISTINCT $$C:DEPT[1]:LOCATION_CODE[1]$$uniq_location_code FROM   $$T:DEPT[1]$$ dpt WHERE  $$C:DEPT[1].LOCATION_CODE[1]$$ NOT IN ( SELECT $$C:LOCATIONS[1].LOCATION_CODE[1]$$ FROM   $$T:LOCATIONS[1]$$ loc2)) uniq_loc';

BEGIN

WHILE (REGEXP_SUBSTR(code,'\$\$') IS NOT NULL) LOOP
              value := REGEXP_SUBSTR(code,'\$\$[^\$]*\$\$');
              value := REGEXP_REPLACE(value,'\$\$.*?([_[:alnum:]]+)(\[1\])?\$\$','\1');
              repl_value := name_value(value);
              code := REGEXP_REPLACE(code,'\$\$[^\$]*\$\$',repl_value,1,1);
        END LOOP;



The code was working fine and I tried running the code 100000 times and it took around 6 minutes .

I also coded the same in perl and for the same 100000 times it took only 20 seconds .

I just want to know whether I have introduced any piece of code which acts as a performance bottleneck ? Let me know whether the pl/sql coding I have done is effective or not ?
Re: Clarification regading the performance of REGEXP functions [message #289622 is a reply to message #289618] Mon, 24 December 2007 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It is useless to start a new topic on the same subject.
2/ Note that you must keep your line size to 80 characters.

Regards
Michel
Re: Clarification regading the performance of REGEXP functions [message #289654 is a reply to message #289618] Mon, 24 December 2007 23:38 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Sorry for that Michel .

I have identified a way to convert the string but I didn't find it much efficient since perl was much more faster . Since this was a performance related question I thought of starting a new topic .

Can anyone help me in converting the code into a more efficient one?
Re: Clarification regarding REGEXP functions [message #289655 is a reply to message #289489] Tue, 25 December 2007 01:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
no
Re: Clarification regarding REGEXP functions [message #289823 is a reply to message #289489] Wed, 26 December 2007 04:00 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Is it possible to use

code := REGEXP_REPLACE(code,'\$\$.*?([_[:alnum:]]+)(\[1\])?\$\$',name_value('\1'));

Following is the error I am getting if I try with the same .
What I want to do is I want to dynamically replace the parts of sring with the contents of the associative array . So I am passing the value to the associative array but I'm getting the below error .



  1  declare
  2     code VARCHAR2(4000) := 'INSERT /*+ APPEND */ INTO $$T:LOCATIONS[1]$$($$C:LOCATIONS[1]:LOCATION_ID[1]$$,$$C:LOCATIONS[1]:LOCATION_CODE[1]$$,$$C:LOCATIONS[1]:ADDRESS[1]$$)SELECT $$S:LOCATIONS_S[1]$$.nextval,UNIQ_LOCATION_CODE,US FROM ( SELECT DISTINCT $$C:DEPT[1]:LOCATION_CODE[1]$$uniq_location_code FROM   $$T:DEPT[1]$$ dpt WHERE  $$C:DEPT[1].LOCATION_CODE[1]$$ NOT IN ( SELECT $$C:LOCATIONS[1].LOCATION_CODE[1]$$ FROM   $$T:LOCATIONS[1]$$ loc2)) uniq_loc';
  3     type name_value_type is table of varchar2(100) index by varchar2(100);
  4     name_value name_value_type;
  5  begin
  6     dbms_output.put_line(code);
  7     name_value('LOCATIONS') := 'LOCATIONS_001';
  8     name_value('LOCATION_ID') := 'LOCATION_ID_001';
  9     name_value('LOCATION_CODE') := 'LOCATION_CODE_001';
 10     name_value('ADDRESS') := 'ADDRESS_001';
 11     name_value('LOCATIONS_S') := 'LOCATIONS_S_001';
 12     name_value('DEPT') := 'DEPT_001';
 13     code := REGEXP_REPLACE(code,'\$\$.*?([_[:alnum:]]+)(\[1\])?\$\$',name_value('\1'));
 14     dbms_output.put_line(code);
 15* end;
SQL> /

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 13



Let me know whether I have comiitted any mistake or using replace string of this type is not possible ?
Re: Clarification regarding REGEXP functions [message #289848 is a reply to message #289823] Wed, 26 December 2007 04:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Yes, you committed a mistake. You didn't follow Michel's #2 advice above.
Re: Clarification regarding REGEXP functions [message #289861 is a reply to message #289848] Wed, 26 December 2007 05:15 Go to previous message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
code VARCHAR2(4000) := 'INSERT /*+ APPEND */ INTO 
			 $$T:LOCATIONS[1]$$
			  ($$C:LOCATIONS[1]:LOCATION_ID[1]$$,$$C:LOCATIONS[1]:LOCATION_CODE[1]$$)
			   SELECT $$S:LOCATIONS_S[1]$$.nextval,UNIQ_LOCATION_CODE,US FROM 
			    ( SELECT DISTINCT $$C:DEPT[1]:LOCATION_CODE[1]$$uniq_location_code FROM   
			     $$T:DEPT[1]$$ dpt WHERE  
			      $$C:DEPT[1].LOCATION_CODE[1]$$ NOT IN ( 
				SELECT $$C:LOCATIONS[1].LOCATION_CODE[1]$$ FROM   
				 $$T:LOCATIONS[1]$$ loc2)) uniq_loc';
Previous Topic: auto number field
Next Topic: How to find the no of records selected by a curcor before processing the rows?
Goto Forum:
  


Current Time: Sun Dec 04 00:14:56 CST 2016

Total time taken to generate the page: 0.09959 seconds