DB: 10g
Hello Everyone,
Problem: My intention is to write a procedure which is going to parse a column.
Parsing part of the problem is done. The code for parsing is as follows and it
works fine. But, I have noticed that sometimes it parses weirdly due to
data issues. For example - column2 should have numbers i.e. 1,2,3 etc. I don't
want column2 to have decimal numbers nor strings which I have seen from
time to time. If I create a table as - CREATE TABLE t AS SELECT .....Then table
is going to have unwanted columns. If I create a table t1 as -
CREATE TABLE (col1 VARCHAR2(1000), ..,col2 NUMBER(3),....) and use I
NSERT INTO t1 SELECT ....1.5 will go into col2 as 1 but exception will be
thrown, the moment, some string show up and my procedure will stop. I don't
want my procedure to stop. I want to keep it running and when it sees 1.5 or
any string, the whole record should be thrown into an exception table but
procedure should keep running. I googled and found this article
http://netprogramminghelp.com/sql-server/stop-terminating-oracle-procedure-while-exception/
But, if I do normal looping, it will take ages for my procedure to run to
completion because SELECT code below generates about 2 million records.
If I use BULK COLLECT it will also make the procedure slow. Looks like I
should go with BULK BIND. Didn't understand the example of the link though.
Would anybody has a different suggestion or is there any other way to prevent
termination of procedure when an exception occurs. Please advise. Thanks.
SELECT SUBSTR(REGEXP_SUBSTR(lc,'Crit:</strong>[^<]+', 1, n.column_value), 20) column1,
SUBSTR(REGEXP_SUBSTR(lc,'points [^\)]+', 1, n.column_value), 8) column2,
SUBSTR(REGEXP_SUBSTR(lc,'\):[^<]+', 1, n.column_value), 3) column3,
SUBSTR(REGEXP_SUBSTR(lc,'blockquote>[^<]+', 1, n.column_value), 12) column4,
SUBSTR(lc, INSTR(lc, '>', -1) + 1) column5
,n.column_value
FROM t,table(cast(multiset(select level from dual CONNECT BY LEVEL <= (LENGTH(lc) - LENGTH(REPLACE(lc, 'Crit:')))/10) as sys.OdciNumberList)) n;
Edited by: abyss on Oct 14, 2010 2:02 PM