Executing multiple Oracle statements in a Query template

[Former Member]

I have been unable to perform more than a single Oracle statement in a Query template. For example, I want to perform multiple UPDATE and INSERT statements one after another in a single query. I have tried ending each statement with ";" and with nothing, etc.. but errors each time. How do I do it? (I presume it can be done?)

Thanks.

John.

[Former Member · Accepted solution]

Hi John,

You'll need to create stored procedure to do this. Or, you may be able to create a SQL Batch statement with a BEGIN and END to complete more than one transact statement in a Query Template.

[Former Member]

Thanks for your reply.

I was sort of expecting that that was the case. I tried using a BEGIN END statement but I get an error also.

John.

[jcgood25]

John,

For an multi-line Oracle example please take a look in the Template Editor at the "ActivePortal/Oracle/AlertLogInsert" or "ActivePortal/Oracle/MetricDelete" templates.

Something to watch out for though is that the overall Command mode success/fail response has no way to ensure that all were successfull or that one or more failed, etc. If you have 5 inserts and the 4th one fails, the initial 3 will have been processed by Oracle, but the 5th one will just 'disappear'. The query template will only succeed if all statements were good, and will fail with any one or more errors.

I'm not sure where the source of your multi-line statements is coming from, but if you need to ensure full completion or proper handling of the statements you might need to create a procedure on the server to handle it.

The query template in xMII just passes the request string through to the oracle driver in one chunk, so please keep this in mind if you need individual statement confirmation, etc.

Best Regards,

Jeremy Good

[Former Member]

Jeremy,

Apologies for not reporting back sooner but I've only just got to try out the suggestions over the last couple of days.

I tried using BEGIN END statements to include multiple statements as in the examples suggested but I was getting the following error -> SQL error ORA-06550 PLS-00103 Encountered the symbol "" when expecting one of the following: begin case declare exit .... etc.

I did some digging around and, following a suggestion on the internet from someone who had encountered the same problem elsewhere (using notepad), I found &#13;&#10; (i.e. <cr><lf>) in a number of places between the BEGIN and the END in the Query template XML. Once I removed these and replaced them with white space and saved the XML the query worked fine in the transaction!

Why do <cr><lf> appear in the queries? Is there some setting I need somewhere?

Thanks,

John.

[jcgood25]

Where was the whitespace - did it come from copying and pasting into the query template editor? Can you explain in a bit more detail?

Regards,

Jeremy

[Former Member]

Jeremy,

I did a find/replace using wordpad in the XML file itself i.e. I replaced the &#13;&#10 characters with a space character.

John.

[jcgood25]

John,

So if I understand you correctly, you hand edited your query template's xml file to remove the characters that were causing the Oracle error number above, and it is no working. I'm glad that this fixed your issue, but I'm still curious how they got there in the first place. When your query template was created did you type the multiple statements into the FixedQuery text area or did you copy and paste the syntax from another starting point like Toad or SQL+ ? I did see the &#10; characters in some of the Oracle specific ActivePortal templates, but never would have expected them to cause syntax issues. What version of Oracle are you connecting to and which Oracle JDBC driver are you using on the xMII server?

Regards,

Jeremy

[Former Member]

Jeremy,

I did copy and paste some of the statements but I have just checked and there were none of these characters in the original document. I have also done a test where I have created a new query template in which I have just typed in a BEGIN and END statement and nothing else and saved it, all in the Query Template Editor. When I perform a Test within the Editor I get the same error as mentioned earlier. Again when I opened the XML in wordpad I found a &#13&#10 between the BEGIN and END.

The Oracle version I am connecting to is 10i. The driver on the xMII server is Oracle JDBC Thin Driver v9.2.0.1

John.

[jcgood25]

John,

Well the good thing about the forums are that they are searchable, so hopefully anyone searching for the dubious Oracle error: ORA-06550 will encounter the lessons learned in this thread and be able to resolve their query template issue.

I've never seen the carriage returns cause an issue with SQL statements before with MSSQL, but the multi-line Oracle handling and the JDBC driver may not be interpreting them accurately. You might consider swapping out the default 9 driver for the Oracle 10i driver to see if that makes any difference, but other than that I assume the best course of action would be to exclude formatting the sql statement inside the template if it produces syntax problems.

Regards,

Jeremy