on 02-12-2016 2:20 PM
I would like to change all the users. Specifically, I would like to add the email address as a parameter. For one user at a time I can do this using ALTER USER. But for a mass user change, I decided I would need a complete procedure using the cursor. To that purpose I uploaded the user names and email addresses to a table I created: user_email.
CREATE PROCEDURE user_email()
LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE v_email NVARCHAR(256) default '';
DECLARE v_user NVARCHAR(5000) default '';
DECLARE CURSOR c_1 FOR
SELECT user, email FROM MYSCHEMA.USER_EMAIL;
FOR cur_row AS c_1
DO
v_user = cur_row.user;
ALTER USER v_user
SET PARAMETER
email address = cur_row.email;
END FOR;
END;
Unfortunately, this does not work. I get a syntax error for the ALTER USER line "USER is not supported".
This has left me baffeled. Any help will be much appreciated.
Cheers,
Martin
The ALTER statements cannot be run directly from within SQLScript.
To do that, you need to use dynamic SQL ( EXEC/EXECUTE IMMEDIATE).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lars,
I tried dynamic SQL and in the end I got it to work (see below). But I think, I don't like all those apostrophes and concatentations very much.
Is it possible to create another procedure to contain the ALTER USER bit and call the second procedure in the first one? I tried. But I got an error message. Somthing about DDL Statements not being supported.
Regards,
Martin
CREATE PROCEDURE user_email()
LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE v_email NVARCHAR(256) default '';
DECLARE v_user NVARCHAR(5000) default '';
DECLARE v_stmt NVARCHAR(500) default '';
DECLARE CURSOR c_1 FOR
SELECT user_name, email FROM MYSCHEMA.USER_EMAIL;
FOR cur_row AS c_1
DO
v_email := cur_row.email;
v_user := cur_row.user_name;
v_stmt := concat('ALTER USER ', :v_user);
v_stmt := concat(:v_stmt,' SET PARAMETER email address = ');
v_stmt := concat(:v_stmt, '''');
v_stmt := concat(:v_stmt, :v_email);
v_stmt := concat(:v_stmt, '''');
EXEC v_stmt;
END FOR;
END;
Hello
No need for separate concat statement, it can be done as one.
create procedure concat_demo()
language sqlscript as
begin
declare v_email NVARCHAR(256) default '';
declare v_user NVARCHAR(5000) default '';
declare v_stmt NVARCHAR(500) default '';
v_email := 'michael_eaton@somewhere.com';
v_user := 'michael_eaton';
v_stmt := 'alter user ' || v_user || ' set parameter email address = ''' || :v_email || '''';
select :v_stmt from dummy;
end;
The result being
alter user michael_eaton set parameter email address = 'michael_eaton@somewhere.com'
Michael
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.