Oracle-L: RE: Help with procedure

From: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Mon, 20 Jan 2003 15:08:42 -0800
Message-ID: <F001.005353E4.20030120150842@fatcity.com>

What is the exact error? What oracle version? Just guessing, are the privs granted directly to the user or through a role? If they are via a role, grant them directly to the user...

HTH
Tim

-----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: Johnston, Tim
INET: TJohnston_at_quallaby.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