Re: Where should place my sql scripts?

Peter,

In UNIX you can set the environment variable SQLPATH which allows you to include all the directories which contain SQL scripts. Although, I did find a bug in Oracle 9.2.0.2 O/S AIX 4.3.3 using the ksh shell.

Test 1

1. Create directory called tmp under /opt/oracle
2. Create script /opt/oracle/tmp/test.sql which contains "select sysdate from dual";
3. unset SQLPATH
4. export SQLPATH=/opt/oracle
5. Execute script from a different location ie. not /opt/oracle

SQL> @tmp/test.sql
SP2-0310: unable to open file "tmp/test.sql"

Test 2

1. Create script /CML/oracle/test2.sql which contains "select sysdate from dual";
2. Create script /opt/oracle/test.sql which calls test2.sql without specifying path ie. "@test2.sql" in test.sql script.
3. unset SQLPATH
4. export SQLPATH=$SQLPATH:/opt/oracle:/CML/oracle
5. Execute script from location /opt/oracle

SQL> @test.sql
SP2-0310: unable to open file "test2.sql"

Under Oracle 8.1.7.4 both test cases return the sysdate as expected.

HTH Roger.

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:37u9av87rjinlil2pk0fib3kon7kqgqn9t_at_4ax.com...
> Sqlplus is complaining that it cannot find my scipts.
> Can I specify where the scripts are in the sql command?
>
>
Received on Tue Apr 22 2003 - 04:56:30 CDT