I want to create an array in sql with user ids, and create a loop which will pick user id from array

Question

I want to create an array in sql with user ids, and create a loop which will pick user id from array. And create a user in sql db.

Please find the code below. It is not working as expected :

declare type namesarray IS VARARRAY (50) OF VARCHAR2 (10);
declare name namesarray;
declare total integer;

names = namesarray('resh1','resh2');
total = names.count;

while (i <= total)
begin
    EXECUTE IMMEDIATE 'create user names(i) identified by newpass01;
    dbms_output.put_line("created user 'names(i)");
end

dbms_output.put_line("cannot create user 'names(i)");

I am creating a .sql file and writing this inside it. it is sql server — reshmavishnudev
– reshmavishnudev, Commented Jul 19, 2018 at 6:23

Priyank · Accepted Answer · 2018-07-19 06:54:55Z

0

In Sql Server , it is done as below. You can use table variable.

Declare @namesarray table
     (name  varchar(50));

Declare @Name varchar(50);

Insert @namesarray values ('resh1');
Insert @namesarray values ('resh2');

Declare name_cur CURSOR FOR
Select name from  @namesarray ;

OPEN name_cur
    FETCH NEXT FROM name_cur INTO @Name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            Print @Name
        FETCH NEXT FROM name_cur INTO @Name
        END
    CLOSE name_cur
DEALLOCATE name_cur

answered Jul 19, 2018 at 6:54

Priyank

1,38410 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

reshmavishnudev Over a year ago

so is it like i cant just create a array variable and pick from it? Am sorry for asking, but am new to sql.

Priyank Over a year ago

No in Microsoft Sql Server, array type is not there.

Priyank Over a year ago

Your question was specific to SQL SERVER, tagged so also,So i gave SQL SERVER specific solution. With same logical approch same can be achived in my sql or oracle database.

Collectives™ on Stack Overflow

I want to create an array in sql with user ids, and create a loop which will pick user id from array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related