Using an object type
Michel Cadot, July 25, 2010 - 2:44 am UTC
Hi,
I'm not sure I completly understand OP's question but if "execute immediate" can't use PL/SQL types it can use a custom object type:
SQL> create or replace type button_rec IS object (
2 label VARCHAR2(40),
3 action VARCHAR2(1000),
4 id VARCHAR2(40),
5 visibility VARCHAR2(40))
6 /
Type created.
SQL> create or replace TYPE button_tab IS TABLE OF button_rec
2 /
Type created.
SQL> create or replace procedure p (p_data in out button_tab)
2 as
3 begin
4 for i in 1 .. p_data.count loop
5 p_data(i).label := upper(p_data(i).label);
6 end loop;
7 end;
8 /
Procedure created.
SQL> declare
2 l_data button_tab := button_tab();
3 begin
4 for i in 1 .. 3 loop
5 l_data.extend;
6 l_data(i) := button_rec(null, null, null, null);
7 l_data(i).label := 'hello ' || i;
8 l_data(i).action := 'action ' || i;
9 l_data(i).id := i;
10 end loop;
11
12 execute immediate 'begin p(:data); end;' using in out l_data;
13
14 for i in 1 .. l_data.count loop
15 dbms_output.put_line (l_data(i).label);
16 end loop;
17 end;
18 /
HELLO 1
HELLO 2
HELLO 3
PL/SQL procedure successfully completed.
Regards
Michel
July 26, 2010 - 8:20 am UTC
the question was:
"How to use "Execute Immediate" with a record type instead of an Object type"
:)
Doh!
Michel Cadot, July 26, 2010 - 10:36 am UTC
Seems I have to improve my english skills, I can't read text between parentheses.
:)
Regards
Michel
Thank you
Walter Alvarado, July 27, 2010 - 3:54 am UTC
This is exactly what I needed.
The only thing I added to your solution was to overload the function as a procedure to save the out parameter(results) on a global as well.
As you mentioned, this would be not too nice because of the overhead but this is more than acceptable in my case.