Solved

Return value from a function

Posted on 2012-03-29
2
541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 150 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 350 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 79
create a nested synonym 4 53
oracle spooling query into csv eliminating new line character 9 51
Oracle cluster . 1 22
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

736 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