Oracle error in OT query for exporting

[CherylAnderton]

I've resulted to exporting data from OT by database query and a LINUX copy. I'm having a issue with my query. I know this may be the incorrect forum but hope for some help. I'm getting ORA-12725: unmatched parentheses in regular expression after about 25000 lines of output. Using TOAD, no errors in my code, but when it fails I get the Oracle error at line 62, which makes no sense. Please help if you can...

SELECT lpad(' ', LEVEL - 1)
,(
CASE dtree.subtype
WHEN 0
THEN to_clob('mkdir -p ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || SYS_CONNECT_BY_PATH(dtree.NAME, '/') || '''')
WHEN 144 THEN (
CASE
WHEN providerdata.providertype = 'Default'
THEN to_clob('install -D /Net/livelink01/discovery_fs01/livelink/discovery/filestore/'|| SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore02'
THEN to_clob('install -D /Net/livelink21/discovery_fs21/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore03'
THEN to_clob('install -D /Net/livelink01/discovery_fs03/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'store04'
THEN to_clob('install -D /Net/livelink01/discovery_fs04/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore05'
THEN to_clob('install -D /Net/livelink01/discovery_fs05/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore06'
THEN to_clob('install -D /Net/livelink01/discovery_fs06/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore07'
THEN to_clob('install -D /Net/livelink01/discovery_fs07/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore08'
THEN to_clob('install -D /Net/livelink01/discovery_fs08/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore09'
THEN to_clob('install -D /Net/livelink01/discovery_fs09/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore10'
THEN to_clob('install -D /Net/livelink11/discovery_fs10/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore11'
THEN to_clob('install -D /Net/livelink11/discovery_fs11/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore12'
THEN to_clob('install -D /Net/livelink11/discovery_fs12/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore13'
THEN to_clob('install -D /Net/livelink11/discovery_fs13/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore14'
THEN to_clob('install -D /Net/livelink11/discovery_fs14/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore15'
THEN to_clob('install -D /Net/livelink11/discovery_fs15/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore16'
THEN to_clob('install -D /Net/livelink11/discovery_fs16/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore17'
THEN to_clob('install -D /Net/livelink11/discovery_fs17/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore18'
THEN to_clob('install -D /Net/livelink21/discovery_fs18/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore19'
THEN to_clob('install -D /Net/livelink21/discovery_fs19/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore20'
THEN to_clob('install -D /Net/livelink21/discovery_fs20/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore21'
THEN to_clob('install -D /Net/livelink21/discovery_fs21/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore22'
THEN to_clob('install -D /Net/livelink21/discovery_fs22/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore23'
THEN to_clob('install -D /Net/livelink21/discovery_fs21/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
WHEN providerdata.providertype = 'filestore24'
THEN to_clob('install -D /Net/livelink21/discovery_fs21/' || SUBSTR(providerdata.providerdata, INSTR(providerdata.providerdata, '''', 1, 3) + 1, (INSTR(providerdata.providerdata, '''', 1, 4) - INSTR(providerdata.providerdata, '''', 1, 3) - 1)) || ' ' || '''' || '/Net/livelink/util_mig/mig/Nexus_3383191' || regexp_replace(sys_connect_by_path(dtree.NAME, '/'), substr(sys_connect_by_path(dtree.NAME, '/'), - instr(reverse(sys_connect_by_path(dtree.NAME, '/')), '/') + 1), '') || dtree.dataid || '.v_' || dversdata.version || '.' || dversdata.filetype || '''')
ELSE null
END
)
END
) COPY
FROM dtree
LEFT OUTER JOIN dversdata ON (dtree.dataid = dversdata.docid)
LEFT OUTER JOIN providerdata ON (providerdata.providerid = dversdata.providerid)
START WITH dtree.dataid = & 1
CONNECT BY PRIOR dtree.dataid = dtree.parentid;

 

[ggriffit]

do you know which record is causing the issue ? its likely that something in the data is causing issues with your RegEx. Have you tried breaking the query down into smaller subqueries or batches ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[CherylAnderton]

Ahhh.... didn't think about the data.
So using sys_connect_by_path gives me the path of the folder where subtype = 0
and the path and name of the document using subtype = 144.

Question how can I use sys_connect_by_path for subtype = 144 and OMIT the name so I can add dataid.filetype?
The reason: names exceed the 260 character count for Microsoft.

 

[ggriffit]

i would do it another way, first select the data including the subtype in an inner query and then in the outer query you can use the sys_connect_by_path on either the node, if its a container, or its parent, if its not which should be a container.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[CherylAnderton]

I've actually added SYS_CONNECT_BY_PATH(TRANSLATE (dtree.name, '_-$–()[];:,%=!@&`+''\/():*?"<>~{}|#', '_'), '/'). it was a bad character in one of the names. I wish OT would restrict bad characters based on Windows.

 

[ggriffit]

As CS is platform agnostic at that level it would be difficult, there is a document covering illegal characters on the KC which details what is supported.

Sounds like you have resolved the issue ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/