Calling dbms_sql from java
marko, March 29, 2005 - 8:53 am UTC
So based on your answer, I'm assuming there is no way to call dbms_sql.parse diretly from Java?
March 29, 2005 - 10:30 am UTC
you could, but you would have to call
a) open
b) parse
c) get last error
d) close
you don't need a function, if that is what is bothering you. you can from jdbc just execute:
declare
l_theCursor integer default dbms_sql.open_cursor;
l_status integer;
begin
begin
dbms_sql.parse( l_theCursor, ?, dbms_sql.native );
exception
when others then l_status := dbms_sql.last_error_position;
end;
dbms_sql.close_cursor( l_theCursor );
? := l_status;
end;
in one call.
What about other querys
David C, April 18, 2005 - 12:23 pm UTC
get_error_offset is very useful but
when I try to find where a sentence like this is incorrect:
drop * from fooo5 ( and fooo5 doesn't exits )
in sql-plus I obtain :
SQL> drop table fooo5
2 ;
drop table fooo5
*
ERROR a la lÝnia 1:
//TRANSLATION: Error in line 1
ORA-00942: la taula o la vista no existeixen
//TRANSLATION: Table or view does not exist
but the function get_error_offset returns a 0.
Why ? Can we found the exact error point?
Thanks,
David C.
April 18, 2005 - 12:33 pm UTC
oh -- thanks for pointing that out
ouch, the get error offset should have this:
if (upper(substr(ltrim(?),1,6)) not in ( 'INSERT', 'UPDATE', 'DELETE', 'MERGE ', 'SELECT')
then
raise_application_error( -20001, 'not going to look' );
end if;
due to the fact that DDL is *executed* upon parse in DBMS_SQL. thank you so much for bringing that point up.
It would be unsafe to parse anything other than DML with this for that reason.
Then, there is nothing to do with not DML querys ?
David C., April 20, 2005 - 4:53 am UTC
Dear Tom,
SQLPlus do it, but now we only can found de error point in DML querys.
How we can find the error point with a PL (callable i.e. from Java or .NET) in drops, truncates or creates ?.
In order to merge it with get_error_offset.
Thank you very much.
David C.
April 20, 2005 - 6:57 am UTC
don't know that you can.