Hello Oracle Gurus's

-Required simple and efficient solution

I want to replace part of the string on some conditions.
here is the small pl/SQL code. Oracle 8,9,10G any one is OK
but, i will try to avoid owa_text package.
(Please pardon me for Not testing under PL/SQL )

Algorithm goes like

1.Here i will read a record from the Database

2.one rows of data is assigned to td_rec;
declare outputstr varchar2(100);



3. Here i am replacing a string
any one possibilities of replace will be true

begin
outputstr:=td_rec.colstr
outputstr=REPLACE(td_rec.colstr,'TOKYO','JAPAN');
outputstr=REPLACE(td_rec.colstr,'DELHI','INDIA');
outputstr=REPLACE(td_rec.colstr,'KPOOR','MALAYSIA' );
outputstr=REPLACE(td_rec.colstr,'PEEKING','CHINA') ;
outputstr=REPLACE(td_rec.colstr,'ISMALABADH','PALI STAN');

etc.............

outputstr=REPLACE(td_rec.colstr,'NEWYORK','USA');
end;
if there is no match then keep the original string as it is

4.One way of the solution will be
just keep the beg----end block of step 3 as it is .

If i take this approach all the lines in the beg----end block of step
3 will be checked and its waste of Resources. and i do not want use IF
ELSE clause.

Any one have better solution to this problem,

Thanks in Advance

In article <1118735821.222784.117370@o13g2000cwo.googlegroups .com>, baka
says...
> If i take this approach all the lines in the beg----end block of step
> 3 will be checked and its waste of Resources. and i do not want use IF
> ELSE clause.
>
> Any one have better solution to this problem,


I don't but I don't think that what you have coded is an unreasonable
way of handling the problem.

However (and this not solving your problem in a different way) I would
suggest that the CITY/COUNTY pairs be loaded into a table and then read
into an array at the start of execution. Then instead of your multiple
replace statments just have one within a loop such as:

for i in 1..cities.count
outputstr=REPLACE(td_rec.colstr,city(i),country(i) );
end loop;

Another potential problem springs to mind actually - you would need to
ensure that the string you are seeking to replace doesn't exist as part
of another word or elsewhere in the data....


--

jeremy

Thanks Jeremay san ,

It seems your approach is very very good and for better than mine and
it is enhanceable for the future.

still looking for some more alternatives (pardon me for this)

BTW
I am sure the searching string occur only once at a given record.

--Tokyo' weather is cloudy today.

baka wrote:

> -Required simple and efficient solution
> I want to replace part of the string on some conditions.

Is this a comlete string evaluation, or dealing with token replacement
in a string? The pseudo code you posted points to the former... If that
is the case, I would not even bother doing it in PL/SQL. I would use
SQL, a lookup table (containing the matching string and replacement
string), and a join.

If this is token replacement, I would also first consider using SQL -
with a LIKE condition as oppose to a normal join.

The reason why I want to try SQL first is that it simply deals a lot
better with data volumes than PL/SQL. It performs much faster, scales
vastly better, than doing similar in PL/SQL.


--
Billy

Billy のメッセージ:

> Is this a comlete string evaluation, or dealing with token replacement
> in a string? The pseudo code you posted points to the former...
> ZAP.......some part
> --
> Billy

Thanks for the reply Billy san,
The whole algorithm will look like this
and the example what i have given not= my requirement but to make it
simple
i given that eg.,.

here we go

1.There is csv data (Data-ware house kind ...) ---EXTRACT PHASE
2.Load that data into raw table using SQL*LOADER -- Bcos FAST
3. Read that table and link break some column,make some column, replace
some column,get some info from other table.
if not exists put some default value. do some more for each record
---Transform PHASE
4. Then insert the modified data to new table. --LOAD PHASE

I hope now the algorithm is clear

Thanks for taking this query

Whe don't u use regular expressions?
in oracle 9 you can access them via owa_utils.pattern.
I believe (but not certain) they will be nativily implemented in 10

baka wrote:

> 1.There is csv data (Data-ware house kind ...) ---EXTRACT PHASE
> 2.Load that data into raw table using SQL*LOADER -- Bcos FAST
> 3. Read that table and link break some column,make some column, replace
> some column,get some info from other table.
> if not exists put some default value. do some more for each record
> ---Transform PHASE
> 4. Then insert the modified data to new table. --LOAD PHASE

Why not see how well this works using EXTERNAL TABLES and a pipelined
table function to do the transformation?

Instead of SQL*Load'ing into a table and then hitting that table to
process the data, these two serial processes can effectively run
concurrently.

Something like this:
INSERT /*+ APPEND*/ INTO target
SELECT * FROM TABLE( pipeline( CURSOR(SELECT * FROM externaltable)) )

The APPEND hint provides a direct path load. The CURSOR provides the
CSV input into a pipeline table function (a PL/SQL function) that
transforms a CSV row into a target row. The CSV data is loaded using an
external table (which is still SQL*Loader under the hood).

I would also enable parallel DML - as the pipeline table function can
be run in parallel and so too (I assume) the SQL*Load via external
table as SQL*Load itself support parallel processing. If the complete
process can be parallelised, performance gains should be very
noticable.

--
Billy