Oracle, cast and multiset with union subquery

Question

I'm new at PL/SQL and I'm having problems creating a view using custom table types in Oracle. These are the types created:

create or replace
TYPE "Control" AS OBJECT ("Date" nvarchar2(10), "R" number(7,3), "Limit(7,3);
create or replace TYPE Controls AS TABLE OF "Control";

CREATE OR REPLACE TYPE Result_typ AUTHID CURRENT_USER AS OBJECT (
  "Program" varchar(10),
  "ID_User" nvarchar2(25),
  "Controls" Controls,
 )

In DB we have two different types of controls (c_control, g_control) stored in different tables with few common fields. I'm trying to create a view selecting all the different controls despite of their type so I use UNION clause. Here there is my view creation statement (with some subtle changes to make it simplier):

CREATE OR REPLACE VIEW "all_controls" OF Result_typ 
with object IDENTIFIER ("ID_User") 
as SELECT 'MYAPP' as Program, 
u.user_id as "ID_User", 
CAST(MULTISET(
     select to_char(control_date,'yyyy-mm-dd') as "Date", 
  r as "R", 
     limit as "Limit"
     from g_control
     where control_date between to_date('20130310','yyyymmdd') 
     and to_date('20130313','yyyymmdd')
  UNION
     select to_char(control_date,'yyyy-mm-dd') as "Date", 
 r as "R", 
 limit as "Limit",
 from control
 where and control_date between to_date('20130310','yyyymmdd') 
     and to_date('20130313','yyyymmdd')
) AS Controls) 
FROM user u
WHERE u.user_name like 'Scott';

When executing the statement from SQL Developer, the returned value is "SQL error: No more data to read from socket". Is there any problem performing a UNION inside the MULTISET? How can I select UNION result as MULTISET? Thanks in advance

What is g_control? A table or some other type? Create sample data, e.g. tables, data... — Art
If you're new to PL/SQL objects is the worst place to start. Even hardened PL/SQL practitioners find them gnarly. Bringing OO experience from another language such a sJava doesn't help; indeed it may make things more difficult, as the Oracle OO implementation is idiosyncratic. — APC
Also, with code please make sure you have posted the whole source. Your samples are currently incomplete. — APC

Jon Heller Jon Heller · Accepted Answer · 2013-03-12T18:29:50

SELECT
    CAST(MULTISET(
            select '2013-01-01' a, 1 b, 1 c from dual union all
            select '2013-01-01', 1, 1 from dual
    ) AS Controls) 
FROM dual;

ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7292
Session ID: 201 Serial number: 4479

Adding an extra inline view seems to fix it:

SELECT
    CAST(MULTISET(
        select * from
        (
            select '2013-01-01' a, 1 b, 1 c from dual union all
            select '2013-01-01', 1, 1 from dual
        )
    ) AS Controls) 
FROM dual;

CONTROLS(Control('2013-01-01', 1, 1), Control('2013-01-01', 1, 1))

(Thanks to MichaelS at Oracle forums)

Some other advice:

Avoid quoted identifiers. It makes objects harder to use.
Avoid storing dates as strings. ESPECIALLY if you are using object types. Stringly-typed object-relational data is bizarre.
AUTHID CURRENT_USER does not help if there are no functions.
Better indenting would help us understand the problem easier
Always include the full error message
A full schema definition would have helped.
There are lots of syntax errors in your example.
As @APC mentioned, Oracle's object-relational features are very difficult. If possible, you should reconsider using it. You should probably focus on regular relational technology first.
There's still some bug here. Check the alert log, you'll see something like ORA-07445: exception encountered: core dump [qcsfsqacn()+105] [ACCESS_VIOLATION] [ADDR:0x4] [PC:0x12286C1] [UNABLE_TO_READ] []. If this workaround isn't sufficient, you'll need to contact Oracle support about this bug. I already checked on My Oracle Support, there's currently no documentation for this error.

Oracle, cast and multiset with union subquery

1 Answers