Archives of the TeradataForum

Message Posted: Wed, 21 Aug 2013 @ 12:40:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, August 21, 2013 05:06 -->

Dear All,

I have created a procedure like below. I'm not able to understand the unpredictable behavior when I call the procedure like below

     CALL  XXX('DST_STAGE',
     'S059_015_WO_HEADER',sqlMsg,sqlErrCde,sqlStte)

When I execute this procedure the FIRST time, it always ends with error 'Table Does Not Exist', though the table does exist; When I execute the same statement the second time by pressing F5 again on SQLA, it executes fine. Please note that I have not made any changes to the call statement. I'm not able to figure out why. Even if I assume something is being 'set' during the first execution, I would expect it to be reset when the procedure bails out.

Requesting you kind guidance on this.

     REPLACE PROCEDURE  UD_GEM_AD.XXX
         ( IN DATABASENAME VARCHAR(30)
          ,IN TABLENAME VARCHAR(30)
          ,OUT  sqlMsg VARCHAR(133)
          ,OUT sqlErrCde INTEGER
          ,OUT sqlStte INTEGER
           )


     AUTOMATE: BEGIN

        DECLARE SQL_ERR_CDE   INTEGER;
        DECLARE SQL_STATE     INTEGER;
        DECLARE tmpMsg        VARCHAR(133);
        DECLARE WS_TBLKIND    CHAR(1) DEFAULT ' ';
        DECLARE WS_DB         INTEGER DEFAULT 0;
        DECLARE CNT           INTEGER;


        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
           SET SQLERRCDE = SQLCODE;
           SET SQLSTTE = SQLSTATE;
           SET SQLMSG = TMPMSG;
           SET SQL_ERR_CDE = SQLCODE;
           SET SQL_STATE = SQLSTATE;
           INSERT INTO UD_GEM_AD.ERR_STD_PRC_LOG
           VALUES ('UD_GEM_AD','MVC_CALC_EXCP','E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);

        END;

        DECLARE CONTINUE HANDLER FOR SQLWARNING
        BEGIN
           SET SQLERRCDE = SQLCODE;
           SET SQLSTTE = SQLSTATE;
           SET SQLMSG = TMPMSG;
           SET SQL_ERR_CDE = SQLCODE;
           SET SQL_STATE = SQLSTATE;
           INSERT INTO UD_GEM_AD.ERR_STD_PRC_LOG
           VALUES ('UD_GEM_AD','MVC_CALC_EXCP','W',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);

        END;



        DECLARE EXIT HANDLER FOR SQLSTATE  '02000'
        BEGIN
           SET SQLERRCDE = SQLCODE;
           SET SQLSTTE = SQLSTATE;
           SET SQLMSG = TMPMSG;
           SET SQL_ERR_CDE = SQLCODE;
           SET SQL_STATE = SQLSTATE;
           INSERT INTO UD_GEM_AD.ERR_STD_PRC_LOG
           VALUES ('UD_GEM_AD','MVC_CALC_EXCP','E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);

        END;


         SET SQLERRCDE = 0;
         SET SQLSTTE = 0;

     /* Validate if the databasename exists on DBC.DBASE.  If not, end with error message. */
         SET tmpMsg = 'Database Does Not Exist  ...';
         SET WS_DB = 0;

         LOCK ROW FOR ACCESS
         SELECT 1
         INTO WS_DB
         FROM DBC.DBASE
         WHERE DATABASENAME = TRIM(:databasename)  ;

     /* Validate if the tablename exists on DBC.TABLES and if it is a table or join index.  If not, end with error message. */
         SET tmpMsg = 'Table Does Not Exist  ...';
         SET WS_TBLKIND = ' ';

         LOCK ROW FOR ACCESS
         SELECT tablekind
         INTO WS_TBLKIND
         FROM DBC.TABLES
         WHERE DATABASENAME = TRIM(:databasename)
           AND TABLENAME = TRIM(:tablename)
           AND TABLEKIND IN ( 'T')
           ;


     CALL DBC.SYSEXECSQL('DELETE FROM UD_GEM_AD.ERR_STD_PRC_LOG;');


     SET sqlMsg =' Data frequency.....';

     CALL UD_GEM_AD.AAA (TRIM(:DATABASENAME),
     TRIM(:TABLENAME),sqlMsg,sqlErrCde,sqlStte) ;


     SET sqlMsg ='Compstmt.....';


      CALL UD_GEM_AD.BBB(TRIM(:DATABASENAME),
     TRIM(:TABLENAME),sqlMsg,sqlErrCde,sqlStte);


      SET sqlMsg ='DDL.....';


     CALL  UD_GEM_AD.CCC (TRIM(:DATABASENAME),
     TRIM(:TABLENAME),sqlMsg,sqlErrCde,sqlStte) ;


     SET sqlMsg ='DDL Ready!.....';


     END AUTOMATE;

	<Prev	Next>		<<First	<Prev	Next>	Last>>