Toad Editor - Output Table Collection

[keith.poulson]

I am sure there is a way and I am just not doing something correctly. I have a procedure that we send to the application as a table type and not a ref cursor I want to see the output of the collection when I run the procedure but toad is telling me its an unsupported data type. I am sure there is some way I can link the output to the collection so it can show me the output somehow but its not clicking with me.

How to see object type output in TOAD

Hi, I have written a procedure and one of the parameter returns a OBJECT, sample below. Sample Procedure: CREATE OR REPLACE PROCEDURE TEST ( IN_PYMNT_ID IN TEST_TABLE.PAYMENT_ID%TYPE, OUT_HEADER_DATA OUT TY_PAYMENT_DETAILS_OBJ_TBL,… Sample Object: CREATE OR REPLACE TYPE ty_payment_details_obj AS OBJECT (FIELD_NAME VARCHAR2(300) , FIELD_VALUE VARCHAR2(300) ); / Sample Table: CREATE OR REPLACE TYPE TY_PAYMENT_DETAILS_OBJ_TBL IS TABLE OF ty_payment_details_obj / How to execute the p…

I saw this post from 2015 which looked like what I needed but it didn't work (expression is of wrong type when I did the :data_out call

Not sure if this was still the only way.

[JohnDorlon]

Did you try this option when running the procedure?

If so, and you still got an error, can you provide some sample code so that we can reproduce the problem?

[keith.poulson]

I did but since it was a custom collection on the output its like the output window didn't know how to define it.

In the package spec we defined the collection as:
TYPE t_mo_error_rec IS RECORD
(
dsign_id VARCHAR2(2),
srch_val NUMBER,
user_message VARCHAR2(200),
ERROR_CODE NUMBER,
error_message VARCHAR2(250)
);

TYPE t_mo_error_tab IS TABLE OF t_mo_error_rec;

Then on the procedure output we output the table.

PROCEDURE chk_errors(p_dsign_id      IN     VARCHAR2,
                     p_srch_val      IN     NUMBER,
                     p_error_tab            OUT t_mo_error_tab);

This works and the application that calls the procedure can read the table but I was trying to troubleshoot the procedure and I couldn't easily see my own output so that is where the question comes from.

[JohnDorlon]

Starting from your code (I changed RECORD to OBJECT and filled in enough to make it run) like this, I created the types and procedure.

drop type t_mo_error_tab;
drop type t_mo_error_rec;

create or replace type t_mo_error_rec as object
(
dsign_id VARCHAR2(2),
srch_val NUMBER,
user_message VARCHAR2(200),
ERROR_CODE NUMBER,
error_message VARCHAR2(250)
);

create or replace tYPE t_mo_error_tab IS TABLE OF t_mo_error_rec;

create or replace
PROCEDURE chk_errors(p_dsign_id      IN     VARCHAR2,
                     p_srch_val      IN     NUMBER,
                     p_error_tab            OUT t_mo_error_tab) as
BEGIN
  p_error_tab := t_mo_error_tab();
  p_error_tab.EXTEND(2);
  p_error_tab(1) := t_mo_error_rec('a', 1, 'hello', 1, 'hello');
  p_error_tab(2) := t_mo_error_rec('b', 2, 'hi', 2, 'hi');
END;
/

And then when I run the procedure from Schema Browser, I get the unsupported datatype message in the output.

So, trying Michael's suggestion from the other post, I then did put this in the Editor and ran it with F9:

declare
  data t_mo_error_tab;
  cur sys_refcursor;
begin
  chk_errors('a', '1', data);
  open cur for select * from table(data);
  :data_out := cur;
end;

The variables dialog appeared and I set it like this:

then output appeared in the data grid:

[keith.poulson]

Ok I will go back and try again. I got an error when I did the :data_out so given the age of the article I wasn't sure that was still correct.