Toad World® Forums

TOAD and PL SQL Error


#1

Hi all, I’m using TOAD 9.5.0.31 (commercial!) and TNSNAMES for my connection is

----------------------------------------------------------
SOFTWARE\ORACLE\KEY_ORACLIENT10G_HOME1
ORACLE_HOME_NAME: OraClient10g_home1
ORACLE_HOME: C:\oracle\product\10.2.0\client_1
ORACLE_SID:
NLS_LANG: ITALIAN_ITALY.WE8MSWIN1252
SQLPATH: C:\oracle\product\10.2.0\client_1\dbs
LOCAL:
C:\oracle\product\10.2.0\client_1\Bin exists.
C:\oracle\product\10.2.0\client_1\Bin is in PATH.
Client DLL: C:\oracle\product\10.2.0\client_1\Bin\oci.dll
Client Version: 10.2.0.1.0
Home is valid.
----------------------------------------------------------

If I try to compile a package with a cursor declared as

----------------------------------------------------------
PROCEDURE xxxxx
AS
--
c_native_sys_ref_cur sys_refcursor;
BEGIN
OPEN c_native_sys_ref_cur FOR
WITH yyyy AS
....
----------------------------------------------------------

TOAD doesn’t accept the select format. It prefers

----------------------------------------------------------
PROCEDURE xxxxx
AS
--
c_native_sys_ref_cur sys_refcursor;
BEGIN
OPEN c_native_sys_ref_cur FOR
SELECT *
from (WITH yyyy AS
....
)
----------------------------------------------------------

However If I try to compile the package in the first format using theSQL Editor (C:\oracle\product\10.2.0\client_1\BIN\sqlplusw.exe), thepackages works and it is compiled!

----------------------------------------------------------
SQL*Plus: Release 10.2.0.1.0 - Production on Mer Nov 11 08:29:38 2009
 
Copyright (c) 1982, 2005, Oracle. All rights reserved.
 
 
Connesso a:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
----------------------------------------------------------

How to set TOAD options?
Thanks, Riccardo


#2

WITH syntax support I think came in a later version of Toad.

Can you upgrade off of 9.5, maybe up to v9.7 or v10?