7 Replies Latest reply on Jul 7, 2016 6:01 PM by 1058268

    Function or procedure for select statement returning multiple values

    1058268

      Hi everyone,

       

      I want a little help to split the output records.

      please help me

       

      I am working on Oracle 11g DB and i am using plsql allround automation tool.

       

      I know this question might have been asked so many times by so many users. I just want to ask you again by showing an example

      Previously i created a small function (Private function) inside a package.

       

      Example:

       

      FUNCTION f_GetCompanyCode

      ( n_ContractId_in IN NUMBER)

      RETURN VARCHAR2

      IS

       

       

      ls_CompanyCode                    company.company_code%TYPE;

       

       

      BEGIN

       

       

         SELECT c.company_code INTO ls_CompanyCode

         FROM contract c

         WHERE c.contract_id=n_ContractId_in;

       

       

         RETURN ls_CompanyCode;

       

       

         --EXCEPTION HANDLER SECTION

         EXCEPTION

            WHEN NO_DATA_FOUND THEN

               --NO ITEMS FOUND

               RETURN ls_CompanyCode;

       

       

            WHEN OTHERS THEN

               --AN UNEXPECTED ERROR OCCURRED

               RETURN ls_CompanyCode;

       

       

      END;

       

       

      Now i have a select statement which is returning multiple values from 3-4 tables so for this kind what is the best way that i need to approach

        • 1. Re: Function or procedure for select statement returning multiple values
          John Thorton

          1058268 wrote:

           

          Hi everyone,

           

          I want a little help to split the output records.

          please help me

           

          I am working on Oracle 11g DB and i am using plsql allround automation tool.

           

          I know this question might have been asked so many times by so many users. I just want to ask you again by showing an example

          Previously i created a small function (Private function) inside a package.

           

          Example:

           

          FUNCTION f_GetCompanyCode

          ( n_ContractId_in IN NUMBER)

          RETURN VARCHAR2

          IS

           

           

          ls_CompanyCode                    company.company_code%TYPE;

           

           

          BEGIN

           

           

             SELECT c.company_code INTO ls_CompanyCode

             FROM contract c

             WHERE c.contract_id=n_ContractId_in;

           

           

             RETURN ls_CompanyCode;

           

           

             --EXCEPTION HANDLER SECTION

             EXCEPTION

                WHEN NO_DATA_FOUND THEN

                   --NO ITEMS FOUND

                   RETURN ls_CompanyCode;

           

           

                WHEN OTHERS THEN

                   --AN UNEXPECTED ERROR OCCURRED

                   RETURN ls_CompanyCode;

           

           

          END;

           

           

          Now i have a select statement which is returning multiple values from 3-4 tables so for this kind what is the best way that i need to approach

           

          Function is flawed.

          WHEN OTHERS is a bug  & should be removed.

          FUNCTION can only return single entity.

           

          which metric measures best?

          • 2. Re: Function or procedure for select statement returning multiple values
            1058268

            Thanks John for correcting that function.

            • 3. Re: Function or procedure for select statement returning multiple values
              Frank Kulash

              Hi,

              1058268 wrote:

               

              Hi everyone,

               

              I want a little help to split the output records.

              please help me

               

              I am working on Oracle 11g DB and i am using plsql allround automation tool.

               

              I know this question might have been asked so many times by so many users. I just want to ask you again by showing an example

              Previously i created a small function (Private function) inside a package.

               

              Example:

               

              FUNCTION f_GetCompanyCode

              ( n_ContractId_in IN NUMBER)

              RETURN VARCHAR2

              IS

               

               

              ls_CompanyCode                    company.company_code%TYPE;

               

               

              BEGIN

               

               

                 SELECT c.company_code INTO ls_CompanyCode

                 FROM contract c

                 WHERE c.contract_id=n_ContractId_in;

               

               

                 RETURN ls_CompanyCode;

               

               

                 --EXCEPTION HANDLER SECTION

                 EXCEPTION

                    WHEN NO_DATA_FOUND THEN

                       --NO ITEMS FOUND

                       RETURN ls_CompanyCode;

               

               

                    WHEN OTHERS THEN

                       --AN UNEXPECTED ERROR OCCURRED

                       RETURN ls_CompanyCode;

               

               

              END;

               

               

              Now i have a select statement which is returning multiple values from 3-4 tables so for this kind what is the best way that i need to approach

              That depends on what you want the function to do.  How would you like to use this function?

               

              As always, post CREATE TABLE and INSERT statements for a little sample data, the results you want from that data, and an explanation of how you get those results from that data.

              See the Forum FAQ: Re: 2. How do I ask a question on the forums?

              • 4. Re: Function or procedure for select statement returning multiple values
                Kalpataru

                1058268 wrote:

                 

                Now i have a select statement which is returning multiple values from 3-4 tables so for this kind what is the best way that i need to approach

                 

                Check this links may help you.

                can functions return multiple values?

                pipelined-table-functions

                https://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm

                PL/SQL Subprograms

                • 5. Re: Function or procedure for select statement returning multiple values
                  Jarkko Turpeinen

                  Hi,

                   

                  here's table function demo.

                   

                  create or replace
                  type poc_item force is object (
                  
                    group_id      number,
                    coverage_id   number,
                    covered_items number,
                    quantity      number 
                  
                  )
                  ;
                  /
                  create or replace
                  type poc_items is table of poc_item
                  ;
                  /
                  
                  
                  create or replace
                  function group_coverages(
                    contract_id number,
                    product_id  number
                  )
                  return poc_items pipelined
                  is
                  begin
                  
                    for r in (
                     
                      select
                        poc_item(1, 2, 3, 4) as item
                      from dual
                     
                    )
                    loop
                     
                      pipe row(r.item);
                  
                    end loop;
                     
                    return;
                  
                  end;
                  /
                  
                  
                  -- test
                  column group_id format 9
                  column coverage_id format 9
                  column covered_items format 9
                  column quantity format 9
                  
                  
                  -- middle tier makes this kind of query
                  -- passing parameters for table function
                  select * from table(
                  
                    group_coverages(
                  
                      contract_id => null,
                      product_id  => null
                  
                    )
                  
                  
                  )
                  ;
                  
                  
                  drop type poc_items
                  ;
                  drop type poc_item
                  ;
                  drop function group_coverages
                  ;
                  
                  
                  Type POC_ITEM compiled
                  Type POC_ITEMS compiled
                  Function GROUP_COVERAGES compiled
                  
                  GROUP_ID COVERAGE_ID COVERED_ITEMS QUANTITY
                  -------- ----------- ------------- --------
                         1           2             3        4
                  
                  Type POC_ITEMS dropped.
                  Type POC_ITEM dropped.
                  Function GROUP_COVERAGES dropped.
                  
                  
                  • 6. Re: Function or procedure for select statement returning multiple values
                    Jarkko Turpeinen

                    i have a select statement which is returning multiple values from 3-4 tables so for this kind what is the best way that i need to approach

                     

                    best way is the working and efficient way. How you know that? Usually needs few tests and proof of concepts to find out what matches your best requirements.

                     

                    Table function would be my choice to start. It is simple to make and use and has clear parameter passing. Parameter passing is good poc because query looked

                    complex and had unions in it (can't see query anymore). It also included order by, that you should let the middle tier decide and remove it from your function implementation.

                     


                    Obviously i assumed that query is already tuned and it can't be rewritten and served as a view to middle tier.

                    • 7. Re: Function or procedure for select statement returning multiple values
                      1058268

                      Hi

                      Thanks for replying back. Yes i agree query is returned in complex way. I am sorry i took out the query. If you want i can post it again