Test Script for PipeLine functions

[Buzz_ss]

Hi.

When I create Test Script for pipeline function using popup menu
I get code like as :

declare
  -- Non-scalar parameters require additional processing
  result MyPackage.TMyPipeType;
begin
  -- Call the function
  result := MyPackage.MyPipeFunc;
end;

I would prefer to get code like as

select * from table(MyPackage.MyPipeFunc)

Is it possible?

Thanks

 

[Jens Naumann]

Hi, just insert the select in a for ... loop like this
begin
for r in (select * from table(MyPackage.MyPipeFunc)) loop
dbms_output.put_line(r.?);
end loop;
end;

 

[Buzz_ss]

Hi.

I talk about autogenerated code! I want the Test menu to make code like as

select * from table(MyPackage.MyPipeFunc)

Is it possible?

Thanks

 

[Jens Naumann]

I don't think so. Because test contains always a plsql-block.
Regards

 

[Buzz_ss]

I don't think so. Because test contains always a plsql-block.
Regards

Why do You think so? Try write

select * from dual

in Test window and press execute button. You will get correct result in page 'SQL Output'.

 

[Jens Naumann]

I don't know what kind of test window you mean. I do it like this
- file/new/test window
- for me it's created with an template plsql-block
- delete all, write "select * from dual", execute => ORA-00933 or
- wite "select * from dual" into the plsql-block, execute => ORA-06550, PLS-00428

 

[Buzz_ss]

It is very strange

- file/new/test window
- for me it's created with an template plsql-block
- delete all, write "select * from dual", execute

All works correctly. I see new page "Sql output" and result in it.

I very often use such operations .

How do You test queries which have parameters?
As example

select * from dual
where 1 =:a

this is impossible in SQL window. You can use for it Test Window only!

Ok. Try

1) to write in SQL window "select * from dual"
2) Execute.
3) If preview step worked correctly, do right click.
4) PopUp menu :"Change window to Test window"
5) Execute

 

[mike]

I don't see that it is possible. I use templates to add to the test script that is generated or I manually change them, because I don't know of a way to modify how they are generated.

Another thought is to save a test script, open it and modify it as needed if most of it stays the same.

 

[Maxmix]

The request is on the famous list, for too long:https://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=44758#Post44758

I am in wait, too.

 

[Buzz_ss]

I am in wait, too.

I see You waited long time...

 

[William Robertson]

Even if a Test Window has to be a PL/SQL block, the generator could still create something like

begin
    open :results for
        select * from table(MyPackage.MyPipeFunc);
end;

with :results provided as a cursor variable.

 

[Buzz_ss]

Even if a Test Window has to be a PL/SQL block, the generator could still create something like

begin
    open :results for
        select * from table(MyPackage.MyPipeFunc);
end;

with :results provided as a cursor variable.

PL/SQL block is not necessary for test window.

select * from table(MyPackage.MyPipeFunc)

works fine in Test window too.

 

[Stew Stryker]

Buzz ss,

Thanks for the tip about adding

select * from table(MyPackage.MyPipeFunc)

In all my years of using PL/SQL Developer, I had never noticed that the Test Window contained a SQL Output tab! (I can be kinda slow that way!)

So I always wrote convoluted ways to display the results of a pipeline function in a Test Window.

Thank you very much!

- Stew

 

[Stew Stryker]

Jens,

Is there a chance that your "SELECT * FROM dual" had a ; at the end?

When I add that, I get the ORA-00933 error you reported below.

I find that it works fine (w/o the semi-colon) in PL/SQL Developer 12.0.7.

Happy trails,

Stew

 

[Jens Naumann]

Hi Stew,

without a plsql-block You can execute a sql-statement without an ending ;, that's true. But for me this test maskes no sense because it's just like a sql-window. I use test windows only for debugging of database packages or triggers and they contains always a plsql-block.

Regards

 

[Buzz_ss]

Hi Jens

You can't use bind variables in Sql-window . It may be important.

Regards