Direct mode and oracle "table type" return parameters

[upscene]

Hi,

I'm trying to use the following routine:

Code: Select all

DBMS_DEBUG.PRINT_BACKTRACE (backtrace OUT backtrace_table);

How can I use this in an anonymous PL/SQL block in Direct mode?

I was trying:

Code: Select all

declare
  backtrace dbms_debug.backtrace_table;
  n binary_integer;
begin
  dbms_debug.print_backtrace(backtrace);
  :stacktrace := dbms_debug.backtrace_table();
  :stacktrace.extend(backtrace.count);
  for n in backtrace.first .. backtrace.last loop
    :stacktrace(n) := backtrace(n);
  end loop;
end;

I set parameter "stacktrace" to be of type ftTable in Delphi, but when executing this, I get:
"type of object must be defined"

How can I get this output parameter value into Delphi?

Thanks.


Martijn Tonies
Upscene Productions

[Plash]

ODAC does not support RECORD type in parameters. So you should return each element of the PROGRAM_INFO record into separate table parameter. For example:

Assign the following to the SQL property:

Code: Select all

declare 
  backtrace dbms_debug.backtrace_table; 
  n binary_integer; 
begin
  dbms_debug.print_backtrace(backtrace);
  :count := backtrace.count; 
  for n in backtrace.first .. backtrace.last loop 
    :name(n) := backtrace(n).name;
    :owner(n) := backtrace(n).owner;
    :line(n) := backtrace(n).line#;
  end loop; 
end;

Setup parameters as the following:

Code: Select all

with OraQuery.ParamByName('COUNT') do begin
  DataType := ftInteger;
  ParamType := ptOutput;
end;
with OraQuery.ParamByName('NAME') do begin
  DataType := ftString;
  ParamType := ptOutput;
  Table := True;
  Length := 100; // number of elements
end;
with OraQuery.ParamByName('OWNER') do begin
  DataType := ftString;
  ParamType := ptOutput;
  Table := True;
  Length := 100;
end;
with OraQuery.ParamByName('LINE') do begin
  DataType := ftInteger;
  ParamType := ptOutput;
  Table := True;
  Length := 100;
end;

Table parameters have fixed length. You can get the real number of elements returned by the PRINT_BACKTRACE procedure from the COUNT parameter.

[upscene]

Thanks, this works great!

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com