Thanks for the question, Walter.

Asked: July 23, 2010 - 7:06 am UTC

Last updated: July 26, 2010 - 8:20 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

I'm trying to dynamically call a procedure with record type in and out parameters. I would like to use the "Execute immediate" statement to determine the right procedure to call but this will not work with record types ( the parameters need to be defined as database object types).

Is there any way to circunvent this restriction in my particular case?

Case details:

The execute immediate call will use as in and out parameters variables of "response_rec" type.

The record type definitions are as folows:
TYPE button_rec IS RECORD(
label VARCHAR2(40) := NULL,
action VARCHAR2(1000) := NULL,
id VARCHAR2(40) := NULL,
visibility VARCHAR2(40) := NULL);
TYPE button_tab IS TABLE OF button_rec INDEX BY BINARY_INTEGER;

TYPE message_tab IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
TYPE error_tab IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;

TYPE response_rec IS RECORD(
module_naam VARCHAR2(120) := NULL,
memo BOOLEAN := FALSE,
session_id number := NULL,
buttons button_tab,
messages message_tab,
errors error_tab);

Thanks in advance for your feedback.

and Tom said...

You cannot, execute immediate doesn't understand record types at all - it is a SQL interface and SQL doesn't know anything about PLSQL defined/specific types.

You could consider using a global for this. You would push your local variable into the global, pass the global (which has a visible name) as a formal parameter, and then pop the return value back off into your local variable, like this:

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          type button_rec IS RECORD(
  4          label    VARCHAR2(40) := NULL,
  5          action  VARCHAR2(1000) := NULL,
  6          id      VARCHAR2(40) := NULL,
  7          visibility VARCHAR2(40) := NULL);
  8
  9          TYPE button_tab IS TABLE OF button_rec INDEX BY BINARY_INTEGER;
 10
 11
 12          g_temporary_global button_tab;
 13  end;
 14  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p( p_data in out my_pkg.button_tab )
  2  as
  3  begin
  4          for i in 1 .. p_data.count
  5          loop
  6                  p_data(i).label := upper(p_data(i).label);
  7          end loop;
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_data my_pkg.button_tab;
  3  begin
  4          for i in 1 .. 3
  5          loop
  6                  l_data(i).label := 'hello ' || i;
  7                  l_data(i).action := 'action ' || i;
  8                  l_data(i).id := i;
  9          end loop;
 10
 11
 12          my_pkg.g_temporary_global := l_data;
 13          execute immediate 'begin p(my_pkg.g_temporary_global); end;';
 14          l_data := my_pkg.g_temporary_global;
 15          my_pkg.g_temporary_global.delete;
 16
 17          for i in 1 .. l_data.count
 18          loop
 19                  dbms_output.put_line( l_data(i).label );
 20          end loop;
 21  end;
 22  /
HELLO 1
HELLO 2
HELLO 3

PL/SQL procedure successfully completed.

Not the prettiest - but it works. If you need to do this infrequently, this would suffice. If you need to do this millions of times the overhead of pushing and popping could get large.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Using an object type

Doh!

Thank you

More to Explore

PL/SQL demos

PL/SQL docs

How to use "Execute Immediate" with a record type instead of an Object type

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Using an object type

Doh!

Thank you

More to Explore

PL/SQL demos

PL/SQL docs