In Teradata SPL, procedure call within a procedure fails when using a cursor

Database
Enthusiast

In Teradata SPL, procedure call within a procedure fails when using a cursor

Hello all,

 

I've been facing this weird issue where procedure call within a procedure fails when using a cursor. It works if I remove the cursor.

 

--Sample Procedure highlighting the issue I'm facing
REPLACE
PROCEDURE TdProcedure()BEGIN DECLARE SqlStr VARCHAR(64000); DECLARE TestCount INTEGER; CALL WriteToLog('begin'); --This is my procedure where I'm writing to a table for loggin /debugging procedures DECLARE C1 CURSOR FOR S1; SET SqlStr = 'SEL 10'; PREPARE S1 FROM SqlStr; OPEN C1; FETCH C1 INTO TestCount; CLOSE C1; END;

 This error occurs

SPL1027:E(L8), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'DECLARE' keyword.'.
SPL1030:E(L13), Referring to undefined cursor 'C1'.
SPL1030:E(L15), Referring to undefined cursor 'C1'.

 

 

The same procedure works if I remove the procedure call in the body

SyntaxEditor Code Snippet

REPLACE PROCEDURE TdProcedure()BEGIN
    
    DECLARE SqlStr VARCHAR(64000);
    DECLARE TestCount INTEGER;
    --CALL WriteToLog('begin');  --This is my procedure where I'm writing to a table for loggin /debugging procedures    
    DECLARE C1 CURSOR FOR S1;
    
    SET SqlStr = 'SEL 10';
    PREPARE S1 FROM SqlStr;
    
    OPEN C1;
        FETCH C1 INTO TestCount;
    CLOSE C1;

END;

 

And the same procedure works if I remove the cursor and have the procedure call in the body

SyntaxEditor Code Snippet

 REPLACE PROCEDURE TdProcedure()BEGIN
    
    DECLARE SqlStr VARCHAR(64000);
    DECLARE TestCount INTEGER;
    CALL WriteToLog('begin');  --This is my procedure where I'm writing to a table for loggin /debugging procedures    
END;

 

For your reference, I'm including code for logging procedure that I'm calling within

SyntaxEditor Code Snippet

---------------------------------------------------------------------- --Logging table and procedure ---------------------------------------------------------------------- --Defination of the table thats being used in the Procedure that's being called inside of other procedures CREATE SET TABLE LOG_TABLE     (
      MSG_ID BIGINT,
      RUN_ID BIGINT,
      MSG_TIMESTAMP TIMESTAMP(6),
      MSG VARCHAR(10000) )UNIQUE PRIMARY INDEX ( MSG_ID );

--Procedure being called withinREPLACE PROCEDURE WriteToLog(IN in_InputMsg VARCHAR(60000)
)BEGIN
    DECLARE v_MaxMsgId BIGINT;
    DECLARE v_MaxRunId BIGINT;
    DECLARE v_MsgTimestamp TIMESTAMP;
    DECLARE v_TempTimestamp TIMESTAMP;
    
    L1:BEGIN
        --Getting Max of LOG_TABLE.MSG_ID into v_MaxId        SELECT MAX(MSG_ID) INTO v_MaxMsgId FROM LOG_TABLE;
        SELECT MAX(RUN_ID) INTO v_MaxRunId FROM LOG_TABLE;
        SELECT MAX(MSG_TIMESTAMP) INTO v_MsgTimestamp FROM LOG_TABLE;
        
        --Creating Next Sequence Id        IF v_MaxMsgId IS NULL THEN
            SET v_MaxMsgId = 1;
        ELSE
            SET v_MaxMsgId = v_MaxMsgId + 1;
        END IF;
        
        --Creating Next Run Id        IF v_MaxRunId IS NULL THEN
            SET v_MaxRunId = 1;
        ELSE
            IF (CURRENT_TIMESTAMP - v_MsgTimestamp) SECOND (4,6) > 1 THEN
                SET v_MaxRunId = v_MaxRunId + 1;
                SELECT CURRENT_TIMESTAMP INTO v_TempTimestamp;
                --v_MaxRunId = SeqInc(:v_MaxRunId);            END IF;
        END IF;
        
        INSERT INTO LOG_TABLE        (
        MSG_ID,
        RUN_ID,
        MSG_TIMESTAMP,
        MSG        )
        VALUES        (
        v_MaxMsgId,
        v_MaxRunId,
        CURRENT_TIMESTAMP,
        in_InputMsg        );
    END L1;
END;

 

Could anyone please point out what I'm doing wrong if I am?

 

Thanks,

Pattabhi


Accepted Solutions
Junior Contributor

Re: In Teradata SPL, procedure call within a procedure fails when using a cursor

There's one declaration block within a Stored Procedure followed by one statement block.

 

But every nested compound statement (indicated by BEGIN/END) allows a new declaration block:

 

REPLACE PROCEDURE TdProcedure()
BEGIN DECLARE SqlStr VARCHAR(64000); -- declaration block DECLARE TestCount INTEGER; CALL WriteToLog('begin'); -- statements start here, No DECLARE after this BEGIN -- new compound statement DECLARE C1 CURSOR FOR S1; -- declaration block SET SqlStr = 'SEL 10'; -- statements start here, No DECLARE after this PREPARE S1 FROM SqlStr; OPEN C1; FETCH C1 INTO TestCount; CLOSE C1; END; END;

 

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: In Teradata SPL, procedure call within a procedure fails when using a cursor

The fact that removing the Call or removing the rest of the logic works is the clue you need! The solution is to move the Call to the line following the Declare Cursor line.  E.g.:

...

DECLARE SqlStr VARCHAR(64000);
DECLARE TestCount INTEGER;
DECLARE C1 CURSOR FOR S1;
CALL WriteToLog('begin');
SET SqlStr = 'SEL 10';
PREPARE S1 FROM SqlStr;

...

Junior Contributor

Re: In Teradata SPL, procedure call within a procedure fails when using a cursor

There's one declaration block within a Stored Procedure followed by one statement block.

 

But every nested compound statement (indicated by BEGIN/END) allows a new declaration block:

 

REPLACE PROCEDURE TdProcedure()
BEGIN DECLARE SqlStr VARCHAR(64000); -- declaration block DECLARE TestCount INTEGER; CALL WriteToLog('begin'); -- statements start here, No DECLARE after this BEGIN -- new compound statement DECLARE C1 CURSOR FOR S1; -- declaration block SET SqlStr = 'SEL 10'; -- statements start here, No DECLARE after this PREPARE S1 FROM SqlStr; OPEN C1; FETCH C1 INTO TestCount; CLOSE C1; END; END;

 

Junior Contributor

Re: In Teradata SPL, procedure call within a procedure fails when using a cursor

Additionally your logging porcedure can be simplified:

You can combine multiple SELECT MAX on the same table into a single one:

SELECT Max(RUN_ID), Max(MSG_TIMESTAMP), Max(MSG_ID)
INTO v_MaxRunId, v_MsgTimestamp, v_MaxMsgId 
FROM LOG_TABLE;

And there's no need to do a SELECT INTO without accessing a table. simply use SET instead:

-- SELECT Current_Timestamp INTO v_TempTimestamp; 
SET v_TempTimestamp = Current_Timestamp;

 

Enthusiast

Re: In Teradata SPL, procedure call within a procedure fails when using a cursor

@dnoeth Thanks for the clarification. It works fine now