This discussion is archived
5 Replies Latest reply: Oct 12, 2012 10:46 AM by Solomon Yakobson RSS

using pipelined function

701800 Newbie
Currently Being Moderated
Hello,

I have defined a VC_ARRAY_1 as
create or replace TYPE  "VC_ARRAY_1" as table of varchar2(4000)
and working as the test indicates below:
select * from table(VC_ARRAY_1('qqqq', pppp'))

COLUMN_VALUE
qqqq
pppp
But, when I use the function as
select * from table (vc_array_1(select personkey from tbl_persons))
I am getting an error ORA_00936 missing expression (the column personkey is a vrachar2(4000)). What's wrong with the second usage?

Thanks,
Rose
  • 1. Re: using pipelined function
    rp0428 Guru
    Currently Being Moderated
    >
    I am getting an error ORA_00936 missing expression (the column personkey is a vrachar2(4000)). What's wrong with the second usage?
    >
    Since personkey is a column in a table why don't you explain why you want to use such a convoluted query whey you could just query personkey from the table.
  • 2. Re: using pipelined function
    Solomon Yakobson Guru
    Currently Being Moderated
    Rose Elle wrote:
    What's wrong with the second usage?
    Everything. You need to cast query reults into collection type:
    select  *
      from  table(
                  cast(
                       multiset(
                                select  personkey
                                  from  tbl_persons
                               )
                       as vc_array_1
                      )
                 )
    /
    SY.
    P.S. And where is pipelined function in all of this?
  • 3. Re: using pipelined function
    701800 Newbie
    Currently Being Moderated
    OK, here are the details:

    Using Application Express (4.1), Oracle Database 11g and BI Publisher to generate a PDF report.

    In the database, I have the following Types and Function defined:
    create or replace TYPE  "VC_ARRAY_1"   as table of varchar2(4000)
    create or replace FUNCTION   "GET_ARRAY" ( p_array  IN   varchar2,  p_delimiter   IN   varchar2  default ':')  return  VC_ARRAY_1 PIPELINED
    
    create or replace TYPE   "AFFECTED_INDIVIDUAL"  as object("PERSONKEY" VARCHAR2(4000),  "FIRST_NAME" VARCHAR2(4000), "LAST_NAME" VARCHAR2(4000)… more variables)
    create or replace TYPE  "AFF_IND_TAB"  as table of  "AFFECTED_INDIVIDUAL"
    create or replace FUNCTION  GET_AFF_INDIVIDUALS(personKeys IN VC_ARRAY_1) return  AFF_IND_TAB PIPELINED
    The function GET_AFF_INDIVIDUALS uses several tables and returns pipelined table.


    In Apex, I have the following SQL query that feeds the Report query.
    select *  from table (get_aff_individuals(get_array(:F_SELECTED_PERSONS, ',')))
    Here, F_SELECTED_PERSONS is a varchar2 that stores the comma seprated person keys. This works as long as the F_SELECTED_PERSONS size is less than 32767 bytes. But, in Apex we can use collections to store the person keys without this limitation and I can't pass the Apex collection to the db function. But it can be used as a view in an query. So, I am trying to replace the above query with somethin like this:
    select *  from table (get_aff_individuals(table(vc_array_1(select c001 from APEX_collections where collection_name='SELECTED_PERSONS'))))
    Here, I am getting the ORA-00936 error. I have also posted in Apex forum regrading this [url https://forums.oracle.com/forums/thread.jspa?messageID=10630106&#10630106]How to convert Collection to user defined type in db


    Thanks for your help!
    Rose
  • 4. Re: using pipelined function
    Solomon Yakobson Guru
    Currently Being Moderated
    Rose Elle wrote:
    So, I am trying to replace the above query with somethin like this
    select  *
      from  table(
                  get_aff_individuals(
                                      cast(
                                           multiset(
                                                    select  c001
                                                      from  APEX_collections
                                                      where collection_name='SELECTED_PERSONS'
                                                   )
                                           as vc_array_1
                                          )
                                     )
                 )
    /
    SY.

    Edited by: Solomon Yakobson on Oct 12, 2012 1:42 PM
  • 5. Re: using pipelined function
    701800 Newbie
    Currently Being Moderated
    Thank you SY, it worked perfectly! I didn't know how to convert the query into collection type.

    Here is the modified query I am using:

    select * from table (get_aff_individuals(cast(multiset(select c001 from APEX_collections where collection_name='SELECTED_PERSONS') as vc_array_1)))


    Rose

    Edited by: Rose Elle on Oct 12, 2012 10:43 AM

    Edited by: Rose Elle on Oct 12, 2012 11:06 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points