Stack Overflow
Stack Overflow

Questions tagged [pipelined-function]

Use the result of a PL/SQL routine as if it were a table.

A pipelined function allows you to use a PL/SQL routine as if it is the source table for another operation like this:

SELECT * FROM TABLE ( schema_name.function_name([any parameters]));

This concept is known as in other relational database systems.

58 questions
0
votes
1 answer

Nested PIPELINED function

create type data_type_1 as object (x number, y number) / create type table_type_1 as table of data_type_1 / create or replace package xyz AS function main_xyz return table_type_1 pipelined; function sub_func return table_type_1…
user256378
  • 35
  • 9
0
votes
0 answers

How can one use a local pipelined function returning a local type in Oracle PL/SQL?

After creation of a local pipelined PL/SQL function returning locally defined type I found no way to use it. Is there one? By being local I mean the function and the type is visible only within some other PL/SQL block and therefore cannot be used in…
Alexey Bashtanov
  • 1,274
  • 10
  • 7
0
votes
1 answer

Is it possible to use "SELECT * FROM MyFunction()" syntax in Oracle for Pipelined Functions?

Is it possible to call an Oracle Pipelined Function without specifying TABLE( ) operator, like in the following example? SELECT * FROM MyFunction() I don't want to use the following, due to compatibility with SqlServer. SELECT * FROM TABLE(…
user1389591
0
votes
2 answers

how to use oracle package to get rid of Global Temp table

I have a sample query like below: INSERT INTO my_gtt_1 (fname, lname) (select fname, lname from users) In my effort to getting rid of temporary tables I created a package: create or replace package fname_lname AS Type fname_lname_rec_type is…
john
  • 493
  • 1
  • 8
  • 12
0
votes
0 answers

Piepelined function is not called after few procedure calls

I have a procedure that calls a function. After few procedure calls it is not executing that function anymore. Also there are no exceptions thrown. When I deleted the session_id from browser and refreshed it, everything starts to work as expected…
Mr. Blond
  • 1,113
  • 2
  • 18
  • 41
0
votes
0 answers

Oracle Pipelined Function with Joins

Here is the creation of my two types needed for the Pipelined function to work: create or replace type RIS_CPU as object ( NAME VARCHAR2(255 BYTE), COMPUTER_NAME VARCHAR2(255 BYTE), PERCENT_PROCESSOR_TIME NUMBER, REGION VARCHAR2(255…
John
  • 214
  • 3
  • 14
0
votes
1 answer

Fine tuning oracle query with pipelined function

I have a query (that powers an Oracle Application Express Report) that I was told by our users was executing "slowly" or at an unacceptable speed (wasn't given an actual load time for the page and the query is the only thing on the page). The query…
user3249281
  • 505
  • 2
  • 6
  • 20
0
votes
2 answers

PL/SQL - use same pipelined function twice in the same query

I'm trying to use a pipelined function to save on time and reduce redundancy in my queries. The function in question returns data from a reference table based on some input. Records in the main data table I am selecting from have multiple columns…
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
0
votes
0 answers

writing generic pipelined table PL/SQL function with sys_refcursor as in parameter

I have a function returning an open SYS_REFCURSOR. The function builds and executes a few different SQL queries and returns that cursor. I am using that cursor in PHP to fetch the results, but the performance is not to good, as the cursor data…
SWilk
  • 3,261
  • 8
  • 30
  • 51
0
votes
2 answers

How to change PL/SQL function call when function is no longer pipelined?

I have PL/SQL function looking like: FUNCTION get_agent_statistics ( id NUMBER RETURN agent_stats_t PIPELINED; And I select from it (iBatis code): SELECT * FROM table(pkg.get_agent_statistics(#id#)) How should I change this…
alexey komov
  • 11
  • 3
0
votes
1 answer

Viewing results of a pipelined function in SQL*Plus or Oracle SQL Developer

How can I view the results returned by a pipelined function in Oracle SQL Developer ? I'm invoking the function using a simple select..from dual like select piaa_extract.FN_PIAA_EXTRACT('01-JAN-00','01-JAN-12') FROM DUAL and the result I get…
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
-2
votes
1 answer

What could cause ORA-00910: specified length too long for its datatype?

While running rdsadmin.rds_file_util.read_text_file, I got error "ORA-00910: specified length too long for its datatype". After extensive investigation, I found out the cause. The system is Oracle Database 19c. The data type is VARCHAR2. Parameter…
Brian Fitzgerald
  • 634
  • 7
  • 14
-2
votes
2 answers

parallel pipelined table function in database

I want to ask whether this parallel pipelined table function feature is present in postgres also or not. I am sure , pipelined table function is present in postgres, but whether they exhibit parallelism or not i dont know. Can somebody help me out…
Abhishek Sagar
  • 1,189
  • 4
  • 20
  • 44
1 2 3
4