Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Return value from a function

Posted on 2012-03-29
2
Medium Priority
?
559 Views
Last Modified: 2012-04-11
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 ?
0
Comment
Question by:ronan_40060
2 Comments
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 600 total points
ID: 37781050
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 1400 total points
ID: 37782820
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question