471,049 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

ORA-00947: WHY!?!?!?

Hello,

I have the following types/table:

create or replace type point as object(
x number(3),
y number(3),
z number(3)
);
/

create or replace type cube as object(
id number(5),
bottom_corner point,
top_corner point,
member function volume return number,
member function surface return number,
pragma restrict_references(volume, WNDS),
pragma restrict_references(surface, WNDS)
);
/

create table Cubes of cube(
id primary key,
bottom_corner not null,
top_corner not null
);
Why does the following line in a procedure i have written

give me these errors:

create or replace procedure cube_intersect(ident IN NUMBER) as
this_cube Cubes%ROWTYPE;
....
....

begin
select * into this_cube from Cubes where id = ident;

^ERROR
36/3 PL/SQL: SQL Statement ignored
36/23 PL/SQL: ORA-00947: not enough values

Thanks,
Vishal
Jul 19 '05 #1
1 8076
"Vishal" <va*****@yahoo.com> wrote in message
news:3f*************************@posting.google.co m...
Hello,

I have the following types/table:

create or replace type point as object(
x number(3),
y number(3),
z number(3)
);
/

create or replace type cube as object(
id number(5),
bottom_corner point,
top_corner point,
member function volume return number,
member function surface return number,
pragma restrict_references(volume, WNDS),
pragma restrict_references(surface, WNDS)
);
/

create table Cubes of cube(
id primary key,
bottom_corner not null,
top_corner not null
);
Why does the following line in a procedure i have written

give me these errors:

create or replace procedure cube_intersect(ident IN NUMBER) as
this_cube Cubes%ROWTYPE;
...
...

begin
select * into this_cube from Cubes where id = ident;

^ERROR
36/3 PL/SQL: SQL Statement ignored
36/23 PL/SQL: ORA-00947: not enough values

Thanks,
Vishal


Vishal,

The primary reason appears to be the nested objects in the definition of the
CUBE object. If I create a procedure as follows, then I have no compilation
or execution errors on 10.1.0.2 on WinXP Pro:

CREATE OR REPLACE PROCEDURE
cube_intersect(
p_ident IN NUMBER
)
AS
-- this_cube Cubes%ROWTYPE;
TYPE cubes_type IS RECORD (
id NUMBER,
bottom_corner
point,
top_corner
point
);
l_this_cube cubes_type;
l_this_point points%ROWTYPE;
BEGIN
SELECT * INTO l_this_cube FROM cubes WHERE id = p_ident;
SELECT * INTO l_this_point FROM points WHERE ROWNUM = 1;
END;
/

Firstly, I created a RECORD that matched the structure of the CUBE object.
There was no problem with the included objects.

Secondly, I created a table (POINTS) with the POINT object type. The
compiler had no problem with one level of object definition.

Douglas Hawthorne
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Adam Ruth | last post: by
6 posts views Thread by bdj | last post: by
reply views Thread by basmgokul | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.