Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




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;









Oracle : Related Topics

Oracle : PL-SQL : Creating a stored procedure
Oracle : PL-SQL : Displaying output
Oracle : PL-SQL : Output variables
Oracle : PL-SQL : Arrays and structures
Oracle : PL-SQL : Conditionals
Oracle : PL-SQL : Looping in PL-SQL
Oracle : PL-SQL : Packages
Oracle : PL-SQL : Exception Handling
Oracle : PL-SQL : Sleep and Wait
Oracle : PL-SQL : Date Manipulation
Oracle : PL-SQL : Parallel Query Processing
Oracle : PL-SQL : Exit if error, in a SQL script
Oracle : PL-SQL : Cursors
Oracle : PL-SQL : Complex IF/THEN Processing
Oracle : PL-SQL : Creating triggers
Oracle : PL-SQL : Ranking Data
Oracle : PL-SQL : How to do an outer join in Oracle

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.