Oracle Merge statement not working in power builder

[Former Member]

Hi,

I use power builder 12.5 classic.

I need to use oracle 'merge into table when matched... 'statement in the save event of a window.

But i get the error, warning  C0014 undefined variable merge.

                               C0031 syntax error

i am able to run that statement without error in sqlplus editor.

But when i paste the same query in power bulider i get the above error.

Please help.

[Former Member]

Hi Krishna;

  PowerBuilder has its own DML parsing part to its PowerScript compiler. The approach is to locate standard DML commands and check them for validity. PowerBuilder does this through using a really "kool" technique - its actually traps the DBMS command syntax and passes this to the connected DBMS using a "No Execute" option. This forces the DBMS compilers to only parse the DML and return a completion code to Pass/Fail the parse. Any parsing errors from the compilers are then presented in the PB IDE's output error area. So the key is look at these errors carefully as they are the target DBMS's analysis of what is contained in your PB source code.

  Now back to your MERGE command question ... PB has never been updated to add this newer command to its list of valid DML. Its also not a valid PowerScipt command - thus it fails the PB compile. Newer products like Appeon 2015 have added the MERGE command to its list of valid DML - so it will pass the Appeon Developer compiler. PB however, is still behind the times on this one.

  You can get around this limitation by using the EXECUTE IMMEDIATE option in PowerBuilder. This allows any DML, DCL or DDL of any DBMS version to be passed as a string.

HTH

Regards ... Chris

[Former Member]

What version of Oracle?  And which database driver are you using?

-Paul-