Page 1 of 1
Direct mode and oracle "table type" return parameters
Posted: Tue 27 Jan 2009 14:12
by 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
Posted: Wed 28 Jan 2009 09:37
by 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.
Posted: Thu 29 Jan 2009 10:22
by 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