Archives of the TeradataForum

Message Posted: Fri, 22 Feb 2008 @ 20:13:50 GMT

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

Bill Hassinger wrote:

          > CREATE PROCEDURE sysdba.DormantUser()
          > BEGIN
          > /* declare Variables */
          > DECLARE userid CHAR(8);

This should be more than just 8 characters, because 'student_1%' returns 9 chars at least :-)

          > /* create cursor */
          > FOR Dormant AS secondcurse CURSOR FOR
          > SELECT UserName
          > FROM DBC.Allusers
          > WHERE (UserName LIKE 'student_1%')
          > /* process records */
          > DO
          > SET userid = Dormant.UserName ;

Does dbc.allusers really exist?

When i tried to compile it, there was another error message:

     SPL1027:E(L11), Missing/Invalid SQL statement'E(3807):
     Object 'DBC.allusers' does not exist.'.

Followed by:

     SPL1031:E(L12), Referring to undefined alias 'username'.

          > REVOKE Logon ON ALL TO :userid ;

You can't pass a parameter for a username, you'll have to use Dynamic SQL instead:

     CALL dbc.sysexecsql('REVOKE LOGON ON ALL FROM ' || userid || ';') ;

Dieter

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