Factored subquery

F

Flieger

Member²
Hi,
i was trying to get a factored subquery to work but could not get any results.
In sql*plus this works fine:

with sub_qry
as
(
select empno,ename,sal
from emp
)
select * from sub_qry
/
The query returns 14 rows.
In plsdev (SQL or Command window) i get zero rows.

Can you please take a look?

Thanks,
Christoph
 
Maybe this is an environment problem. Can you go to Help > Support Info, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?
 
Here you go:

--- Info ---

PL/SQL Developer
Version 7.0.0.1050 (MBCS)
02.31551 - 5 user license
Windows XP version 6.0 (build 2600) Service Pack 2

Physical memory : 523,496 kB (127,660 available)
Paging file : 1,276,964 kB (782,132 available)
Virtual memory : 2,097,024 kB (1,991,752 available)

Parameters
C:\Program Files\PLSQL Developer 7\plsqldev.exe

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Plug-Ins
*Active Query Builder (C:\Program Files\PLSQL Developer 7\PlugIns\ActiveQueryBuilder.dll)
*Browser Extender version 2.0 by Joachim Rupik (C:\Program Files\PLSQL Developer 7\PlugIns\be.dll)
*FTP File Interface 1.1 (C:\Program Files\PLSQL Developer 7\PlugIns\ftp.dll)
*Named Sql (DB) (C:\Program Files\PLSQL Developer 7\PlugIns\named_sql_db.dll)
*PL/SQL Documentation (plsqldoc) (C:\Program Files\PLSQL Developer 7\PlugIns\plsqldoc.dll)
*Shortcuts Plug-In v1.2 by Tiger (C:\Program Files\PLSQL Developer 7\PlugIns\shortcuts.dll)
(* is Active)

Aliases
asdb
orcl.world
Rep60CRUEPPRICH
Rep60CRUEPPRICH.world
rosie
...

Homes
dev6i (C:\orant)

DLLs
C:\orant\bin\oci.dll
C:\orant\bin\ORA803.DLL
C:\orant\bin\ORA804.DLL
C:\orant\bin\ORA805.DLL

TNS File
C:\orant\Net80\Admin\tnsnames.ora

Using
Home: dev6i
DLL: C:\orant\bin\oci.dll
OCI: Version 8.0
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Character size: 1 byte(s)

--- Registry ---

