Home » SQL & PL/SQL » SQL & PL/SQL » Using sql type with cursor in plsql block
Using sql type with cursor in plsql block [message #242134] Thu, 31 May 2007 14:22 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I wanted to bulk collect columns in a table into a sqltype .I cannot use plsql type here since I am using it with table function in a merge statement.

Sample code is like this

create type t_type as object(empno number);
create type tab_type as table of t_type;

declare
  t1 tab_type;
begin
  select empno bulk collect into t1;
end;
/


I get an error like

ORA-00932: inconsistent datatypes: expected UDT got number

Pls suggest some alternative

[Updated on: Thu, 31 May 2007 14:27]

Report message to a moderator

Re: Using sql type with cursor in plsql block [message #242138 is a reply to message #242134] Thu, 31 May 2007 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On which statement you have the error?

Please, once again, just copy and paste your screen.
Stop ./fa/1597/0/.

Regards
Michel
Re: Using sql type with cursor in plsql block [message #242173 is a reply to message #242134] Thu, 31 May 2007 21:33 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
1 declare
2  t1 tab_type;
3 begin
4  select empno bulk collect into t1 from emp;
5 end;
/



I get an error like

ERROR at line 4:
ORA-06550: line 4, column 8:
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored


Pls suggest some alternative
Re: Using sql type with cursor in plsql block [message #242191 is a reply to message #242173] Fri, 01 June 2007 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to post that:
SQL> create type t_type as object(empno number);
  2  /

Type created.

SQL> create type tab_type as table of t_type;
  2  /

Type created.

SQL> declare
  2    t1 tab_type;
  3   begin
  4    select empno bulk collect into t1 from emp;
  5   end;
  6  /
  select empno bulk collect into t1 from emp;
         *
ERROR at line 4:
ORA-06550: line 4, column 10:
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Is this so difficult?

Regards
Michel
Re: Using sql type with cursor in plsql block [message #242194 is a reply to message #242191] Fri, 01 June 2007 00:20 Go to previous message
beetel
Messages: 96
Registered: April 2007
Member
You have to use something like:

        select t_type (empno)
          bulk collect into t_tab
          from emp
         where ...


where t_type is your object type and t_tab is an instance of your tab_type.
Previous Topic: to find out sql statement executed on server
Next Topic: Is there such thing as update all??
Goto Forum:
  


Current Time: Thu Dec 08 04:22:49 CST 2016

Total time taken to generate the page: 0.10741 seconds