Folks,
Good day !
I am working on Oracle 11G.
I am wirting a Pipelined Function, that calls a couple of other Pipelined Functions. I have not been successful so far, and with the latest code, I get this error :-
Error(29,3): PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
I do get a couple of other errors, but I guess they are a fallout of this main error.
I have two similar Pipelined Functions called DRN_SECRTY_GRP_DETAILS_FN and DRN_SECRTY_USR_DETAILS_FN. I use them to fetch a pair of Attribute values from an LDAP Server, using DBMS_LDAP. They both return a Table Type of this Object :-
CREATE OR REPLACE TYPE DRN_SECRTY_LDAP_ATTR_TYP AS OBJECT
(
attr_name varchar2(1000),
attr_val varchar2(1000)
);
Now, I have another Pipelined Function called DRN_SECURITY_LDAP_USR_FN, which makes use of DRN_SECRTY_GRP_DETAILS_FN and DRN_SECRTY_USR_DETAILS_FN :-
CREATE OR REPLACE TYPE DRN_SECRTY_LDAP_USR_TYP AS OBJECT
(
USER_LOGIN_ID VARCHAR2(1000),
USER_NAME VARCHAR2(1000),
USER_STATUS VARCHAR2(10),
USER_CREATION_DATE VARCHAR2(1000),
USER_LAST_LOGIN_DATE VARCHAR2(1000),
USER_GROUP VARCHAR2(1000),
ROLE_NAME VARCHAR2(1000)
);
CREATE OR REPLACE TYPE DRN_SECRTY_LDAP_USR_TBL_TYP AS TABLE OF DRN_SECRTY_LDAP_USR_TYP;
CREATE OR REPLACE FUNCTION DRN_SECURITY_LDAP_USR_FN
RETURN DRN_SECRTY_LDAP_USR_TBL_TYP PIPELINED
IS
v_user_name VARCHAR2(1000);
v_user_login_id VARCHAR2(1000);
v_user_status VARCHAR2(10);
v_user_creation_date VARCHAR2(1000);
v_user_last_login_date VARCHAR2(1000);
v_user_group VARCHAR2(1000);
v_role_name VARCHAR2(1000);
v_group_details DRN_SECRTY_LDAP_ATTR_TYP:= DRN_SECRTY_LDAP_ATTR_TYP(NULL,NULL);
v_user_details DRN_SECRTY_LDAP_ATTR_TYP:= DRN_SECRTY_LDAP_ATTR_TYP(NULL,NULL);
BEGIN
<< group_loop >>
FOR each_group IN
(
SELECT
GROUP_NAME
FROM
USER_GROUP
)
LOOP
v_user_group := each_group.GROUP_NAME;
select *
BULK COLLECT into v_group_details
from
table(DRN_SECRTY_GRP_DETAILS_FN(v_user_group));
<< users_in_group_loop >>
FOR each_user_in_group IN v_group_details.first..v_group_details.last
LOOP
v_user_name := SUBSTR(REGEXP_SUBSTR(each_user_in_group.ATTR_VALUE,'=[^,]+',1,1),2);
select *
BULK COLLECT into v_user_details
from
table(DRN_SECRTY_GRP_DETAILS_FN(v_user_name));
<<user_details_loop>>
FOR each_user IN v_user_details.first..v_user_details.last
LOOP
IF each_user.ATTR_NAME='uid'
THEN
v_user_login_id := each_user.ATTR_VALUE;
END IF;
IF each_user.ATTR_NAME='cn'
THEN
v_user_name := each_user.ATTR_VALUE;
END IF;
IF each_user.ATTR_NAME='ou'
THEN
v_user_status := each_user.ATTR_VALUE;
END IF;
IF each_user.ATTR_NAME='createTimestamp'
THEN
v_user_creation_date := each_user.ATTR_VALUE;
END IF;
PIPE ROW
(
DRN_SECRTY_LDAP_USR_TYP
(
v_user_login_id,
v_user_name,
v_user_status,
v_user_creation_date,
v_user_last_login_date,
v_user_group,
v_role_name
)
);
END LOOP user_details_loop;
END LOOP users_in_group_loop;
END LOOP group_loop ;
END DRN_SECURITY_LDAP_USR_FN;
It looks like I cannot use BULK COLLECT in this fashion.
Can you please help me with this ? I don't think I have got the syntax right in the parts where I call the other two Pipelined Functions.
Regards,
Sandeep