Archives of the TeradataForum
Message Posted: Wed, 22 May 2013 @ 12:43:54 GMT
<-- Anonymously Posted: Wednesday, May 22, 2013 06:11 -->
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|