Home » SQL & PL/SQL » SQL & PL/SQL » Query checker (Oracle 10g, XP)
Query checker [message #390542] Sat, 07 March 2009 03:32 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Hi all is there any procedure or function to check whether an oracle query that is correct or not .

Eg:

 SELECT * FROM EMP


To check whether this query is correct or not without executing.


Thanks
Re: Query checker [message #390544 is a reply to message #390542] Sat, 07 March 2009 03:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rakeshramm wrote on Sat, 07 March 2009 10:32
Hi all is there any procedure or function to check whether an oracle query that is correct or not .

Eg:

 SELECT * FROM EMP


To check whether this query is correct or not without executing.

No, there is nothing.
Just curious, which kind of "correctness" you want to check:
- syntactical - the query uses the syntax documented in SQL Reference
or semantical - all used tables/columns/types/... exist and the executing user has necessary privileges
? And what is the problem you are really trying to solve (as Oracle fails immediately when executing incorrect SQL)?
Re: Query checker [message #390546 is a reply to message #390542] Sat, 07 March 2009 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_sql.parse.

Regards
Michel
Re: Query checker [message #390547 is a reply to message #390544] Sat, 07 March 2009 03:54 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Hi flyboy

I will generate a query by selecting table and fields and keywords and what i want is to check the query that i created is is correct or not without executing it .Is there any function or procedure to check the whether the supplied query is correct or not

Thanks
Re: Query checker [message #390549 is a reply to message #390547] Sat, 07 March 2009 03:55 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


Thanks Michel
Re: Query checker [message #390550 is a reply to message #390546] Sat, 07 March 2009 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More precisely:
SQL> l
  1  declare c integer := dbms_sql.open_cursor;
  2  begin
  3    dbms_sql.parse(c, 'select * from emp', dbms_sql.native);
  4    dbms_sql.close_cursor (c);
  5  exception when others then dbms_sql.close_cursor (c); raise;
  6* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> 3
  3*   dbms_sql.parse(c, 'select * from emp', dbms_sql.native);
SQL> c:emp:nonexistent
  3*   dbms_sql.parse(c, 'select * from nonexistent', dbms_sql.native);
SQL> l
  1  declare c integer := dbms_sql.open_cursor;
  2  begin
  3    dbms_sql.parse(c, 'select * from nonexistent', dbms_sql.native);
  4    dbms_sql.close_cursor (c);
  5  exception when others then dbms_sql.close_cursor (c); raise;
  6* end;
SQL> /
declare c integer := dbms_sql.open_cursor;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 5

Regards
Michel
Re: Query checker [message #390551 is a reply to message #390550] Sat, 07 March 2009 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care to do this ONLY on query or other DML but NOT on DDL as DDL is executed at parse time.

Regards
Michel
Re: Query checker [message #390572 is a reply to message #390542] Sat, 07 March 2009 11:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Though not meant for this purpose, DBMS_ASSERT could be used to check for qualified SQL names etc.
Have not tried it for this purpose, just curious and could be blatantly wrong.
Re: Query checker [message #390726 is a reply to message #390547] Mon, 09 March 2009 08:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rakeshramm wrote on Sat, 07 March 2009 04:54
Hi flyboy

I will generate a query by selecting table and fields and keywords and what i want is to check the query that i created is is correct or not without executing it .Is there any function or procedure to check the whether the supplied query is correct or not


It's nice that you were able to rearrange your words, but please explain how this adds any extra information to the question asked. Define CORRECT.
Re: Query checker [message #390740 is a reply to message #390542] Mon, 09 March 2009 09:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
you could try wrapping the SQL to be examined in 'SELECT count(*) FROM (<query>) WHERE 1=2', running that dynamically, and seeing if it blows up. That'll parse the query without fetching all the rows.
declare
  v_ret   pls_integer;
  
  v_sql   varchar2(32000) := 'select * from test_080';
begin
  v_sql := 'SELECT COUNT(*) FROM ('||v_sql||') where 1=2';
  
  begin
    execute immediate v_sql into v_ret;
  
    raise_application_error(-20001,'Success');
  exception
    when others then
      raise_application_error(-20001,'Error '||sqlerrm);
  end;
end;
/
Previous Topic: rename column
Next Topic: get row above stated on
Goto Forum:
  


Current Time: Sun Dec 04 23:06:47 CST 2016

Total time taken to generate the page: 0.16171 seconds