Of Interest
Business Intelligence and Analytics
Oracle Training
|
Oracle
»
PL-SQL
»
Coding
»
Loops in PL-SQL : Hierarchy
PL/SQL Script for performing repetitive jobs
:
Hierarchical loop constructs in PL/SQL
--PL/SQL Script for performing repetitive jobs
--------------------------------------------
--Author JP Vijaykumar
--Date Oct 17 2007
--------------------------------------------
set serverout on size 1000000
declare
v_word varchar2(4000):='abc xyz';
/*********************aaaaaaaaaaaaaaaaaaaaaaaaaaa**************************************/
/********************HERE I SUPPLY THE VALUES FOR v_word VARIABLE**********************/
/*********************aaaaaaaaaaaaaaaaaaaaaaaaaaa**************************************/
v_num number:=0;
v_len number:=0;
v_inc number:=0;
v_str varchar2(100);
v_sql varchar2(1000);
v_cnt number;
begin
select length(v_word) into v_len from dual;
while ( v_num <= v_len ) loop
v_sql:='select instr(substr('||chr(39)||v_word||chr(39)||', ( '||v_num||' + 1 ),'||v_len||'),'||chr(39)||' '||chr(39)||') from dual';
execute immediate v_sql into v_inc;
if ( v_inc = 0 ) then
v_inc := v_len - v_num + 1;
end if;
v_sql:='select upper(substr('||chr(39)||v_word||chr(39)||', ( '||v_num||' + 1 ),( '||v_inc||' - 1 ))) from dual';
execute immediate v_sql into v_str;
/***********************bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb************************************/
/******************************* HERE CODE STARTS TO PARSE THE STRING ******************/
/***********************bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb************************************/
begin
dbms_output.put_line(v_str);
exception
when others then
dbms_output.put_line(v_str||' '||sqlerrm);
end;
/***********************cccccccccccccccccccccccccccccc*************************************/
/******************************** HERE CODE ENDS TO PARSE THE STRING *********************/
/***********************cccccccccccccccccccccccccccccc*************************************/
v_num := v_num + v_inc;
end loop;
end;
ABC
XYZ
PL/SQL procedure successfully completed.
When I want to create 20 users in a database, grant them create session and SCOTT_READER role
in the database. Most of the time, I have to manually create all the users, grant each user
create session and grant each user with SCOTT_READER role. First I have to create 10 times 3
is 30 statements and then execute them in the database. How can I execute all these statements
through a loop like construct. This is my string parser, that parses each and every word
supplied at "aaaaaaaaaaaaaaaaaaaaa" for v_word variable. Then I submit the parsed v_str with
all the commands I want to execute between "bbbbbbbbbbbbbbbbbb" and "cccccccccccccccc" after
the dbms_output.put_line command. My job of creating is taken care. Let us see how the script
looks like after modification:
set serverout on size 1000000
declare
v_word varchar2(4000):='jp1 jp2 jp3 ...................... jp20';
/*********************aaaaaaaaaaaaaaaaaaaaaaaaaaa**************************************/
/********************HERE I SUPPLY THE VALUES FOR v_word VARIABLE**********************/
/*********************aaaaaaaaaaaaaaaaaaaaaaaaaaa**************************************/
v_num number:=0;
v_len number:=0;
v_inc number:=0;
v_str varchar2(100);
v_sql varchar2(1000);
v_cnt number;
begin
select length(v_word) into v_len from dual;
while ( v_num <= v_len ) loop
v_sql:='select instr(substr('||chr(39)||v_word||chr(39)||', ( '||v_num||' + 1 ),'||v_len||'),'||chr(39)||' '||chr(39)||') from dual';
execute immediate v_sql into v_inc;
if ( v_inc = 0 ) then
v_inc := v_len - v_num + 1;
end if;
v_sql:='select upper(substr('||chr(39)||v_word||chr(39)||', ( '||v_num||' + 1 ),( '||v_inc||' - 1 ))) from dual';
execute immediate v_sql into v_str;
/***********************bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb************************************/
/******************************* HERE CODE STARTS TO PARSE THE STRING ******************/
/***********************bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb************************************/
begin
dbms_output.put_line(v_str);
execute immediate 'create user '||v_str||' identified by abc123 account unlock password expire';
execute immediate 'grant create session to '||v_str;
execute immediate 'grant scott_reader to '||v_str;
exception
when others then
dbms_output.put_line(v_str||' '||sqlerrm);
end;
/***********************cccccccccccccccccccccccccccccc*************************************/
/******************************** HERE CODE ENDS TO PARSE THE STRING *********************/
/***********************cccccccccccccccccccccccccccccc*************************************/
v_num := v_num + v_inc;
end loop;
end;
|
|
|
Get the latest Rocket99 news and tech tips via
|
