topic Re: Unable to submit Oracle PL/SQL statement in QlikView

Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

Hello,

I got my odbc data source setup to connect to an Oracle 11g instance. I also got the Oracle connection string

created in script editor and tested it. However, when I plugged in my Oracle PL/SQL statement, I got an "Unknow Statement"

error. I have been testing this statement in Oracle SQL Developer and it's running fine.

Can someone provide some help to me as to what I need to do in order to submit this query to Oracle and

get the data back?

Thank you.

Re: Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 12 Feb 2016 15:45:05 GMT

Just wanted to post additional notes. I'm using the WITH CLAUSE and a SELECT statement similar to the example

found in this link: https://oracle-base.com/articles/misc/with-clause

Is this an issue (or limitation) in Qlikview?

Re: Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 12 Feb 2016 15:46:18 GMT

Could you share the script ?

Re: Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 12 Feb 2016 16:00:48 GMT

Bill,

Unfortunately I won't be able to post the entire statement here due to sensitive info. But here's a simplified version of it which looks like this:

WITH

BALANCE AS

(

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

ORDER BY col1

)

SELECT * FROM BALANCE

As soon as I press CTRL+R, I get "Unknown Statement". However if I replaced the above with just this:

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

ORDER BY col1

It works. So it appears I can only place SELECT statements in the script editor. But I can't confirm this as I'm new to QV. So I will need the QV experts to help me here.

Thank you.

Re: Unable to submit Oracle PL/SQL statement

sathishkumar_go — Fri, 12 Feb 2016 16:04:35 GMT

Please share the error message and your script

-Sathish

Re: Unable to submit Oracle PL/SQL statement

Clever_Anjos — Fri, 12 Feb 2016 16:05:31 GMT

Please try

SQL

WITH

BALANCE AS

(

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

)

SELECT col1 FROM BALANCE ORDER BY col1

Re: Unable to submit Oracle PL/SQL statement

maxgro — Fri, 12 Feb 2016 16:07:16 GMT

maybe you need a sql

SQL

WITH

BALANCE AS

(

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

ORDER BY col1

)

SELECT * FROM BALANCE;

Re: Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 12 Feb 2016 16:07:53 GMT

I'm glad I was able to locate a solution found in this link:

community.qlik.com/message/973758#973758

It was as simple as adding the prefix "SQL" to my entire statement. It's working now.

Re: Unable to submit Oracle PL/SQL statement

Clever_Anjos — Fri, 12 Feb 2016 16:08:40 GMT

Or even without ORDER BY (this clause does not make sense when loading data inside QV)

Re: Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 12 Feb 2016 16:08:51 GMT

Yes, that's correct. I found the solution with the link I posted as well.

Thank you.

Re: Unable to submit Oracle PL/SQL statement

Peter_Cammaert — Fri, 12 Feb 2016 16:28:49 GMT

And the reason is simple:

Whatever comes after a SQL prefix until (but not including) the semicolon will be sent through your open ODBC connection to the DB engine. QlikView Script Engine doesn't know anything about SQL (not really true but you get the idea) and as such is incapable of parsing and understanding a complex SQL statement. So it stuffs everything through the pipe for someone else to handle.

If you do not prefix such a statement with the SQL keyword, QlikView Script Engine will think that this series of keywords-clauses-&-operators is meant to be parsed and executed by the Script Engine. Which of course won't work.

SELECT is a special case, because it seems that it doesn't always require the SQL prefix.

Hope this helps.

Peter

Re: Unable to submit Oracle PL/SQL statement

Anonymous — Fri, 12 Feb 2016 16:35:48 GMT

Peter,

Thanks for the explanation.

Yes, I kind of figured that QV should not know anything about the actual Oracle SQL. It's a QV engine not an Oracle engine. I knew the actual parsing and execution was going to happen on the Oracle server. I initially though we could only submit SELECT and nothing more complcated than that. As I discovered and in your explanation, prefixing SQL

tells the QV engine to send the text after that and including the last semi-colon. I guess I'm learning QV through

writing Oracle PL/SQL.

Re: Unable to submit Oracle PL/SQL statement

Peter_Cammaert — Fri, 12 Feb 2016 16:42:33 GMT

Good luck!

Enjoy QlikView and the Qlik Community