Toad World® Forums

select with xmltable leads to different behavior between F9 and F5, Error in Syntax check ?


#1

In the Create Statement of a procedure we have following code:

SELECT INTO FROM table1, XMLTABLE( myStringVar PASSING …) WHERE …;

If I use F9 to compile it, I get the Message:[Error] Syntax check (519: 26): Found: ‘myStringVar’ Expecting: string -or- XMLNAMESPACES

If I use F5, everything is fine: Procedure created.

We have this in Toad 10.6 and 11.2. (Cannot test toad 12, our procurement management is a bit slow).

Thank’s in anticipation.

Steffen


#2

I’m not able to reproduce this in v 10.6 +. Here is my test scenario:

Execute the following script to setup the table and insert a row of data:

create
table
xmljunk (id
number,
profile
xmltype);

insert
into
xmljunk values
(1,
XMLTYPE(’

    <subject>I

       <action>like

           <object>sports</object>

           <object>music</object>

       </action>

    </subject>

</profile>'));

commit;

Create the following procedure and compile via F9 (or F5)

CREATE
OR
REPLACE
PROCEDURE
xmltest

IS

i_xpath
varchar2(4000);

BEGIN

select
x.object.getStringVal()
into
i_xpath

from
xmljunk u,

XMLTABLE(’/profile/subject/action’

passing
u.profile

columns
action VARCHAR2(30)
PATH
‘text()’,

object
XMLTYPE
PATH
‘object’)
x;

end;

Am I missing anyting?

From: steffen.brosig [mailto:bounce-steffenbrosig@toadworld.com]

Sent: Wednesday, October 30, 2013 2:55 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] select with xmltable leads to different behavior between F9 and F5, Error in Syntax check ?

select with xmltable leads to different behavior between F9 and F5, Error in Syntax check ?

Thread created by steffen.brosig

In the Create Statement of a procedure we have following code:

SELECT INTO FROM table1, XMLTABLE( myStringVar PASSING …) WHERE …;

If I use F9 to compile it, I get the Message:[Error] Syntax check (519: 26): Found: ‘i_xpath’ Expecting: string -or- XMLNAMESPACES

If I use F5, everything is fine: Procedure created.

We have this in Toad 10.6 and 11.2. (Cannot test toad 12, our procurement management is a bit slow).

Thank’s in anticipation.

Steffen

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#3

You’ve stripped out most of your SELECT especially the portion containing i_xpath so we won’t be able to reproduce. I think there was a bug in previous releases where the Editor was incorrectly using the parser as a pre-syntax check
before allowing the statement to go to the database. If the parser incorrectly thinks the statement has an error you’d see the syntax check error in the messages panel and the statement would not execute. If you send a statement that reproduces the issue I
can confirm. If this is the case updating would be the only real fix.

Just saw your latest email. I’m nearly 100% certain the bug above is the cause.

Michael

On 10/30/2013 02:54 AM, steffen.brosig wrote:

select with xmltable leads to different behavior between F9 and F5, Error in Syntax check ?

Thread created by steffen.brosig
In the Create Statement of a procedure we have following code:

SELECT INTO FROM table1, XMLTABLE( myStringVar PASSING …) WHERE …;

If I use F9 to compile it, I get the Message:[Error] Syntax check (519: 26): Found: ‘i_xpath’ Expecting: string -or- XMLNAMESPACES

If I use F5, everything is fine: Procedure created.

We have this in Toad 10.6 and 11.2. (Cannot test toad 12, our procurement management is a bit slow).

Thank’s in anticipation.

Steffen

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#4

Thanks to Michael. It was definitly the pre syntax check, which couldn’t accept a Variable instead of a Literal. With Toad 11.0 it works, a workmate made a test for me.

So I wait - with the patience of a Saint - for my new License.

St. (i.e. Steffen)