×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle error in OT query for exporting

Oracle error in OT query for exporting

Oracle error in OT query for exporting

(OP)
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;

RE: Oracle error in OT query for exporting

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/

RE: Oracle error in OT query for exporting

(OP)
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.

RE: Oracle error in OT query for exporting

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/

RE: Oracle error in OT query for exporting

(OP)
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.

RE: Oracle error in OT query for exporting

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close