ORACLE SQL PROBLEM

[StevenAHeithoff]

I get the following error when I exec
ResultSet = Statement.executeQuery (StatementSQL)
With StatementSQL equaling
SELECT * from table (cwms.ghk_test.retrieve_ts ( 'ft', 'NWD', 'FTPK-Fort Peck Dam-Missouri.Elev.Inst.1Hour.0.mrrppcs-rev', TO_DATE ('11Feb2008 00:00', 'DDMONYYYY HH24:MI'), TO_DATE ('11Feb2008 00:00', 'DDMONYYYY HH24:MI'), 'America/Regina'))

Note: For somereason it works fine inside the my toad software. Also other sqlstatements work so doesn't seem to be the connection.
Does anyone know if you have to give custom packages additional privilages in oracle to get them to work in STAROFFICE basic?



Running on Windows XP and Oracle 10.2.3, using ODBC connection.

[Villeroy]

Does the query work with the sql-editor in direct mode (Menu:Edit>Execute directly)?

[StevenAHeithoff]

Opening up the database connection and exec directly it gives this error which is not very helpfull for me?


Also, I forgot to say that I'm executing this in Staroffice Calc Marco.

[Villeroy]

Sorry, I'm not familiar with that kind of stuff. Possibly it is a problem with your jdbc or odbc driver.

Code: Select all

SELECT * from table (
cwms.ghk_test.retrieve_ts (
 'ft',
 'NWD',
 'FTPK-Fort Peck Dam-Missouri.Elev.Inst.1Hour.0.mrrppcs-rev',
 TO_DATE ('11Feb2008 00:00', 'DDMONYYYY HH24:MI'),
 TO_DATE ('11Feb2008 00:00', 'DDMONYYYY HH24:MI'),
 'America/Regina'
))

Does this select all fields from a table that is retrieved from a function cwms.ghk_test.retrieve_ts ?

[StevenAHeithoff]

Well yes it returns three fields from a table in a ref cursor?

[Villeroy]

The error message "parse error ..." with title "StarOffice Base" puzzles me since StarOffice is not supposed to parse anything in direct mode. In direct mode the query string should be passed over to the driver.

[StevenAHeithoff]

Sorry, I'm new to Star office I think I executed wrong. The error is from when it execute not directly.

This one happens when I have the execute directly checked.



Though it doesn't make much since to me since from the same connection. This sql statement works which to me means the connection is ok? My only question is does ODBC/StarOffice not support ref cursors?

[Villeroy]

I *believe* your driver is the delinquent. The "More" button of that error message uses to be quite verbose.

[StevenAHeithoff]

The More button contains This? Which was not every informative to me?

SQL Status: œ=ü
Error code: 1677194709

[Villeroy]

Aaaah, yes ... 1677194709 is my phone number actually.

I'm clueless. Nevertheless I would contact the creators of the (odbc?) database driver you are utilizing for the connection. May be the driver can not handle that complex queries (FROM derived from function call).