Re: Parse SQL-statement in SQL-Plus

Giganews Newsgroups
Subject: Re: Parse SQL-statement in SQL-Plus
Posted by:  yong3…@yahoo.com
Date: 14 Dec 2004

Ole Hansen wrote:
>
> I have created an SQL-file "copy.sql", that I am executing from
> SQL-Plus, using the following command:
>
> sqlplus xal_supervisor/xal_supertest_psw@XALTST @copy.sql
>
> copy.sql looks like this:
> -------------------------
> SET PAGES 9999
> WHENEVER SQLERROR EXIT;
> WHENEVER OSERROR EXIT;
> SPOOL copy.log;
>
> <SQL-statement 1>
> /
> <SQL-statement 2>
> /
> <SQL-statement 3>
> /
> ...
> <SQL-statement 999>
> /
> SPOOL OFF;
> EXIT;
> -------------------------
>
> copy.sql is a BIG sql-file, with lots of statements, and it takes 5
> ours to execute. It copies a lot of data...
>
> My problem is, that there are some "syntax errors" and other errors
in
> the file. Therefore i want to PARSE the file, before i execute it.
>
> I use SQL-Plus. How can i parse the entire file, before i execute it,
> so that i can find the all the errors ???

You got good responses from others. I want to add that you may consider

EXPLAIN PLAN FOR
<each SQL statement from your .sql file>
/

See if it errors out. Simply add EXPLAIN PLAN FOR in front of every SQL
statement (a sed or Perl script can easily do that). Note that explain
plan only accepts SQL statements. So you still have to manually verify
each SQL*Plus line, probably by typing each one at SQL> prompt unless
you're certain it's correct.

If you're not using 10g, you may get ORA-02402 (PLAN_TABLE not found).
Then run @?/rdbms/admin/utlxplan.sql first.

Yong Huang

Replies

None

In response to

Parse SQL-statement in SQL-Plus posted by Ole Hansen on 11 Dec 2004