[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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
0
sam_2012
Asked:
sam_2012
3 Solutions
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
sam_2012Author Commented:
Thanks a lot.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now