need help on pipeline function

Hi ,

I have created the below package which contains the pipeline funcition . Iam getting an error organization_id not found , even though it is declared in the code. Kindly help me to compile this package.

create or replace
Package sam_test As

type t_orgdata is record(
org_id number,
org_name varchar2(50)
);


type t_t_orgdata is table of t_orgdata ;

function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined ;

 End sam_test;


create or replace
package body sam_test
as
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined  
is
v_orgdata t_orgdata;
v_organization_id number:=0;
v_cnt number:=1;
begin
v_orgdata := t_t_orgdata();
loop
    fetch p_cur into v_organization_id ;
    exit when p_cur%NOTFOUND;
        v_orgdata.extend;
        v_orgdata.org_id :=  v_organization_id;      
        v_orgdata.org_name:='sam';
        pipe row (v_orgdata);
        v_cnt:=v_cnt+1;
end loop;
close p_cur;
return;
end get_orgdets;

end sam_test;

I get an error expression is of wrong type at line v_orgdata := t_t_orgdata(); Any help in this regard is really appreciated
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
I get an error expression is of wrong type at line v_orgdata := t_t_orgdata();
v_orgdata is single record
t_t_orgdata() is table pf records
:p
0
slightwv (䄆 Netminder) Commented:
You declare:
v_orgdata t_orgdata;

So this cannot happen:
 v_orgdata := t_t_orgdata();


The v_orgdata record cannot be set to a TABLE.
0
slightwv (䄆 Netminder) Commented:
Also, why populate a TABLE OF records to just PIPE a row back?

Can you clarify what problem you are trying to solve with this approach?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Another problem you are likely to run into:
I believe you can only use pipelined functions in straight SQL not PL/SQL.

Singe the pipelined function returns a 'type', the type needs to be created outside the package so the SQL engine can see it.
0
slightwv (䄆 Netminder) Commented:
Here is a pipelined function example I had lying around from a previous question here on EE.

Maybe it will help you with what you are trying to do.

If allows you to pipe data from different tables just by changing a function parameter value.

drop table tab1;
create table tab1(col1 varchar2(20), col2 varchar2(20));

drop table tab2;
create table tab2(col1 varchar2(20), col2 varchar2(20));

insert into tab1 values('From tab1','Hello');
insert into tab2 values('From tab2','Goodbye');
commit;

drop type some_list;
drop type my_t;

create type my_t as object
(
	col1 varchar2(20),
	col2 varchar2(20)
)
/

show errors

create type some_list as table of my_t
/

show errors


create or replace function some_func(condition char) return some_list pipelined is
  	out_rec my_t := my_t(NULL,NULL);
begin
		case condition
			when '1' then
				for c1 in ( select col1, col2 from tab1 ) loop
					out_rec.col1 := c1.col1;
					out_rec.col2 := c1.col2;
					pipe row (out_rec); 
				end loop;
			when '2' then
				for c1 in ( select col1, col2 from tab2 ) loop
					out_rec.col1 := c1.col1;
					out_rec.col2 := c1.col2;
					pipe row (out_rec); 
				end loop;

		end case;


	return;
end;
/

show errors

select * from table(some_func('1'));
select * from table(some_func('2'));

Open in new window

0
sam_2012Author Commented:
Hi Below is the actual code iam trying to achieve . I will pass an organization_id as an input parameter  which would be an cursor. for this cursor , i will pull out all the customer
details and send it back. I am still geting an error

create or replace
Package sam_test As
type t_orgdata is record(
CRM_ORGANIZATION_ID NUMBER,
ORGANIZATION_ID NUMBER,
PARENT_ID NUMBER,
DEAL_GROUP_ID NUMBER,
IS_INTERNAL CHAR(1CHAR),
OWNER_USER_ID NUMBER,
STATUS_ID NUMBER,
ORGANIZATION_NAME VARCHAR2(50CHAR),
DOMAIN VARCHAR2(4000CHAR),
LOCK_VERSION NUMBER,
NAME_EN VARCHAR2(50CHAR),
TAX_ID VARCHAR2(50CHAR),
ADDRESS1 VARCHAR2(50CHAR),
ADDRESS2 VARCHAR2(50CHAR),
STATE VARCHAR2(50CHAR),
CITY VARCHAR2(50CHAR),
ZIP VARCHAR2(50CHAR),
SUMMARY VARCHAR2(4000CHAR),
COUNTRY_ID NUMBER,
REGION_ID NUMBER,
VISIBILITY_TYPE NUMBER,
VISIBILITY_RESOURCE_ID NUMBER,
IS_ARRANGER VARCHAR2(1CHAR),
IS_BORROWER VARCHAR2(1CHAR),
IS_INVESTOR VARCHAR2(1CHAR),
ILP_ORG_STATUS NUMBER
);
type t_t_orgdata is table of t_orgdata ;
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined ;
End sam_test;
/


