Click here to Skip to main content
15,905,148 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have written the following stored proc in db2.

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);

IF api_method = 'getIncident' THEN
    SET TICKT_ID = EXECUTE IMMEDIATE ( 'SELECT DISTINCT TICKET_ID FROM RTIMD.V3_TICKET_LOG WHERE RTIM_ID = IN_RTIM_ID' );
    SET RTIM_ID_OUT = IN_RTIM_ID;
ELSEIF api_method = 'createIncident' THEN
    SET RTIMID_VAR = 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;
ELSEIF api_method = 'closeIncident' THEN
    SET RTIM_ID_OUT = IN_RTIM_ID;
END IF;

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, TIMESTAMP, 0, 0, application_name, 'self', '' );

END



When executing the stored proc, I am getting the following error message.

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "IMMEDIATE" was found following "SET
TICKT_ID=EXECUTE".  Expected tokens may include:  "IN".  LINE NUMBER=1.
SQLSTATE=42601


What went wrong here, I have surfed enough but no answers :( There could be some syntactical errors. Please guide me.

TIA.
Posted

I think you can't use EXECUTE IMMEDIATE with SET Statement.

Try this link as it shows how to return parameter from the executing statement which you can use further

EXECUTE IMMEDIATE statement (PL/SQL)[^]
 
Share this answer
 
The problem was in quoting. After replacing all the double quotes with single quotes, it worked :)
 
Share this answer
 

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