Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Oracle error in OT query for exporting

Status
Not open for further replies.

CherylAnderton

Technical User
Nov 21, 2016
30
US
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;
 
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
 
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.
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top