Home » SQL & PL/SQL » SQL & PL/SQL » PIPE ROW (Oracle, 12.2, Unix)
PIPE ROW [message #686344] Thu, 04 August 2022 07:15 Go to next message
Duane
Messages: 557
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 #686345 is a reply to message #686344] Thu, 04 August 2022 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No relation between the two.

And that:
  exception
    when others
      then
        raise;
  end;
I think it should be changed to:
  exception
    when others
      then
        begin raise;
    exception
    when others
      then
        begin raise;
    exception
    when others
      then
        begin raise;
    exception
    when others
      then
        raise;
  end; 
  end;
  end;
  end;
Read WHEN OTHERS.
Re: PIPE ROW [message #686383 is a reply to message #686345] Tue, 23 August 2022 08:43 Go to previous messageGo to next message
Duane
Messages: 557
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 #686385 is a reply to message #686383] Tue, 23 August 2022 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PIPE, PIPE ROW and PIPELINED are part of the PL/SQL language syntax like BEGIN, END or EXCEPTION.

Read this overview: Get Started with Table Functions.

Re: PIPE ROW [message #686386 is a reply to message #686385] Tue, 23 August 2022 10:07 Go to previous messageGo to next message
Duane
Messages: 557
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 #686387 is a reply to message #686386] Tue, 23 August 2022 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Without a test case we can't say what is wrong.

Re: PIPE ROW [message #686388 is a reply to message #686344] Tue, 23 August 2022 11:42 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous message
Solomon Yakobson
Messages: 3269
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)
/
or, if you are on lower version:

select  *
  from  table(UnZipGetFileList(DirectoryName,ZipFileName))
/
SY.
Previous Topic: Retrieve last month running total when current month is null
Next Topic: Where is the session timeout value for the duration of running an sql query/procedure
Goto Forum:
  


Current Time: Thu Mar 28 18:55:08 CDT 2024