Re: Parsing Query Text

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 27 May 2003 19:56:11 -0700
Message-ID: <92eeeff0.0305271856.38322ac3@posting.google.com>

"Axe" <way_at_too.much.spam> wrote in message news:<pRJAa.18752$Io.1682630_at_newsread2.prod.itd.earthlink.net>...
> I'm working on a windows app using C# that will be able to connect to either
> SQL Server or Oracle. I'll part of my project is a "query builder" however
> the user will interact with dropdowns and checkboxes so there is no chance
> that tablenames or fieldnames will be mispelled. Mostly I'd just like to
> run the SQL query through the DB and parse it for syntax errors (incorrect
> number of parenthesis, etc.). To do this in SQL Server you use the command
> SET PARSEONLY and I'll be using it like this (although this is VB.Net code):
>
> Dim con As New SqlConnection("...")
> con.open()
> Call New SqlCommand("Set parseonly on", con).ExecuteNonQuery()
> Try
> dim SQL as string = "create procedure foo as select *, from t"
> Call New SqlCommand(SQL,con).ExecuteNonQuery()
> Catch ex As SqlException
> Trace.Write(ex.ToString)
> Finally
> Call New SqlCommand("Set parseonly off", con).ExecuteNonQuery()
> End Try
>
>
>
> Now, the question of the day is this: What's the Oracle equivalent of SET
> PARSEONLY? Is this possible?
>
> Please don't get all "Oracle" on me and tell me to read textbooks and how
> bad I am for using SQL Server, OK! If you can help, fine, if not you don't
> have to waste your time. I know that I'll be learning more about Oracle as
> soon as I get to that point. I just need to know if this is
> possible--letting Oracle parse the query for me.
>
> Axe

There is DBMS_SQL package in Oracle that has a parse procedure. Although used primarily for dynamic SQL, you can use it to parse DML's. How you use it in your situation is upto your creative imagination but here is a small example,

SQL> CREATE TABLE foo (n1 NUMBER, n2 NUMBER);

Table created.

Notice the missing right ) in the sql statement in line 8. This should fail sql parse. See Oracle error at the end.

SQL> SET SERVEROUT ON
SQL> DECLARE

  2     cursor_    INTEGER;
  3     stmt_      VARCHAR2(200);
  4     err_no_    NUMBER;
  5     err_msg_   VARCHAR2(100);
  6  BEGIN
  7     cursor_ := DBMS_SQL.OPEN_CURSOR;
  8     stmt_ := 'INSERT INTO foo VALUES (1, 2';
  9     DBMS_SQL.PARSE(cursor_, stmt_, DBMS_SQL.NATIVE);
 10     DBMS_SQL.CLOSE_CURSOR(cursor_);
 11     EXCEPTION
 12        WHEN OTHERS THEN
 13           err_no_ := SQLCODE;
 14           err_msg_ := SUBSTR(SQLERRM(err_no_), 1, 100);
 15           DBMS_OUTPUT.PUT_LINE('Parse Error: ' || err_msg_);
 16           IF (DBMS_SQL.IS_OPEN(cursor_)) THEN
 17              DBMS_SQL.CLOSE_CURSOR(cursor_);
 18           END IF;   

 19 END;
 20 /

Parse Error: ORA-00917: missing comma

This is a simple example with simple sql statement. You can wear your creative hat to see how you can utilize this. To read more about this, goto http://tahiti.oracle.com and search for DBMS_SQL.

On a side note, if you are using check boxes and combo boxes to let a user build a query, why go thru all this? Why not just incorporate the correct syntax around user choices in the client? Just curious -:)

Regards
/Rauf Sarwar Received on Tue May 27 2003 - 21:56:11 CDT