Archives of the TeradataForum

Message Posted: Wed, 22 May 2013 @ 12:43:54 GMT

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

<-- Anonymously Posted: Wednesday, May 22, 2013 06:11 -->

Dear specialists,

I'm trying to create a role via a stored procedure. (I'm not going into details why, I just need it).

One of the first things within that procedure is to test whether or not that role already exists. If so, stop processing and end gracefully.

But for one reason or the other, I can't trap the sqlcode 5612.

This is my test procedure:

     REPLACE PROCEDURE sysdba.test_role(p_rolename varchar(30)) BEGIN
         DECLARE h_rolename     VARCHAR(30);
         DECLARE l_sql          VARCHAR(255);

         DECLARE EXIT HANDLER FOR SQLSTATE 'T5612'
         BEGIN
               CALL SYSDBA.SEC_ADDLOG('test_role',
                 'Role'||TRIM(h_rolename)||' already exists.',:SQLSTATE,:SQLCODE, :ACTIVITY_COUNT);
         END;

         SET h_rolename = p_rolename;

         SET l_sql = 'CREATE ROLE '||TRIM(h_rolename)||';';
         CALL DBC.SysExecSQL(:l_sql);

     END;

This is my unittest :

     -- first clean out entries from my test in the log table.
     delete from sysdba.sec_log
     where proc_abr = 'test_role';

     -- Execute test procedure
     call sysdba.test_role('test');

     -- Show the entries from my test in the logtable.
     select *
     from sysdba.seC_log
     where proc_abr = 'test_role';

The first time it executes fine, because the role doesn't exist.

The second time however, I would expect it to just exit but it just doesn't. (I've also tried to define the handler as .FOR SQLEXCEPTION. Also without success)

Can anyone help me out here?

Best regards

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