| PIPE ROW [message #686344] |
Thu, 04 August 2022 07:15  |
Duane
Messages: 519
Registered: December 2002
|
Senior Member |
|
|
I'm pretty sure I know the answer but I thought I would ask anyway just to confirm.
Using a function that is PIPELINED (with PIPE ROW) doesn't have anything to do with using DBMS_PIPE? I only ask because I have a function that is PIPELINED and our DBA was questioning if it used the older DBMS_PIPE functionality. Everything is moving to Oracle Advanced Queuing so that's why he asked. If, by chance it does, then he doesn't want me to use the PIPLINED function.
function UnZipFile (DirectoryName varchar2,
ZipFileName varchar2) return UnZipFileNameList pipelined is
ZipFile UnZipFileNameList;
begin
ZipFile := UnZipGetFileList(DirectoryName, ZipFileName);
for i in ZipFile.first..ZipFile.last
loop
pipe row(ZipFile(i));
end loop;
return;
exception
when others
then
raise;
end;
|
|
|
|
|
|
| Re: PIPE ROW [message #686383 is a reply to message #686345] |
Tue, 23 August 2022 08:43  |
Duane
Messages: 519
Registered: December 2002
|
Senior Member |
|
|
By chance, do you know what resource owns PIPE, PIPE ROW or PIPELINED? I ask because we assign Roles to everything. So, I grant execute on the function to Role XYZ. If I just create a Table Function and grant the role then the resource can return the result set. If I create a Table Function that is PIPELINED and grant the role to that then the resource can't return the result set.
It would seem that I need to grant PIPE, PIPE ROW or PIPELINED to Role XYZ but I don't know what that is. Anyone know what GRANT on WHAT I need to issue? Maybe sys.PIPE?
grant execute on PIPE to XYZ;
|
|
|
|
|
|
| Re: PIPE ROW [message #686386 is a reply to message #686385] |
Tue, 23 August 2022 10:07 |
Duane
Messages: 519
Registered: December 2002
|
Senior Member |
|
|
Thank you.
That is very odd then. Same grants for both table functions. One doesn't use PIPELINED and one does. The one without the resource can read. The one with PIPELINED the resource can't read. Strange. I'll go through it again to ensure the grants are the same. I've already done that but I'll do it again.
|
|
|
|
|
|
| Re: PIPE ROW [message #686388 is a reply to message #686344] |
Tue, 23 August 2022 11:42 |
Duane
Messages: 519
Registered: December 2002
|
Senior Member |
|
|
|
You already answered by question so I must have missed a grant. I can't confirm because the resource that uses the Table Function is down. The system was upgraded and that broke the log in. They are working to get it up.
|
|
|
|
| Re: PIPE ROW [message #686404 is a reply to message #686388] |
Sat, 27 August 2022 05:22 |
Solomon Yakobson
Messages: 3213
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Question is why do you need this function to begin with? It is just a wrapper over UnZipGetFileList(DirectoryName,ZipFileName) and will work by a fraction slower. I'd get rid of it and simply use
select *
from UnZipGetFileList(DirectoryName,ZipFileName)
/
select *
from table(UnZipGetFileList(DirectoryName,ZipFileName))
/
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
