|Subject:||Re: Parse SQL-statement in SQL-Plus|
|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;
> 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
> 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.
Parse SQL-statement in SQL-Plus posted by Ole Hansen on 11 Dec 2004