| Record vs Object problem [message #600579] |
Fri, 08 November 2013 11:04  |
 |
ageless1946
Messages: 3
Registered: November 2013
|
Junior Member |
|
|
I have a stored procedure that is attempting a bulk collect from a execute immediate. It works if I define:
create or replace procedure t_test(idList IN varchar2) IS
BEGIN
DECLARE
type trec is record (id NUMBER, key VARCHAR2(36));
type ttable_TYPE is table of trec;
ttable ttable_type;
dyn_sql varchar2(500);
BEGIN
dyn_sql := 'SELECT ID, KEY FROM TTABLE WHERE ID IN ( ' || idList || ')'
EXECUTE IMMEDIATE DYN_SQL
BULK COLLECT INTO ttable;
END;
END;
the problem is that I really need to do the following:
CREATE OR REPLACE TYPE TREC AS OBJECT
(
ID NUMBER,
KEY VARCHAR2(36)
);
/
CREATE OR REPLACE TYPE TTABLE AS TABLE OF TREC;
/
create or replace procedure t_test(idList IN varchar2, return_array IN OUT ttable) IS
BEGIN
DECLARE
dyn_sql varchar2(500);
BEGIN
dyn_sql := 'SELECT ID, KEY FROM TTABLE WHERE ID IN ( ' || idList || ')'
EXECUTE IMMEDIATE DYN_SQL
BULK COLLECT INTO return_array;
END;
END;
the error I get on the EXECUTE IMMEDIATE IS
ORA-00932: inconsistent datatypes: expected - got -
the only difference is that the external TYPE is an OBJECT versus the internal stored procedure type of RECORD
|
|
|
|
|
|
|
|
| Re: Record vs Object problem [message #600586 is a reply to message #600579] |
Fri, 08 November 2013 12:40  |
|
ageless1946
Messages: 3
Registered: November 2013
|
Junior Member |
|
|
I found the problem.
I needed to specify the object type in the select to establish the type it is dealing with.
CREATE OR REPLACE TYPE TREC AS OBJECT
(
ID NUMBER,
KEY VARCHAR2(36)
);
/
CREATE OR REPLACE TYPE TTABLE AS TABLE OF TREC;
/
create or replace procedure t_test(idList IN varchar2, return_array IN OUT ttable) IS
BEGIN
DECLARE
dyn_sql varchar2(500);
BEGIN
dyn_sql := 'SELECT T_REC(ID, KEY) FROM SOMETABLE WHERE ID IN ( ' || idList || ')'
EXECUTE IMMEDIATE DYN_SQL
BULK COLLECT INTO return_array;
END;
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