create or replace
package body sam_test
as
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined  --DCM-8575
is
v_orgdata t_orgdata;
v_organization_id number:=0;
v_cnt number:=1;
begin
--v_orgdata := t_t_orgdata(NULL,NULL);

--v_orgdata.extend;
--v_orgdata.org_id:=1;

loop
    fetch p_cur into v_organization_id ;
    exit when p_cur%NOTFOUND;
   
    SELECT co.crm_organization_id,
    co.organization_id,
    co.parent_organization_id,
    co.product_group_id,
    co.isinternal,
    END IS_INTERNAL ,
    co.OWNER_USER_ID ,
    NVL(co.STATUS_ID,DECODE(co.STATUS_ID ,11,11,2)) STATUS_ID ,
    co.organization_name,
    NULL domain,
    co.LOCK_VERSION ,
    co.NAME_EN,
    co.TAX_ID,
    co.ADDRESS1 ADDRESS1,
    co.ADDRESS2 ADDRESS2,
    co.STATE STATE,
    co.CITY CITY,
    co.ZIP ZIP,
    co.SUMMARY,
    co.COUNTRY_ID ,
    co.REGION_ID,
    co.VISIBILITY_TYPE,
    co.VISIBILITY_RESOURCE_ID,
    NVL(co.is_arranger,'N') is_arranger,
    NVL(co.is_borrower,'N') is_borrower,
    NVL(co.is_investor,'N') is_investor,
    co.status_id
    BULK COLLECT INTO v_orgdata
  FROM
    customer co
  where co.organization_id=v_organization_id;
   
        pipe row (v_orgdata);
        v_cnt:=v_cnt+1;
end loop;
NULL;
--close p_cur;
return;
end get_orgdets;

end sam_test;
/
0
flow01Commented:
What error ?

Maybe somewhere in
   co.isinternal,
    END IS_INTERNAL ,
    co.OWNER_USER_ID ,
>
   co.isinternal IS_INTERNAL ,
    co.OWNER_USER_ID ,
?
0
slightwv (䄆 Netminder) Commented:
This works for me ( I changed the table name just in case you tried to run the sample as-is and accidentally dropped your customer table):
drop table my_sample_customer purge;
create table my_sample_customer(
     crm_organization_id number,
     organization_id number,
     parent_organization_id number,
     product_group_id number,
     isinternal char(1),
     END number,
     OWNER_USER_ID  number,
     organization_name varchar2(50),
     LOCK_VERSION  number,
     NAME_EN char(50),
     TAX_ID char(50),
     ADDRESS1 char(50),
     ADDRESS2 char(50),
     STATE char(50),
     CITY char(50),
     ZIP char(50),
     SUMMARY varchar2(4000),
     COUNTRY_ID  number,
     REGION_ID number,
     VISIBILITY_TYPE number,
     VISIBILITY_RESOURCE_ID number,
     is_arranger char(1),
     is_borrower char(1),
     is_investor char(1),
     status_id number
);
  
insert into my_sample_customer(organization_id) values(1);
insert into my_sample_customer(organization_id) values(2);
insert into my_sample_customer(organization_id) values(3);
commit;
 
