Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

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

Advanced Member

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


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 :


when I run:

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

for l in (select labe from test)

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

end loop;

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
post Mar 30 2017, 11:47 AM
Post #2

Advanced Member

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

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

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

p_statement varchar2(4000);


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;


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: 17th October 2017 - 10:14 AM