Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Ignore
btidba
post Mar 29 2017, 02:32 PM
Post #1


Advanced Member
***

Group: Members
Posts: 170
Joined: 30-September 13
Member No.: 49,003



Hi,

I have a string field (labe) containig the single cote from a table TEST that I need to parse in order to extract fields between # character :

#A#BB#C#|TOT'O|TITI|TYTY

when I run:

declare
str varchar2(1000);
v_substring VARCHAR2(1000);
v_pattern VARCHAR2(100):='[^#]+';
begin

for l in (select labe from test)
loop

str:='SELECT REGEXP_SUBSTR('''||l.labe||''','''||v_pattern||''',1,'||0||') from test';
EXECUTE IMMEDIATE str into v_substring;

end loop;
end;

I get : ORA-00907: missing right parenthesis

what is the right way to parse the labe string containing the single cote and extract the data ?


Many thanks


Go to the top of the page
 
+Quote Post
burleson
post Mar 30 2017, 11:47 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,380
Joined: 26-January 04
Member No.: 13



declare p_schema_name varchar2(1000) := '&1';

p_table_names varchar2(4000) := '&2';

p_statement varchar2(4000);

begin

p_statement := 'select * from :x'||'.'||':y';

for foo in ( select regexp_substr (replace(p_table_names, ' ', ''), '[^,]+', 1, level) as txt from dual connect by regexp_substr (replace(p_table_names, ' ', ''), '[^,]+', 1, level) is not null)

loop execute immediate p_statement using p_schema_name, foo.txt;

end loop;

end;




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 28th April 2017 - 08:39 PM