create or replace
Package sam_test As
type t_orgdata is record(
CRM_ORGANIZATION_ID NUMBER,
ORGANIZATION_ID NUMBER,
PARENT_ID NUMBER,
DEAL_GROUP_ID NUMBER,
IS_INTERNAL CHAR(1CHAR),
OWNER_USER_ID NUMBER,
STATUS_ID NUMBER,
ORGANIZATION_NAME VARCHAR2(50CHAR),
DOMAIN VARCHAR2(4000CHAR),
LOCK_VERSION NUMBER,
NAME_EN VARCHAR2(50CHAR),
TAX_ID VARCHAR2(50CHAR),
ADDRESS1 VARCHAR2(50CHAR),
ADDRESS2 VARCHAR2(50CHAR),
STATE VARCHAR2(50CHAR),
CITY VARCHAR2(50CHAR),
ZIP VARCHAR2(50CHAR),
SUMMARY VARCHAR2(4000CHAR),
COUNTRY_ID NUMBER,
REGION_ID NUMBER,
VISIBILITY_TYPE NUMBER,
VISIBILITY_RESOURCE_ID NUMBER,
IS_ARRANGER VARCHAR2(1CHAR),
IS_BORROWER VARCHAR2(1CHAR),
IS_INVESTOR VARCHAR2(1CHAR),
ILP_ORG_STATUS NUMBER
);
type t_t_orgdata is table of t_orgdata ;
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined ;
End sam_test;
/
 
show errors
 
create or replace
package body sam_test
as
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined  --DCM-8575
is
v_orgdata t_orgdata;
v_organization_id number:=0;
v_cnt number:=1;

begin
loop
     fetch p_cur into v_organization_id ;
     exit when p_cur%NOTFOUND;
    
     SELECT co.crm_organization_id,
     co.organization_id,
     co.parent_organization_id,
     co.product_group_id,
     co.isinternal,
     --END IS_INTERNAL ,
     co.OWNER_USER_ID ,
     NVL(co.STATUS_ID,DECODE(co.STATUS_ID ,11,11,2)) STATUS_ID ,
     co.organization_name,
     NULL domain,
     co.LOCK_VERSION ,
     co.NAME_EN,
     co.TAX_ID,
     co.ADDRESS1 ADDRESS1,
     co.ADDRESS2 ADDRESS2,
     co.STATE STATE,
     co.CITY CITY,
     co.ZIP ZIP,
     co.SUMMARY,
     co.COUNTRY_ID ,
     co.REGION_ID,
     co.VISIBILITY_TYPE,
     co.VISIBILITY_RESOURCE_ID,
     NVL(co.is_arranger,'N') is_arranger,
     NVL(co.is_borrower,'N') is_borrower,
     NVL(co.is_investor,'N') is_investor,
     co.status_id
     INTO v_orgdata
   FROM
     my_sample_customer co
   where co.organization_id=v_organization_id;
   
         pipe row (v_orgdata);
         v_cnt:=v_cnt+1;
end loop;
close p_cur;
return;
end get_orgdets;
 
end sam_test;
 /
 
show errors


--test it
--pull back only 1 and 2, not 3
select organization_id from table(sam_test.get_orgdets(cursor(
	select 1 from dual
	union all
	select 2 from dual
)));

Open in new window

0
sam_2012Author Commented:
Thanks a lot guys. I need to return more than one row  from the function. The above code allows to return the only one row. I need to modify the above code to return mutiple rows.  Is there any change I need to make it to return mutiple rows. Any help is really appreciated.
0
slightwv (䄆 Netminder) Commented:
I do not understand.

The sample I posted above returns two of the three rows in the test table.

Please provide sample data and expected results.
0
sam_2012Author Commented:
for example :

I meant the select query returns more than one row , the current implementation supports only one row. When i call the funciton as Select 12345 from dual

select organization_id from table(sam_test.get_orgdets(cursor(
      Select 12345 from dual
)));

In the customer table , I have two records for the organization_id =12345. So,  In the function when I fire ,  the query  to get the customer info for organization_id=12345, I getting error , Since the query supports only one record to fetched and stored in v_orgdata.

My requirement is the query can fetch mutilple rows and all these rows must be passed to the calling environment by storing it in the v_orgdata.
0
slightwv (䄆 Netminder) Commented:
Again, please provide sample data and expected results.

I seriously doubt you would be trying to call the function with the select and CURSOR function they way I had in my example.

I would also like to see how you plan on using this function once we get it working.

Duplicate rows is a simple fix with an inner loop.

I had to further tweak the example because you have two status_id alias columns.

If you noticed, I also commented out one of the columns because your record had a column your table didn't.

