Re: Parse SQL-statement in SQL-Plus

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_at_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 Received on Tue Dec 14 2004 - 10:52:09 CST