sqlplus command on Command Window

[Nicolas D.]

Hi,

I did see that "sqlplus" command on Command Window seems not working -- it open a Cmd.exe window that do nothing, while PL/SQL Developer 13 show "Not Responding" while waiting. SQL*Plus seems to never start on the cmd.exe window.

I did try while I was also testing that creating a view that contain a WITH FUNCTION... seems not to work either on Beta 3. (I normally use sqlplus command to run it in such case).

Database server version is 12.1.0.2 and client version is 12.2.0.1

PL/SQL Developer
Version 13.0.0.1873 Beta (64 bit)
Windows 8.1 Build 9600

Physical memory : 16,677,944 kB (5,984,232 available)
Paging file : 19,168,312 kB (4,989,288 available)
Virtual memory : 137,438,953,344 kB (137,438,012,688 available)

Parameters
C:\PLSQLDeveloper64_13BETA\plsqldev.exe

Preferences
Session mode: Single
OCI Library:
Use OCI7: False
Allow Multiple Connections: True

Preference Files
C:\PLSQLDeveloper64_13BETA\Preferences\Default\Default.ini
C:\Users\nicolasd\AppData\Roaming\PLSQL Developer 13\Preferences\NicolasD\default.ini

Debug file
C:\PLSQLDeveloper64_13BETA\PlSqlDev.elf

Plug-Ins
*Active Query Builder (C:\PLSQLDeveloper64_13BETA\PlugIns\ActiveQueryBuilder.dll)
*PL/SQL Documentation (plsqldoc) (C:\PLSQLDeveloper64_13BETA\PlugIns\plsqldoc.dll)
(* is Active)

Aliases
LDEV
...

Homes
OracleHome1 (C:\Oracle\Middleware\JDev12)
OraDB12Home3 (C:\oracle\product\12.2.0\dbhome_1)

DLLs
C:\oracle\product\12.2.0\dbhome_1\bin\oci.dll

TNS File
C:\oracle\admin\tnsnames\tnsnames.ora

Using
Home: OraDB12Home3
DLL: C:\oracle\product\12.2.0\dbhome_1\bin\oci.dll
OCI: version 12.1 (12.2.0.1.0)
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

Character Sets
Character size: 4 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: AMERICAN_AMERICA.WE8MSWIN1252
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: AL32UTF8

Process
Working Set = 154,079,232
Memory = 29,993,888
GDI Objects = 1421
User Objects = 513
Handles = 657

Monitors
PixelsPerInch = 96

Id = 0
PPI = 120
Primary = True
Handle = 448138955
Left = 0
Top = 0
Width = 1920
Height = 1080

Id = 1
PPI = 96
Primary = False
Handle = 548346175
Left = 3840
Top = 0
Width = 1920
Height = 1080

Id = 2
PPI = 96
Primary = False
Handle = 78845729
Left = 1920
Top = 0
Width = 1920
Height = 1080

MainFormOnTaskbar = True

 

[Marco Kalter]

The SQLPLUS command does indeed not seem to work. We'll fix it.

 

[Nicolas D.]

Thanks.

and about plan for VIEW WITH FUNCTION to be accepted in Command Window?

 

[Marco Kalter]

We'll check it out.

 

[Nicolas D.]

Marco, restested with Beta 4:
- SQLPLUS work fine as before.
- CREATE VIEW with FUNCTION seems not. Still rejecting it in both SQL Window or Command Window. Work fine in SQLPLUS.

Here my test view I did use. Goal of that function is to extract data from XML. I did add the function to avoid exception (if XML stored in CLOB is not valid, it return NULL instead of failing).
Right now it still stop at the first ; regardless.

CREATE OR REPLACE FORCE VIEW FUNCTIONTESTVIEW AS
WITH
FUNCTION getXML(oClob CLOB) RETURN SYS.XMLTYPE IS
BEGIN
RETURN XMLTYPE(oCLob);
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;
testxml AS
(SELECT 1 testid, '20181105This is a test 1' XMLDATA FROM dual
UNION ALL
SELECT 2 testid, '20181104This is a test 2 that will be ignored' XMLDATA FROM dual
UNION ALL
SELECT 3 testid, '20181106This is a test 3' XMLDATA FROM dual),
base AS
(SELECT t.testid,x.*
FROM testxml t,
xmltable('//*' passing getXML(t.XMLDATA) columns node_name VARCHAR2(4000) path 'name()', node_value VARCHAR2(4000) path
'text()' /*, node_desc VARCHAR2(4000) PATH '@DESC'*/) x)
SELECT *
FROM base
/

 

[Marco Kalter]

Okay. This may be caused by the "force" option. We'll check it out.

 

[Nicolas D.]

checked in Beta 5, all working.

Thanks!