Miriam,
alaong with what everyone else sad, your loop control is wrong.
you said:
FOR x IN get_workgroups
this is not referencing your cursor properly. you named your cursor
get_admin.
the FOR statement should be:
FOR x in get_admin
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, January 20, 2003 4:16 PM
To: Multiple recipients of list ORACLE-L
Hi List, I'm hoping someone can help me. I'm trying to compile a procedure,
which calls another procedure and I keep getting an error on the first end;
can't figure out what's wrong. Perhaps someone that hasn't seen this
procedure for hours can tell me what's wrong.
The user has all the right privileges.
Here's the code:
CREATE OR REPLACE PROCEDURE EMPLOYEE_ACTIVITY.convert_AA_admin
AS
--
--
-- Purpose: Convert Agent_Activity.Admin table to Employee_Activity
-- User_Info and User_Security_Group.
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ------ -------------------------------------------
-- psurring 1/20/03 Initial implementation
--
err_num NUMBER;
err_msg VARCHAR2 (100);
V_SECURITY_GROUP USER_SECURITY_GROUP.SECURITY_GROUP_ID%TYPE;
V_BRANCH USER_INFO.BRANCH%TYPE;
CURSOR get_admin
IS
Select username,
password,
level_,
center,
first_name,
last_name
From AGENT_ACTIVITY.ADMIN
Where upper(level_) in ('CENTER','TEAM','GROUP','PAYROLL');
BEGIN
FOR x IN get_workgroups
LOOP
begin
if upper(x.level_) in ('CENTER','TEAM','GROUP') then
v_security_group := 4;
else if upper(x.level_) in ('PAYROLL') then
v_security_group := 2;
end if;
if x.center = 'TX' then
v_branch := '7';
else
v_branch := 'G';
end if;
EMPLOYEE_ACTIVITY.ADD_USER(x.username,
x.first_name,
x.last_name,
x.password,
v_branch,
null,
'SYSTEM',
v_security_group);
EXCEPTION
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
ROLLBACK;
insert into Application_error (USER_NAME,
ERROR_DATE,
PROCEDURE_NAME,
SQL_ERROR_NUM,
SQL_ERR_MSG,
PARAMETER)
values
(v_LAST_UPDATED_BY,sysdate,'EMPLOYEE_ACTIVITY.ADD_USER',v_err_num,v_err_msg,
'v_USER_NAME='||v_USER_NAME||
'v_FIRST_NAME='||v_FIRST_NAME||
'v_LAST_NAME='||v_LAST_NAME||
'v_PASSWORD='||v_PASSWORD||
'v_DEFAULT_BRANCH='||v_DEFAULT_BRANCH||
'v_DEFAULT_WORKGROUP_ID='||v_DEFAULT_WORKGROUP_ID||
'v_LAST_UPDATED_BY='||v_LAST_UPDATED_BY||
'v_SECURITY_GROUP_ID='||v_SECURITY_GROUP_ID);
COMMIT;
RAISE;
END;
END LOOP;
end;
/
TIA,
M. Bryan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bryan, Miriam
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
