INSERT SELECT with SELECT's column object

Question

I have this code.

CREATE TABLE USERS(
  USERNAME NVARCHAR2(30),
  USER_TYPE VARCHAR2(13)
);
/

CREATE OR REPLACE TYPE OBJ_USER_TYPE IS OBJECT(
  USER_TYPE VARCHAR2(13),
  USER_LEVEL NUMBER(38, 0)
);
/

CREATE OR REPLACE TYPE OBJ_USER IS OBJECT(
  USERNAME NVARCHAR2(30),
  USER_TYPE OBJ_USER_TYPE
);
/

CREATE OR REPLACE TYPE NST_USERS IS TABLE OF OBJ_USER;
/

DECLARE
  objUser OBJ_USER NOT NULL DEFAULT OBJ_USER('MARION', OBJ_USER_TYPE('USER', 3));
  tblUSERS NST_USERS NOT NULL DEFAULT NST_USERS(objUser);
BEGIN
  INSERT INTO USERS(USERNAME, USER_TYPE)
  SELECT USERNAME, USER_TYPE.USER_TYPE
  FROM TABLE(tblUSERS);
END;
/

It raises an error of "invalid identifier" just like here.
But when I used:

INSERT INTO USERS(USERNAME, USER_TYPE)
SELECT USERNAME, TREAT(USER_TYPE AS OBJ_USER_TYPE).USER_TYPE
FROM TABLE(tblUSERS);

It raised error "too many values".
What's wrong with this code?

Can you show us the description of `USERS` table also type `NST_USERS` ? — Maheswaran Ravisankar, Oct 02 '14 at 05:54
Unrelated but: you don't need the `;` **and** the `/` for non PL/SQL blocks: http://stackoverflow.com/a/10207695/330315 `create ... type ...;/` will actually run that statement *twice* — , Oct 02 '14 at 06:02
@a_horse_with_no_name but creating types wants it. Different from other DDLs. — Maheswaran Ravisankar, Oct 02 '14 at 06:17
@MaheswaranRavisankar: You are right. I wanted to write `create table` :) — , Oct 02 '14 at 06:38

Maheswaran Ravisankar · Accepted Answer · 2014-10-02T06:14:04.793

0

DECLARE
  objUser OBJ_USER ;
  tblUSERS NST_USERS;
BEGIN
  objUser := OBJ_USER('MARION', OBJ_USER_TYPE('USER', 3));
  tblUSERS := NST_USERS(objUser);

  INSERT INTO USERS(USERNAME, USER_TYPE)
    SELECT USERNAME, TREAT(USER_TYPE AS OBJ_USER_TYPE).USER_TYPE
     FROM
    TABLE(tblUSERS);

END;
/

Fiddle Demo

edited Oct 02 '14 at 06:14

answered Oct 02 '14 at 06:02

Maheswaran Ravisankar

17,652
6
47
69

Sorry but it's only a typo. I already edit it from USER_TYPE to OBJ_USER_TYPE. There's still an error. – marion-jeff Oct 02 '14 at 06:10
@marionjeff can you check my edit? .. I see your [updated Question behaves as you expected too](http://sqlfiddle.com/#!4/2491b/8)! What version of database are you using? – Maheswaran Ravisankar Oct 02 '14 at 06:20

INSERT SELECT with SELECT's column object

1 Answers1