[
http://jira.codehaus.org/browse/MSQL-48?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=206045#action_206045
]
code hause commented on MSQL-48:
--------------------------------
after a lot of trial and error, i've been able to resolve the issue by
repositioning the 2 stored procedures that contain the curly brackets in the
regexes, to be the last 2 blocks defined in the package body. who knew? it
seems illogical - i know - but it fixed the problem for me. that's all i care
about.
i didn't need to do an "EXECUTE IMMEDIATE". i simply moved the sprocs to the
very end of the package body.
i tried reproducing the issue with dummy code (a few "hello world" type sprocs
that come to about 20-30 lines altogether), but i can't reproduce the
exception.
i get the " Non supported SQL92 token at position" exception only in my real
code, when the sprocs with the curly brackets are NOT positioned at the bottom
of the body AND when the body has several lines of pl/sql (the code in which i
encountered the issue has approx 3,000 lines). go figure!
> sql-maven-plugin fails to parse oracle REGEXP_INSTR syntax
> ----------------------------------------------------------
>
> Key: MSQL-48
> URL: http://jira.codehaus.org/browse/MSQL-48
> Project: Maven 2.x SQL Plugin
> Issue Type: Bug
> Affects Versions: 1.3
> Environment: "windows xp" version: "5.1" arch: "x86"; Personal Oracle
> Database 10g Release 10.2.0.1.0 - Production; Maven version: 2.0.10
> Java version: 1.6.0_13
> Reporter: code hause
> Attachments: 09_01_10_sql-maven-plugin_jira_post_0.txt
>
>
> when sql-maven-plugin encounters a regular expression in an sql source file
> it throws an "[ERROR] java.sql.SQLException: Non supported SQL92 token at
> position: N: N".
> that very same sql source file executed in sql*plus successfully compiles as
> expected.
> a couple of the pl/sql stored procedures in our project use the "REGEX_INSTR"
> function (http://www.psoug.org/reference/regexp.html). the pattern argument
> we pass to "REGEX_INSTR" contains curly brackets ("REGEXP_INSTR(v_foo,
> '^[N-Rn-r]{12}[0-9]{99}$')").
> evidently
> (http://forums.oracle.com/forums/thread.jspa?messageID=164501#164501) the
> "Non supported SQL92 token at position" exception is thrown because "...the
> parser thinks you are trying to invoke a JDBC Escape {} and then tosses the
> exception..."
> the stored procedures i'm trying to get maven to build need to be part of a
> package.
> others have resolved the "Non supported SQL92 token at position" exception in
> other jdbc-based apps by constructing - as a string - the stored procedure
> that contains the curly brackets ("{}"), and doing an "EXECUTE IMMEDIATE" on
> the string. but i can't seem to get sql-maven-plugin to do the right thing.
> i've tried different configuration settings
> "<enableBlockMode>true</enableBlockMode>",
> "<enableBlockMode>false</enableBlockMode>", etc. tried escaping the offending
> characters that are sent to the server as part of the whole package body
> declaration; also tried putting the "EXECUTE IMMEDIATE" call in a separate
> sql file from the rest of the "create package..." declaration statements; all
> with the same errors.
> is there a fix|workaround for this issue?
> thanks in advance for your help
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
