Home » SQL & PL/SQL » SQL & PL/SQL » How to select into a table variable with a table function (ORACLE 10G ,VISTA)
How to select into a table variable with a table function [message #393084] Fri, 20 March 2009 05:09 Go to next message
lzfhope
Messages: 67
Registered: July 2006
Member
HI,
i declared user type two like below:
TYPE_R IS OBJECT(
ID NUMBER,
Name varchar2(40)
);
TYPE_TR IS TABLE OF TYPE_R;
Then,i wrote script:
declare
    r type_tr:=type_tr();
  begin
    select * bulk collect into r from table(func_getNames(1));
  end;


But,i got a error:PL/SQL ORA_00947:NOT ENOUGH VALUE.
WHY?
HOW CAN I SELECT INTO "R" WITH THE TABLE FUNCTION.
FUNC_GETNames is a pipelined table function ,it return type type_tr.if run "select * from table(func_getNames(1))" ,oracle would not send any error,it does well!
Re: How to select into a table variable with a table function [message #393085 is a reply to message #393084] Fri, 20 March 2009 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Incorrect posting.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to select into a table variable with a table function [message #393087 is a reply to message #393084] Fri, 20 March 2009 05:12 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
ORA-00947: not enough values
Cause: This error occurs when a SQL statement requires two sets of values equal in number, but the second set contains fewer items than the first set. This can occur in a WHERE or HAVING clause in which a nested SELECT returns too few columns as in:


WHERE (A,B) IN (SELECT C FROM ...)

Another common cause of this error is an INSERT statement in which the VALUES or SELECT clause does not contain enough values needed for the INSERT, as in


INSERT INTO EMP(EMPNO,ENAME) VALUES('JONES')

Action: Check the number of items in each set and change the SQL statement to make them equal.


Please read the OraFAQ Forum Guide before posting.
http://www.orafaq.com/forum/t/88153/0/

What you tried?

Post your DDL and PL/SQL Script

Babu
Re: How to select into a table variable with a table function [message #393089 is a reply to message #393087] Fri, 20 March 2009 05:26 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
thanks ,
But,first,please watch carefully ! it is not the question you consider as !
Re: How to select into a table variable with a table function [message #393093 is a reply to message #393089] Fri, 20 March 2009 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you posted it correctly there would be no misinterpretation.

Regards
Michel

[Updated on: Fri, 20 March 2009 05:29]

Report message to a moderator

Re: How to select into a table variable with a table function [message #393226 is a reply to message #393084] Fri, 20 March 2009 15:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need to cast the type as shown below.

-- test environment:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE TYPE_R IS OBJECT(
  2  ID NUMBER,
  3  Name varchar2(40)
  4  );
  5  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE TYPE_TR IS TABLE OF TYPE_R;
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION FUNC_GETNames
  2    (p_num IN NUMBER)
  3    RETURN type_tr PIPELINED
  4  AS
  5    v_rec type_tr := type_tr ();
  6  BEGIN
  7    SELECT type_r (empno, ename)
  8    BULK   COLLECT INTO v_rec
  9    FROM   emp
 10    WHERE  deptno = p_num;
 11    FOR i IN 1 .. v_rec.COUNT LOOP
 12  	 PIPE ROW (v_rec(i));
 13    END LOOP;
 14    RETURN;
 15  END FUNC_GETNames;
 16  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT * FROM TABLE (func_getNames (10))
  2  /

        ID NAME
---------- ----------------------------------------
      7782 CLARK
      7839 KING
      7934 MILLER


-- reproduction of error:
SCOTT@orcl_11g> declare
  2  	 r type_tr:=type_tr();
  3    begin
  4  	 select *
  5  	 bulk	collect into r
  6  	 from	table(func_getNames(10));
  7    end;
  8  /
    from   table(func_getNames(10));
    *
ERROR at line 6:
ORA-06550: line 6, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored


-- correction (note casting on line 4):
SCOTT@orcl_11g> declare
  2  	 r type_tr:=type_tr();
  3    begin
  4  	 select type_r (t.id, t.name) 
  5  	 bulk	collect into r
  6  	 from	table(func_getNames(10)) t;
  7  	 for i in 1 .. r.count loop
  8  	   dbms_output.put_line (r(i).id || ' ' || r(i).name);
  9  	 end loop;
 10    end;
 11  /
7782 CLARK
7839 KING
7934 MILLER

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>

Re: How to select into a table variable with a table function [message #393333 is a reply to message #393226] Sat, 21 March 2009 12:20 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Great!
Thank Barbara very much!!
Thank Michel Cadot!
This question has puzzled me for a few days!
I hope oracle would improve this issue!
Thank again!

Re: How to select into a table variable with a table function [message #393334 is a reply to message #393333] Sat, 21 March 2009 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also do it as follow with a PL/SQL table:
SQL> declare
  2   type r is record (
  3     ID NUMBER,
  4     Name varchar2(40)
  5   );
  6   type tr is table of r index by pls_integer;
  7   t tr;
  8  begin
  9   select * bulk collect into t from table(func_getNames(10));
 10   for i in 1 .. t.count loop
 11     dbms_output.put_line (t(i).id || ' ' || t(i).name);
 12   end loop;
 13  end;
 14  /
7782 CLARK
7839 KING
7934 MILLER

PL/SQL procedure successfully completed.

Regards
Michel
Re: How to select into a table variable with a table function [message #393401 is a reply to message #393334] Sun, 22 March 2009 09:49 Go to previous message
lzfhope
Messages: 67
Registered: July 2006
Member

Thank Michel!
It alse run well!
Previous Topic: Tune the plsql
Next Topic: Deleting Duplicate Row Based on Timestamp
Goto Forum:
  


Current Time: Fri Dec 09 07:53:20 CST 2016

Total time taken to generate the page: 0.28207 seconds