Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Stored Procedure error SPL1031
 
From:   Dieter Noeth

Bill Hassinger wrote:

  I am creating a stored procedure to revoke logons for dormant account. Below is the Text and error codes, can someone help me correct the problem?  


          > 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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016