SOFTWARE\ORACLE
ORACLE_HOME = C:\orant
ORACLE_HOME_NAME = dev6i
SHARED_ORACLE_HOME = C:\orant
API = C:\orant\DBS
NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1
COMPANY_NAME = Williamson-Dickie
ORACLE_GROUP_NAME = Oracle for Windows NT
ORACLE_JDK = C:\orant\JDK\
OI_NLS32 = C:\orant\orainst\NLB
RDBMS80 = C:\orant\RDBMS80
PLSQL80 = C:\orant\PLSQL80
CORE40 = C:\orant\CORE40
NLSRTL33 = C:\orant\NLSRTL33
ORA_NLS33 = C:\orant\NLSRTL33\DATA
PRO80 = C:\orant\PRO80
OTRACE80 = C:\orant\OTRACE80
NET80 = C:\orant\NET80
SQLPATH = C:\orant\dbs;C:\orant\bin;C:\orant\report60\server\security
PLUS80 = C:\orant\PLUS80
EXECUTE_SQL = PLUS80
PLUS_DFLT = PLUS80
MM60 = C:\orant\TOOLS\COMMON60
UI60 = C:\orant\TOOLS\COMMON60
TK60 = C:\orant\TOOLS\COMMON60
FORMS60_JAVADIR = C:\orant\FORMS60\java
FORMS60_OUTPUT = C:\orant\tools\web60\temp
FORMS60_MAPPING = /dev60temp/
FORMS60_WALLET = C:\orant\FORMS60\WALLET
FORMS60_REPFORMAT = HTML
FORMS60_PATH = C:\orant\FORMS60;C:\orant\TOOLS\OPEN60\PLSQLLIB;c:\dev
FORMS60 = C:\orant\FORMS60
VGS60 = C:\orant\TOOLS\COMMON60
DE60 = C:\orant\TOOLS\COMMON60
CA_GPREFS = C:\orant
CA_UPREFS = C:\orant
QT_PLAYER = OMO
DEV2000_SOURCECONTROL = PVCS
DEV2000_PROJECT = None
DEV2000_SCVIEWER = Notepad.exe
DEV2000_SCCONVERT = FALSE
DOC60 = C:\orant\TOOLS\DOC60
OIN60 = C:\orant\OIN60
ORAINFONAV_DOCPATH = C:\orant\OIN
ORAINFONAV_CDPATH = C:\dl\6i\disk\INSTALL
BROWSER60 = C:\orant\BROWSE60
GRAPHICS60 = C:\orant\TOOLS\DBTAB60\GRAPH60
OCL60 = C:\orant\TOOLS\DBTAB60\GRAPH60
GRAPHOGD60 = C:\orant\TOOLS\DBTAB60\GRAPH60\GWIZ_OGD
GRAPHBMP60 = C:\orant\TOOLS\DBTAB60\GRAPH60\GWIZ_BMP
GWIZARD60 = C:\orant\TOOLS\DBTAB60\GRAPH60
OIN = C:\orant\OIN
doc60_nls = C:\orant\TOOLS\DOC60\US
OTM60 = C:\orant\OTM60
PJ60 = C:\orant\PJ60
OPEN60 = C:\orant\TOOLS\OPEN60
D2TX60 = C:\orant\TOOLS\OPEN60\TUXEDO
OPEN60_PLSQL = C:\orant\TOOLS\OPEN60\PLSQLLIB
ORACLE_JINIT = C:\orant\JINIT
REPORTS60_JNI_DLL = C:\orant\jdk\bin\javai.dll
REPORTS60_CLASSPATH = C:\orant\jdk\lib\classes.zip;C:\orant\REPORT60\java\jars\myreports60.jar;C:\orant\REPORT60\java\jars\xmlparser.jar
REPORTS60_DB_AUTH = dbauth.htm
REPORTS60_SYS_AUTH = sysauth.htm
REPORTS60_EXPRESS_UI = C:\orant\bin
GSINSTALLDIR = C:\orant\olap\ece620
GSCONNECTDIR = C:\orant\olap\ecf620
REPORTS60_PATH = c:\dev;C:\orant\REPORT60\ADMIN\TEMPLATE\US;C:\orant\REPORT60
REPORTS60_TMP = C:\orant\REPORT60\TMP
REPORTS60_JNI_LIB = C:\orant\jdk\bin\javai.dll
REPORTS60_DEV2K = FALSE
RW60 = C:\orant\REPORT60
REPORTS_RUNTIME = rwrun60.exe
REPORTS_BUILDER = rwbld60.exe
REPORTS60_SPLASH = TRUE
UI_ICON = C:\
inst_loc = C:\Program Files\Oracle\Inventory
ORA_CRS_HOME = C:\oracle\product\10.1.0\db_2

SOFTWARE\ORACLE\ALL_HOMES
HOME_COUNTER = 1
DEFAULT_HOME = dev6i
LAST_HOME = 0

SOFTWARE\ORACLE\ALL_HOMES\ID0
NAME = dev6i
PATH = C:\orant
NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1

SOFTWARE\ORACLE\HOME0
ID = 0
ORACLE_HOME = C:\orant
ORACLE_HOME_NAME = dev6i
SHARED_ORACLE_HOME = C:\orant
API = C:\orant\DBS
NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1
COMPANY_NAME = Williamson-Dickie
ORACLE_GROUP_NAME = Oracle for Windows NT
D2K60_COMPONENTS = Oracle Forms & Reports 6i Comps
D2K60_ADMINISTRATION = Oracle Forms & Reports 6i Admin
D2KOR60_ADMINISTRATION = Oracle Reports 6i Admin
D2KIF60_ADMINISTRATION = Oracle Forms 6i Admin
D2K60_DEMOS = Oracle Forms & Reports 6i Demos
D2K60_DOCS = Oracle Forms & Reports 6i Doc
D2K60_GROUP = Oracle Forms & Reports 6i
D2KOR60_GROUP = Oracle Reports 6i
D2KIF60_GROUP = Oracle Forms 6i
D2K60_DRIVERS = Oracle Forms & Reports 6i Direct Drivers
OPEN2K60_DOCS = Oracle Forms & Reports 6i Doc
OI_NLS32 = C:\orant\orainst\NLB

