Debugging a function with table type as input parameter

Question

Can anybody let me know how can i debug a functions with table type as input parameter and this function returns a table type pipelined.

Please see below details.When i try to test the function it creates below anonymous block but when i click on debug button it gives error:

Anonymous block:

declare
  -- Non-scalar parameters require additional processing 
  result t_bmk_q;
  pit_srch_str t_parm;
begin
  -- Call the function
  result := f_bmk_srch(pit_srch_str => pit_srch_str,
                               piv_op => 'ALL');
end;


---f_bmk_q  function returns table type t_bmk_q   pipelined


defintions:
==============
t_bmk_q  --->table type

t_bmk_q is TABLE OF r_bmk_q -->object of some attributes.


pit_srch_str ---> is parameter of type  t_parm which is table type  of r_parm


--plz see def of r_parm:

CREATE OR REPLACE TYPE r_parm AS OBJECT
    (
        p_abc                 varchar2(200)
                ,p_new_val               varchar2(2000)
                ,CONSTRUCTOR FUNCTION r_parm
                  (
                   p_abc                 varchar2
                   ,p_new_val                varchar2
                  ) RETURN SELF AS RESULT
        );

Example:I have below sample values to test and debug:
r_parm('TAB1.VALUE','123321123')

Thanks Rajesh

And... what error did you get? And did you notice the 'require additional processing' part? — Alex Poole
– Alex Poole, Commented Aug 30, 2014 at 22:42
Yes i noticed but i am not sure what additional things i need to add for multi dimensional table type object input parameter. — user2090701
– user2090701, Commented Aug 31, 2014 at 2:03

Luke Woodward · Accepted Answer · 2014-08-31 11:46:28Z

It seems you are using a PL/SQL Developer Test Window to run the tests. I recognise the comment Non-scalar parameters require additional processing.

PL/SQL Developer's Test Window doesn't handle pipelined functions too well. You are best off removing the result variable and wrapping the function call in open :cursor for select * from table(...). Add a variable named cursor of type Cursor to the variables list below the window.

To populate the input table, you can simply 'call' the table type, passing each row in the table as a separate argument. For example,

t_parm(r_parm(...), r_parm(...), r_parm(...))

You can add as many rows to the table as you like this way.

Putting both of these changes together, we have something like the following:

declare
  pit_srch_str t_parm;
begin
  -- Create input table.    
  pit_srch_str := t_parm(
            r_parm('TAB1.VALUE','123321123'),
            r_parm('TAB2.VALUE','456597646')
  );

  -- Call the function
  open :cursor for select * from table(f_bmk_srch(pit_srch_str => pit_srch_str,
                               piv_op => 'ALL'));
end;
/

Once you've run the function, you can get the results from the cursor variable (use the ... button at the far right).

Collectives™ on Stack Overflow

Debugging a function with table type as input parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related