Oracle-L: RE: Help with procedure

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 21 Jan 2003 05:09:05 -0800
Message-ID: <F001.00535AD5.20030121050905@fatcity.com>

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: mbryan_at_bcharrispub.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: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

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