SOFTWARE\ORACLE\JInitiator

SOFTWARE\ORACLE\JInitiator\1.3.1.17
JavaHome = C:\Program Files\Oracle\JInitiator 1.3.1.17

SOFTWARE\ORACLE\ORACLE_HOMES

SOFTWARE\ORACLE\ORACLE_HOMES\ORACLE0
ORACLE_HOME = C:\orant
ORACLE_HOME_NAME = dev6i
SHARED_ORACLE_HOME = C:\orant
API = C:\orant\DBS
NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1
COMPANY_NAME = Williamson-Dickie
ORACLE_GROUP_NAME = Oracle for Windows NT

--- Path ---

C:\orant\bin
C:\oracle\product\10.1.0\db_2\bin
C:\oracle\product\10.1.0\db_2\jre\1.4.2\bin\client
C:\oracle\product\10.1.0\db_2\jre\1.4.2\bin
C:\oracle\product\10.1.0\Db_1\bin
C:\oracle\product\10.1.0\Db_1\jre\1.4.2\bin\client
C:\oracle\product\10.1.0\Db_1\jre\1.4.2\bin
C:\WINNT\system32
C:\WINNT
C:\WINNT\system32\WBEM
C:\Program Files\ATI Technologies\ATI Control Panel
C:\Program Files\Common Files\Adaptec Shared\SystemC:\PROGRA~1\IBM\CLIENT~1\shared
C:\Program Files\Symantec\ClientVPN
C:\orant\jdk\bin
C:\jdk1.3\bin
C:\Program Files\QuickTime\QTSystem\
C:\LLPRO\

--- TNS names ---

rosie.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 18.0.0.232)(PORT = 1522))
)
(CONNECT_DATA =
(SID = db1)
(SERVER = DEDICATED)
)
)
WCS52.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wcs5)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wcs2)
(SERVER = DEDICATED)
)
)
WCS53.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wcs5)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wcs3)
(SERVER = DEDICATED)
)
)
WCS62.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.242)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wcs2)
(SERVER = DEDICATED)
)
)
WCS63.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.242)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wcs3)
(SERVER = DEDICATED)
)
)
asdb.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.242)(PORT = 1521))
)
(CONNECT_DATA =
(SID = asdb)
(SERVER = DEDICATED)
)
)
WCS172.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.183.217)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wcs2)
(SERVER = DEDICATED)
)
)
VOXVLS9.DICKIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.239)(PORT = 1521))
)
(CONNECT_DATA =
(SID = VOXVLS9)
(SERVER = DEDICATED)
)
)
orcl.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
)
)
Rep60CRUEPPRICH,Rep60CRUEPPRICH.world=(ADDRESS=(PROTOCOL=tcp)(HOST=CRUEPPRICH)(PORT=1949))
 
I see you are using Oracle Net 8.0. This probably causes the problem. Can you upgrade to a more recent Oracle Net version?
 
You must log in or register to reply here.

Similar threads

Z
  • Discussion Discussion
PLSQL -> put into Result Set tabs in SQL window?
Replies
1
Views
265
Marco Kalter
Marco Kalter
C
  • Discussion Discussion
please change the Beautifier output for nested queries
Replies
1
Views
1K
Marco Kalter
Marco Kalter
C
  • Discussion Discussion
Check connection does not work in Multisession mode which leads to freezing app.
Replies
2
Views
1K
czdavidm
C
J
  • Discussion Discussion
Copy to Excel / Office 365 - Autosave turned off message
Replies
6
Views
4K
Jason McCleskey
J
J
  • Discussion Discussion
Character Set Issues - NLS_LANG
Replies
11
Views
23K
johnsok
johnsok
Back
Top