Page 1 of 1
ORACLE SQL PROBLEM
Posted: Mon Feb 11, 2008 11:58 pm
by 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.
Re: ORACLE SQL PROBLEM
Posted: Tue Feb 12, 2008 12:02 am
by Villeroy
Does the query work with the sql-editor in direct mode (Menu:Edit>Execute directly)?
Re: ORACLE SQL PROBLEM
Posted: Tue Feb 12, 2008 12:14 am
by 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.
Re: ORACLE SQL PROBLEM
Posted: Tue Feb 12, 2008 12:28 am
by 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 ?
Re: ORACLE SQL PROBLEM
Posted: Wed Feb 13, 2008 9:28 pm
by StevenAHeithoff
Well yes it returns three fields from a table in a ref cursor?
Re: ORACLE SQL PROBLEM
Posted: Wed Feb 13, 2008 10:36 pm
by 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.
Re: ORACLE SQL PROBLEM
Posted: Wed Feb 13, 2008 11:22 pm
by 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?
Re: ORACLE SQL PROBLEM
Posted: Wed Feb 13, 2008 11:32 pm
by Villeroy
I *believe* your driver is the delinquent. The "More" button of that error message uses to be quite verbose.
Re: ORACLE SQL PROBLEM
Posted: Thu Feb 14, 2008 7:59 pm
by StevenAHeithoff
The More button contains This? Which was not every informative to me?
SQL Status: œ=ü
Error code: 1677194709
Re: ORACLE SQL PROBLEM
Posted: Fri Feb 15, 2008 12:21 am
by 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).