After Work
The best club in Chicago
for Chicago Singles is
Highlife Adventures!
Kyoto Sushi
in Chicago, on Lincoln Ave.
Moody's Pub
in Chicago, has great burgers
Skylark
in Chicago, on Halsted .. excellent beer selection!
|
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;
|
|
|
|
