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
2
votes
3 answers

PL/SQL - execute immediate in pipelined function

I want to execute dynamic query in my pipelined function and return results of this query. Is it possible to do this? Pipelined function is convenient for me to achieve good interface for my application cause it behaves like a table. The…
dzb
  • 61
  • 1
  • 1
  • 7
2
votes
2 answers

Pipelining to find an element at an index

I would like to do the following in F#: let index = 5 let sequence = [0..10] let fifthElement = sequence |> .[index] However, the last line is invalid. What I'd like to do is to actually retrieve the element at the index of 5 in the…
matt
  • 2,857
  • 7
  • 33
  • 58
1
vote
1 answer

ORACLE expression is of wrong type on pipelined function (multiple joins table)

I'm writting a Oracle Package with a pipelined function to get multiple records, the query is about multiple joined tables, which that will save into another table, I created a TYPE RECORD and the TABLE TYPE for the type record, then I created the…
Cesar Vega
  • 13
  • 3
1
vote
1 answer

Oracle pipelined function with cache

How can use oracle pipelined function on query to fetch data only first time. example: create or replace function best_employees return my_type pipelined; select * from employees a join table(best_employees) b on a.employee_id =…
Ayubxon Ubaydullayev
  • 339
  • 2
  • 16
1
vote
1 answer

No data found When Piping Row

I have a function that returns a list of records, and then im looping over the list and piping them, however during piping I am getting ORA-01403: no data found error. Below is the code I am using, and I am getting this error on some rows, not all…
hakuna matata
  • 3,243
  • 13
  • 56
  • 93
1
vote
1 answer

Oracle: Return dynamic result set from sys_refcursor

I need a pipelined functions that can return a table from a sys_refcursor in with the sys_refcursor parameter is unknown at compiled time. for example: select * from table(pipeline_func(cursor(select 1 col_1, 2 col_2 from dua))) or select *…
Huu Vinh Nguyen
  • 81
  • 1
  • 1
  • 6
1
vote
0 answers

Oracle 9i - Version 9.2.0.1.0 - Windows 2003. Pipelined function

Thank you for reading and willingness to help me. Am basically a java developer and less knowledge on oracle analytics side. There exists a materialized view that generates daily holdings of customer units which as days goes by slowed down a lot…
Vel
  • 11
  • 2
1
vote
1 answer

Performance is poor of pipelined function in SQL Developer

I have a pipelined table function like FUNCTION FOO ( ) RETURN T_TAB PIPELINED AS BEGIN FOR rec IN () LOOP PIPE row(T_WF()); END LOOP; RETURN; END FOO; I…
comphilip
  • 500
  • 5
  • 15
1
vote
1 answer

Oracle PL/SQL array input into parameter of pipelined function

I am new to PL/SQL. I have created a pipelined function inside a package which takes as its parameter input an array of numbers (nested table). But I am having trouble trying to run it via an sql query. Please see below my input array CREATE OR…
user1667385
1
vote
1 answer

Oracle: Translate subselect into pipelined function?

How do I generically translate a subselect into a pipelined function call? For example how would I translate this: select id, stuff from t1 where id in (select unique id from kw where k = 'foo') to this: select id, stuff from t1 where id in (select…
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
1
vote
1 answer

Execute Pipelined Functions in ODP.NET

I want to select data from a pipelined function in C# "just in time". That means the function pipes a row every second (like a status report) an I would like to fetch the data in C# immediately. So far I have the following: …
user3270991
  • 43
  • 4
1
vote
1 answer

What is Better for Mimicking PL/SQL Returning SQL in Interactive Reports: Collection or Pipelined-Function

The worst aspect of the Interactive Report (IR) is that you cannot create it using a PL/SQL returning SQL statement. I have gotten around this using two methods: 1) APEX_COLLECTION.CREATE_COLLECTION in the Before Header Process, which takes a SQL…
Matthew Moisen
  • 16,701
  • 27
  • 128
  • 231
1
vote
1 answer

table function as physical table in Oracle BI EE

how do I import oracle table function as physical table in Oracle BI EE ? Let's say it is select * from scheme.table_function1(adate) What is correct syntaxis for setting it as BI physical table? How do I specify input parameter (adate)?
Marchello
  • 65
  • 1
  • 3
  • 11
0
votes
0 answers

ORA-22905: cannot access rows from a non-nested table item

CREATE OR REPLACE TYPE myObjectFormat AS OBJECT ( A VARCHAR2(200), B INTEGER, C INTEGER ) / CREATE OR REPLACE TYPE myTableType AS TABLE OF myObjectFormat ; / CREATE OR REPLACE PACKAGE demo4 AS FUNCTION f1(p_abc_tab IN myTableType)…
gaurav
  • 345
  • 2
  • 9
  • 20
0
votes
1 answer

Oracle function where clause not considering all filtering

First of all, I usually try to replicate my problem in some dummy data to make it easier to ask here but this at time it won't be possible. So please forgive me for this. So. My problem is the following. I have a Oracle Database and on it I have…
Dariva
  • 330
  • 2
  • 13
1
2
3 4