RE: Help with procedure

I make changes below, if I don't make mistaken.

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;
> elsif upper(x.level_) in ('PAYROLL') then
> v_security_group := 2;
> end if;
>

> -----Original Message-----
> From: Jamadagni, Rajendra [SMTP:Rajendra.Jamadagni_at_espn.com]
> Sent: Tuesday, January 21, 2003 7:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Help with procedure
>
> There is a END IF missing before the EXCEPTION ...
>
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of ESPN
> Inc.
> QOTD: Any clod can have facts, but having an opinion is an art!
>
>
> -----Original Message-----
> From: Kulev, Milen [ <mailto:Milen.Kulev_at_BusinessMart.de>]
> Sent: Tuesday, January 21, 2003 5:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: AW: Help with procedure
>
>
> Wasn't the Syntax for nested IFs
> ----------------------------------------
> IF <condition1>
> THEN
> ...
> ELSIF <condition2>
> THEN
> ...
> ELSIF <condition2>
> THEN
> ...
> END IF;
> ----------------------------------------
> instead of
>
> ----------------------------------------
> else if upper(x.level_) in ('PAYROLL') then
> > v_security_group := 2;
> ----------------------------------------
>
>
> -----Ursprüngliche Nachricht-----
> Von: Tim Gorman [ <mailto:Tim_at_SageLogix.com>]
> Gesendet: Montag, 20. Januar 2003 22:47
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Help with procedure
>
>
> Could you relate exactly what error you are seeing? Just seeing the code
> doesn't help much...
>
> To get the error messages, you can execute the following from SQL*Plus:
>
> SHOW ERRORS PROCEDURE PROCEDURE CONVERT_AA_ADMIN
>
> while connected as the account EMPLOYEE_ACTIVITY...
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Monday, January 20, 2003 2:15 PM
>
>
> > 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_ms
> g,
> > '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;
> > /
>
> --
> Please see the official ORACLE-L FAQ: <http://www.orafaq.net>
> --
> Author: Tim Gorman
> INET: Tim_at_SageLogix.com
>
> 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: ListGuru_at_fatcity.com (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: Kulev, Milen
> INET: Milen.Kulev_at_BusinessMart.de
>
> 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: ListGuru_at_fatcity.com (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).
> << File: ESPN_Disclaimer.txt >>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sony kristanto
  INET: Sony_at_polyfincanggih.com

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: ListGuru_at_fatcity.com (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).