Here's the revised test case.  I added TWO '2' rows in the customer table to simulate the two rows you suggested.
drop table my_sample_customer purge;
create table my_sample_customer(
     crm_organization_id number,
     organization_id number,
     parent_organization_id number,
     product_group_id number,
     isinternal char(1),
     END number,
     OWNER_USER_ID  number,
     organization_name varchar2(50),
     LOCK_VERSION  number,
     NAME_EN char(50),
     TAX_ID char(50),
     ADDRESS1 char(50),
     ADDRESS2 char(50),
     STATE char(50),
     CITY char(50),
     ZIP char(50),
     SUMMARY varchar2(4000),
     COUNTRY_ID  number,
     REGION_ID number,
     VISIBILITY_TYPE number,
     VISIBILITY_RESOURCE_ID number,
     is_arranger char(1),
     is_borrower char(1),
     is_investor char(1),
     status_id number
);
  
insert into my_sample_customer(organization_id) values(1);
insert into my_sample_customer(organization_id) values(2);
insert into my_sample_customer(organization_id) values(2);
insert into my_sample_customer(organization_id) values(3);
commit;
 
create or replace
Package sam_test As
type t_orgdata is record(
CRM_ORGANIZATION_ID NUMBER,
ORGANIZATION_ID NUMBER,
PARENT_ID NUMBER,
DEAL_GROUP_ID NUMBER,
IS_INTERNAL CHAR(1CHAR),
OWNER_USER_ID NUMBER,
STATUS_ID NUMBER,
ORGANIZATION_NAME VARCHAR2(50CHAR),
DOMAIN VARCHAR2(4000CHAR),
LOCK_VERSION NUMBER,
NAME_EN VARCHAR2(50CHAR),
TAX_ID VARCHAR2(50CHAR),
ADDRESS1 VARCHAR2(50CHAR),
ADDRESS2 VARCHAR2(50CHAR),
STATE VARCHAR2(50CHAR),
CITY VARCHAR2(50CHAR),
ZIP VARCHAR2(50CHAR),
SUMMARY VARCHAR2(4000CHAR),
COUNTRY_ID NUMBER,
REGION_ID NUMBER,
VISIBILITY_TYPE NUMBER,
VISIBILITY_RESOURCE_ID NUMBER,
IS_ARRANGER VARCHAR2(1CHAR),
IS_BORROWER VARCHAR2(1CHAR),
IS_INVESTOR VARCHAR2(1CHAR),
ILP_ORG_STATUS NUMBER
);
type t_t_orgdata is table of t_orgdata ;
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined ;
End sam_test;
/
 
show errors
 
create or replace
package body sam_test
as
function get_orgdets(p_cur IN sys_refcursor) return t_t_orgdata pipelined  --DCM-8575
is
v_orgdata t_orgdata;
v_organization_id number:=0;
v_cnt number:=1;

begin
loop
     fetch p_cur into v_organization_id ;
     exit when p_cur%NOTFOUND;
    
	for i in (
     SELECT co.crm_organization_id,
     co.organization_id,
     co.parent_organization_id,
     co.product_group_id,
     co.isinternal,
     --END IS_INTERNAL ,
     co.OWNER_USER_ID ,
     NVL(co.STATUS_ID,DECODE(co.STATUS_ID ,11,11,2)) q_STATUS_ID ,
     co.organization_name,
     NULL domain,
     co.LOCK_VERSION ,
     co.NAME_EN,
     co.TAX_ID,
     co.ADDRESS1 ADDRESS1,
     co.ADDRESS2 ADDRESS2,
     co.STATE STATE,
     co.CITY CITY,
     co.ZIP ZIP,
     co.SUMMARY,
     co.COUNTRY_ID ,
     co.REGION_ID,
     co.VISIBILITY_TYPE,
     co.VISIBILITY_RESOURCE_ID,
     NVL(co.is_arranger,'N') is_arranger,
     NVL(co.is_borrower,'N') is_borrower,
     NVL(co.is_investor,'N') is_investor,
     co.status_id
   FROM
     my_sample_customer co
   where co.organization_id=v_organization_id
) loop
	v_orgdata := i;
   
         pipe row (v_orgdata);
         v_cnt:=v_cnt+1;
	end loop;
end loop;
close p_cur;
return;
end get_orgdets;
 
end sam_test;
 /
 
show errors


--test it
--pull back only 1 and 2, not 3
select organization_id from table(sam_test.get_orgdets(cursor(
	select 1 organization_id from dual
	union all
	select 2 from dual
)));
                                          

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.