Re: afiedt.buf vs myOwnSql.sql

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 4 Jun 2002 07:14:01 -0700
Message-ID: <adihv90bps@drn.newsguy.com>

In article <ldbpfu4tqvbojahll6h9ts7flpuljf3vo6_at_4ax.com>, Martin says...
>
>I have this sql statement:
>
>compute sum of sal on deptno
>break on deptno skip 2
>select deptno, ename
>from emp
>order by deptno
>/
>
>Now, if I run it from afiedt.buf SQL+ complains about line 1. But if I
>run it from myOwnSql.sql, all is well. Why this difference?

Actually, if you run

SQL> @afiedt.buf

it'll run just fine, but if you

SQL> get myownsql.sql
SQL> / it'll fail. It is not the name of the file, but rather how the file is "loaded"

Consider:

scott_at_ORA817DEV.US.ORACLE.COM> @test
scott_at_ORA817DEV.US.ORACLE.COM> compute sum of sal on deptno
scott_at_ORA817DEV.US.ORACLE.COM> break on deptno skip 2
scott_at_ORA817DEV.US.ORACLE.COM> select deptno, ename

  2 from emp
  3 order by deptno
  4 /

DEPTNO ENAME
---------- ----------

        10 CLARK
           KING
           MILLER


        20 x
           ADAMS
           FORD
           SCOTT
           JONES


        30 y
           BLAKE
           MARTIN
           JAMES
           TURNER
           WARD

14 rows selected.

scott_at_ORA817DEV.US.ORACLE.COM> get test
  1 compute sum of sal on deptno
  2 break on deptno skip 2
  3 select deptno, ename
  4 from emp
  5* order by deptno
scott_at_ORA817DEV.US.ORACLE.COM> /
compute sum of sal on deptno
*
ERROR at line 1:
ORA-00900: invalid SQL statement

Now, if you cp test.sql to afiedt.buf and run @afiedt.buf -- it'll run ok.

It is because the contents of afiedt.buf is typically considered to be a SINGLE SQL statement -- so sqlplus is trying to run that entire string as a sql statement, and that fails.

If you "run" the file (@filename), sqlplus will parse it line by line and do the right thing.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Jun 04 2002 - 09:14:01 CDT