DataPush, Post-push SQL

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
2.44K viewsDataLink
0

Hello,

I use an Oracle JDBC driver and want to post-push a PL/SQL Block, eg.
[color=”Blue”]declare
a number;
begin
select count(*)
into a
from dual;
end;[/color]

But Quantrix reports an error back:
[color=”Red”]Error while pushing data: ORA-06550: row 2, column 9:
PLS-00103: Encountered the symbol “end-of-file” when expecting …[/color]

Unfortunately, there are no references in the Documentation for DataPush. My question is: Are only allowed SQL Statements and no PL/SQL Blocks?

With kind regards,
Robert

0

Mike,

There is a second problem with the pre-/post push SQL parser. We could clearly demonstrate that the parser ignores SQL hints (colored red in the example), which means that such hints are not passed on to the JDBC driver.

Example: The post-push SQL statement …
[color=”Blue”]SELECT [color=”Red”]/*+ INDEX(T1 date_I) */[/color]
T1.OBJECT_ID,
T2.FULLNAME,
to_char(to_date(‘01.01.1972’, ‘dd.mm.yyyy’) + T1.UNIVERSAL_TIME / 24 / 3600, ‘YYYY-MM-DD HH24:MI’) “CET”,
T1.VALUE
FROM
OBJECT_FULLNAMES T2, HFD T1
WHERE
T1.LOCAL_TIME between (trunc(sysdate, ‘DD’) + 1/96) and (trunc(sysdate, ‘DD’) + 2)
AND (T2.OBJECT_ID = T1.OBJECT_ID)
[/color]

is passed through to the Oracle DB Server as …
[color=”Blue”]SELECT
T1.OBJECT_ID,
T2.FULLNAME,
to_char(to_date(‘01.01.1972’, ‘dd.mm.yyyy’) + T1.UNIVERSAL_TIME / 24 / 3600, ‘YYYY-MM-DD HH24:MI’) “CET”,
T1.VALUE
FROM
OBJECT_FULLNAMES T2, HFD T1
WHERE
T1.LOCAL_TIME between (trunc(sysdate, ‘DD’) + 1/96) and (trunc(sysdate, ‘DD’) + 2)
AND (T2.OBJECT_ID = T1.OBJECT_ID)
[/color]

Can this problem be resolved?

With kind regards,
Robert

0

Mike,

Thanks for your reply.

I tried the “one line” command but it dosn’t work either. I think that the “one line” formatting is not the problem because we use accurate post-push SQL statements across multiple lines, eg.
[color=”Blue”]Delete
from SCHEDULE
where
(nvl(HOURS, 0) = 0)
or (TIMESTAMP_BEGIN >= trunc(add_months(sysdate, 48), ‘YYYY’))
or (TIMESTAMP_BEGIN < trunc(sysdate, ‘IW’))[/color]

There are indications that the JDBC driver supports PL/SQL: [url=http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1014127:c09nneu2]http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1014127[/url:c09nneu2]

If so, the question arises whether the Quantrix pre- and post-push SQL panel allows only SQL statements, eg. select and delete, and no PL/SQL blocks.

We really need PL/SQL via post-push because we want Quantrix to start a database procedure after the data transaction process has completed.

With kind regards,
Robert

0

Hi – Thanks for your post.

In theory, Quantrix should be able to communicate and process anything that the JDBC driver can. However, the format of the SQL in the Pre and Post panels is important. It executes SQL based on lines in the editor. So if the SQL is broken up by newlines (for formatting purposes) it may not execute properly which admittedly is not documented well.

One thing you can try is to enter the entire command on one line without any line breaks and seeing if that makes the parser behave more as expected.

Please let us know your results.

Latest Questions