AnsweredAssumed Answered

Call stored procedure in stored procedure

Question asked by Endyno on Sep 24, 2013
Latest reply on Nov 26, 2013 by RamBandari
Hello all,

I need to create job of SQL stored procedure type with parameter. Parameter is unique_name from srm_resources. At the test stage I will write this name into job parameter manually. According the unique_name stored precedure calls another SP called CMN_SEC_ASSIGN_INST_RIGHT_SP (SP to assign instance right). Purpose is to assign instance right ResourceEnterTime (for 700 resources) to another resource.
create or replace
PROCEDURE z_link_timesheets 
(
OLD_TIMETRACKER_UNIQUE_NAME varchar2

)
AS 

BEGIN

CMN_SEC_ASSIGN_INST_RIGHT_SP ('USER',OLD_TIMETRACKER_UNIQUE_NAME, 'ResourceEnterTime', 5016793);
commit;

END;
I get the message:
Stored Procedure application error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20005: USER_NAME DOES NOT EXIST
ORA-06512: at "NIKU.CMN_SEC_ASSIGN_INST_RIGHT_SP", line 48
ORA-06512: at "NIKU.Z_LINK_TIMESHEETS", line 14
ORA-06512: at line 1
So the problem is in CMN_SEC_ASSIGN_INST_RIGHT_SP that user_name from cmn_sec_users cannot be found.

from CMN_SEC_ASSIGN_INST_RIGHT_SP
    IF P_PRINCIPAL_TYPE = 'GROUP'
    THEN
      BEGIN
        SELECT  ID
        INTO    V_PRINCIPAL_ID
        FROM    CMN_SEC_GROUPS
        WHERE   GROUP_CODE = P_PRINCIPAL_CODE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR (-20004, 'GROUP_CODE FOR GROUP DOES NOT EXIST');
        WHEN OTHERS THEN
          NULL;
      END;
    ELSE
      BEGIN
        SELECT  ID
        INTO    V_PRINCIPAL_ID
        FROM    CMN_SEC_USERS
        WHERE   USER_NAME = P_PRINCIPAL_CODE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR (-20005, 'USER_NAME DOES NOT EXIST');
        WHEN OTHERS THEN
          NULL;
      END;
    END IF;
If I run this SP in developer
EXEC z_link_timesheets('acarj')
where acarj is unique_name, SP is processed succesfully. It means that SP: CMN_SEC_ASSIGN_INST_RIGHT_SP cannot 'read' parameter which was inserted in Clarity.

Parameter defined in Clarity:

Parameter Label : OLD_TIMETRACKER_UNIQUE_NAME
Bind Parameter Code OLD_TIMETRACKER_UNIQUE_NAME
Type: Text


Any idea how to solve this?

Thanks

Outcomes