Re: Help with procedure

bryan!

please provide the errors, you can query them with

SHO[W] ERR[ORS] ... daniel

"Bryan, Miriam" wrote:
>
> 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: Daniel Wisser
  INET: daniel.wisser_at_isis-papyrus.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).