Click here to Skip to main content
15,903,854 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi frnz,

I have created the following stored procedure


SQL
CREATE or REPLACE PROCEDURE RTIMD.IALERT_PROC ( IN usr_name VARCHAR(20) DEFAULT '', IN IN_RTIM_ID VARCHAR(25) default '', IN api_method VARCHAR(25) DEFAULT 

'', IN params VARCHAR(500) default '', IN application_name VARCHAR(20) default '', OUT TICKT_ID VARCHAR(20), OUT RTIMID_VAR VARCHAR(25)) 
LANGUAGE SQL 
BEGIN 
DECLARE RTIM_ID_OUT VARCHAR(25); 
DECLARE DO INTEGER DEFAULT 0;

IF api_method = 'getIncident' THEN 
	EXECUTE IMMEDIATE 'SELECT DISTINCT TICKET_ID FROM RTIMD.V3_TICKET_LOG WHERE RTIM_ID = IN_RTIM_ID'; 
	SET RTIM_ID_OUT = IN_RTIM_ID; 
	SET DO = 1;
ELSEIF api_method = 'createIncident' THEN 
	EXECUTE IMMEDIATE 'select ''RTIM_'' concat usr_name concat varchar(bigint(days(current date) - days(''1970-01-01''))*bigint(86400) + 

midnight_seconds(current_timestamp - current_timezone)) concat ''_'' concat varchar(SMALLINT ( RAND () *10000 + 1 )) from SYSIBM.SYSDUMMY1'; 
	SET RTIM_ID_OUT = RTIMID_VAR; 
	SET DO = 1;
ELSEIF api_method = 'closeIncident' THEN 
	SET RTIM_ID_OUT = IN_RTIM_ID; 
	SET DO = 1;
END IF; 

IF DO = 1 THEN 
	INSERT INTO RTIMD.V3_TICKET_LOG( RTIM_ID, STATE, API_METHOD, API_MESSAGE, TICKET_TYPE, IMPACT_ROW_ID, TICKET_ID, PARAMS, REMOTE_USER, CREATED, 

UPDATED, DB_STATE, CPS_STATE, APPLICATION, PRB_OWNER, SN_INSTANCE ) VALUES ( RTIM_ID_OUT, 0, api_method, '', 'Incident', 0, '', params, usr_name, CURRENT 

TIMESTAMP, CURRENT TIMESTAMP, 0, 0, application_name, 'self', 0 ); 
END IF;
END


It is been created successfully

Then I tried to call the SP in the following ways

SQL
CALL IALERT_PROC('sweety', DEFAULT, 'createIncident', '{''identifier'' => ''673EAC14-7784-11E2-9E36-9A6641CD357D'',''time_worked'' => 120,''u_sys_id'' => ''6096ab35876b34008ef7ad4a7d434dd2'',''u_parent_ticket'' => ''INC00523082'',''user'' => ''suhass'',''u_test_record'' => 0,''task'' => ''INC00523082'',''u_effort_type'' => ''MAINT - INCIDENT REVIEW''}', 'test')


SQL
CALL RTIMD.IALERT_PROC('sweety', DEFAULT, 'createIncident', '{''identifier'' => ''673EAC14-7784-11E2-9E36-9A6641CD357D'',''time_worked'' => 120,''u_sys_id'' => ''6096ab35876b34008ef7ad4a7d434dd2'',''u_parent_ticket'' => ''INC00523082'',''user'' => ''suhass'',''u_test_record'' => 0,''task'' => ''INC00523082'',''u_effort_type'' => ''MAINT - INCIDENT REVIEW''}', 'test')


But these statements doesn't seems to work.

I am getting the following error.

SQL0440N No authorized routine named "IALERT_PROC" of type "PROCEDURE" having
compatible arguments was found. SQLSTATE=42884

Even I checked the CURRENT SCHEMA & CURRENT FUNCTION PATH and set them to RTIMD
But still I am getting the same error.

Please any suggestions.

TIA.
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900