Home » SQL & PL/SQL » SQL & PL/SQL » NESTED TABLE in table column
NESTED TABLE in table column [message #323926] Fri, 30 May 2008 01:17 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I had one script to create department pasted below in that i want details about
NESTED TABLE courses STORE AS courses_tab


CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;


after created this one i want to insert some data into that department table but it shows error.

 SQL> CREATE TABLE department (
  2  name VARCHAR2(20),
  3  director VARCHAR2(20),
  4  office VARCHAR2(20),
  5  courses CourseList)
  6  NESTED TABLE courses STORE AS courses_tab;

Table created.

SQL> INSERT INTO department
  2  VALUES(’Psychology’, ’Irene Friedman’, ’Fulton Hall 133’,
  3  CourseList(Course(1000, ’General Psychology’, 5),
  4  Course(2100, ’Experimental Psychology’, 4),
  5  Course(2200, ’Psychological Tests’, 3),
  6  Course(2250, ’Behavior Modification’, 4),
  7  Course(3540, ’Groups and Organizations’, 3),
  8  Course(3552, ’Human Factors in Busines’, 4),
  9  Course(4210, ’Theories of Learning’, 4),
 10  Course(4320, ’Cognitive Processes’, 4),
 11  Course(4410, ’Abnormal Psychology’, 4)));
VALUES(’Psychology’, ’Irene Friedman’, ’Fulton Hall 133’,
       *
ERROR at line 2:
ORA-00911: invalid character

[Updated on: Fri, 30 May 2008 01:19]

Report message to a moderator

Re: NESTED TABLE in table column [message #323930 is a reply to message #323926] Fri, 30 May 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use standard quote ' and not "smart" quote ’.

Regards
Michel
Re: NESTED TABLE in table column [message #323940 is a reply to message #323930] Fri, 30 May 2008 02:01 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Michel,

Please give details about below.why this statement is for and how it work.

NESTED TABLE courses STORE AS courses_tab
Re: NESTED TABLE in table column [message #323967 is a reply to message #323940] Fri, 30 May 2008 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=nested+table&tab_id=&format=ranked

Regards
Michel
Re: NESTED TABLE in table column [message #323987 is a reply to message #323940] Fri, 30 May 2008 04:06 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Again it will shows an error and for this i have checked all the datatypes and data also but it shows an error.

SQL> INSERT INTO department
  2  VALUES(to_char('Psychology'), 'Irene Friedman', 'Fulton Hall 133',
  3  CourseList(Course(1000, 'General Psychology', 5),
  4  Course(2100, 'Experimental Psychology', 4),
  5  Course(2200, 'Psychological Tests', 3),
  6  Course(2250, 'Behavior Modification', 4),
  7  Course(3540, 'Groups and Organizations', 3),
  8  Course(3552, 'Human Factors in Busines', 4),
  9  Course(4210, 'Theories of Learning', 4),
 10  Course(4320, 'Cognitive Processes', 4),
 11  Course(4410, 'Abnormal Psychology', 4)));
CourseList(Course(1000, 'General Psychology', 5),
           *
ERROR at line 3:
ORA-00932: inconsistent datatypes
Re: NESTED TABLE in table column [message #323996 is a reply to message #323987] Fri, 30 May 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not enough information to compute.
Review what you posted and tell us what is missing.

Regards
Michel
Re: NESTED TABLE in table column [message #324437 is a reply to message #323996] Mon, 02 June 2008 08:36 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I tried but i cant rectify the below can you please help me.


SQL> INSERT INTO department
  2  VALUES(to_char('Psychology'), 'Irene Friedman', 'Fulton Hall 133',
  3  CourseList(Course(1000, 'General Psychology', 5),
  4  Course(2100, 'Experimental Psychology', 4),
  5  Course(2200, 'Psychological Tests', 3),
  6  Course(2250, 'Behavior Modification', 4),
  7  Course(3540, 'Groups and Organizations', 3),
  8  Course(3552, 'Human Factors in Busines', 4),
  9  Course(4210, 'Theories of Learning', 4),
 10  Course(4320, 'Cognitive Processes', 4),
 11  Course(4410, 'Abnormal Psychology', 4)));
CourseList(Course(1000, 'General Psychology', 5),
           *
ERROR at line 3:
ORA-00932: inconsistent datatypes
Re: NESTED TABLE in table column [message #324438 is a reply to message #324437] Mon, 02 June 2008 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't know what is course and courselist.
Give the creation DDL.

Regards
Michel
Re: NESTED TABLE in table column [message #324440 is a reply to message #323926] Mon, 02 June 2008 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>to_char('Psychology'),
Why is TO_CHAR used here?
Re: NESTED TABLE in table column [message #324444 is a reply to message #324440] Mon, 02 June 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anacedent wrote on Mon, 02 June 2008 15:42
>to_char('Psychology'),
Why is TO_CHAR used here?

To go deeper in string psychology. Smile

Regards
Michel

Re: NESTED TABLE in table column [message #324446 is a reply to message #324438] Mon, 02 June 2008 08:55 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
SQL> CREATE TYPE Course AS OBJECT (
2 course_no NUMBER(4),
3 title VARCHAR2(35),
4 credits NUMBER(1));


SQL> CREATE TYPE CourseList AS TABLE OF Course;


SQL> CREATE TABLE department (
2 name VARCHAR2(20),
3 director VARCHAR2(20),
4 office VARCHAR2(20),
5 courses CourseList)
6 NESTED TABLE courses STORE AS courses_tab;





Re: NESTED TABLE in table column [message #324447 is a reply to message #324444] Mon, 02 June 2008 08:56 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
No it is my try againts the error.
Re: NESTED TABLE in table column [message #324454 is a reply to message #324447] Mon, 02 June 2008 09:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I think either what you are posting is not what you are running or your have an invisible control character somewhere. You may need to retype the whole thing to make sure that you don't. Please post a copy and paste of a complete continuous actual run of the whole thing, not separate pieces put together, in the same manner as I have done below, which shows that if you are running what you are posting it should work. And get rid of the to_char in front of psychology.

SCOTT@orcl_11g> drop table department
  2  /

Table dropped.

SCOTT@orcl_11g> drop type courselist
  2  /

Type dropped.

SCOTT@orcl_11g> drop type course
  2  /

Type dropped.

SCOTT@orcl_11g> CREATE TYPE Course AS OBJECT (
  2  course_no NUMBER(4),
  3  title VARCHAR2(35),
  4  credits NUMBER(1));
  5  /

Type created.

SCOTT@orcl_11g> CREATE TYPE CourseList AS TABLE OF Course;
  2  /

Type created.

SCOTT@orcl_11g> CREATE TABLE department (
  2  name VARCHAR2(20),
  3  director VARCHAR2(20),
  4  office VARCHAR2(20),
  5  courses CourseList)
  6  NESTED TABLE courses STORE AS courses_tab
  7  /

Table created.

SCOTT@orcl_11g> INSERT INTO department
  2  VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
  3  CourseList(Course(1000, 'General Psychology', 5),
  4  Course(2100, 'Experimental Psychology', 4),
  5  Course(2200, 'Psychological Tests', 3),
  6  Course(2250, 'Behavior Modification', 4),
  7  Course(3540, 'Groups and Organizations', 3),
  8  Course(3552, 'Human Factors in Busines', 4),
  9  Course(4210, 'Theories of Learning', 4),
 10  Course(4320, 'Cognitive Processes', 4),
 11  Course(4410, 'Abnormal Psychology', 4)))
 12  /

1 row created.

SCOTT@orcl_11g> 


Re: NESTED TABLE in table column [message #324555 is a reply to message #324454] Tue, 03 June 2008 02:17 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Given below is my script

SQL> INSERT INTO department
  2    VALUES(to_char('Psychology'), 'Irene Friedman', 'Fulton Hall 133',
  3    CourseList(Course(1000, 'General Psychology', 5),
  4    Course(2100, 'Experimental Psychology', 4),
  5    Course(2200, 'Psychological Tests', 3),
  6    Course(2250, 'Behavior Modification', 4),
  7    Course(3540, 'Groups and Organizations', 3),
  8    Course(3552, 'Human Factors in Busines', 4),
  9    Course(4210, 'Theories of Learning', 4),
 10    Course(4320, 'Cognitive Processes', 4),
 11    Course(4410, 'Abnormal Psychology', 4)));
  CourseList(Course(1000, 'General Psychology', 5),
             *
ERROR at line 3:
ORA-00932: inconsistent datatypes


SQL> /
  CourseList(Course(1000, 'General Psychology', 5),
             *
ERROR at line 3:
ORA-00932: inconsistent datatypes
Re: NESTED TABLE in table column [message #324560 is a reply to message #324555] Tue, 03 June 2008 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy Barbara test case, execute it, and post the result IN THE SAME WAY.

Regards
Michel
Re: NESTED TABLE in table column [message #324578 is a reply to message #324560] Tue, 03 June 2008 03:41 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
SQL>  drop table department
  2  /

Table dropped.

SQL> drop type courselist
  2  /

Type dropped.

SQL> drop type course
  2  
SQL> /

Type dropped.

SQL> CREATE TYPE Course AS OBJECT (
  2      course_no NUMBER(4),
  3      title VARCHAR2(35),
  4      credits NUMBER(1));
  5  /

Type created.

SQL> CREATE TYPE CourseList AS TABLE OF Course;
  2  /

Type created.

SQL> CREATE TABLE department (
  2      name VARCHAR2(20),
  3      director VARCHAR2(20),
  4      office VARCHAR2(20),
  5      courses CourseList)
  6      NESTED TABLE courses STORE AS courses_tab
  7  /

Table created.

SQL> INSERT INTO department
  2     VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
  3     CourseList(Course(1000, 'General Psychology', 5),
  4     Course(2100, 'Experimental Psychology', 4),
  5     Course(2200, 'Psychological Tests', 3),
  6     Course(2250, 'Behavior Modification', 4),
  7     Course(3540, 'Groups and Organizations', 3),
  8     Course(3552, 'Human Factors in Busines', 4),
  9     Course(4210, 'Theories of Learning', 4),
 10     Course(4320, 'Cognitive Processes', 4),
 11     Course(4410, 'Abnormal Psychology', 4)))
 12  /

1 row created.
Re: NESTED TABLE in table column [message #324580 is a reply to message #324578] Tue, 03 June 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is good for you.
Now check the other point Barbara mentioned:
Quote:
or your have an invisible control character somewhere

Regards
Michel

[Edit: correct typo.]

[Updated on: Tue, 03 June 2008 05:31]

Report message to a moderator

Re: NESTED TABLE in table column [message #324584 is a reply to message #324580] Tue, 03 June 2008 04:09 Go to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I can't able to find any diff b/w my script and Barbara script

But i confused why it will happend.Any its great support to me to get the solution very much thanks to everybody.
Previous Topic: Unable to capture the parameter values from a PL/SQL procedure
Next Topic: Defining Date Format while creation table
Goto Forum:
  


Current Time: Wed Dec 07 16:47:07 CST 2016

Total time taken to generate the page: 0.15890 seconds