Return value from a function

I have written a function that returns the pipelined pkg
Now When I do an INSERT to TABLE 1 FROM TABLE O and one of the TABLE O column value being fetched from the function that returns the pipelined pkg  as below


CREATE OR REPLACE FUNCTION FNC_AGG_GET_BUCKET
    (
      pi_template_desc VARCHAR2,
      pi_as_of_date DATE,
      pi_cashflow_date DATE
      )
    RETURN PKG_PIPELINED_TENOR.piped_buckets PIPELINED

------

INSERT INTO TABLE1 (
column1,
column2,
column3,
column4) VALUES 
SELECT 
 O.COLUM1,
 O.COLUMN2,
[b] (SELECT A.COLUMN1 FROM TABLE(FNC_AGG_GET_BUCKET('4G',V_AS_OF_DATE,O.MATURITY_DATE))A),[/b]
O.COLUMN4
FROM TABLE O

Open in new window


Is there any better way of doing this ?
LVL 9
ronan_40060Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SujithConnect With a Mentor Data ArchitectCommented:
You may not need a pipelined function here. You are getting a single row out of the function, that may as well be achieved with a normal function.

And if the logic of the function is not complex, you may as well get rid of the function and
- Convert is as join or
- Write it as a scalar subquery

that will certainly perform better.
0
 
Wasim Akram ShaikConnect With a Mentor Commented:
First of all.. the insert statement is not valid.. you cannot have the values clause when you are using select statement..!!


I think you have just pasted the definition of the function..!!

is column3 data type the same from the output of pipelined function???

if its same then its ok??


well, i don't see anything wrong in the approach .?? you can go ahead and use this

do you have any errors as such?, if yes then let us know..!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.