Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Equivalents To SQL Server's STUFF() Function

Ray WinkelmanJul 28 2016 — edited Jul 28 2016

Hey Guys!

Let's have some fun with strings today.

So, I've got some T-SQL code I'm translating into PL-SQL, and I've been encountering some uses of SQL Server's STUFF() function. Microsoft's documentation on the STUFF() function states:

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

The header of the STUFF() function accepts the following arguments:

STUFF ( character_expression , start , length , replaceWith_expression )


So, considering the following code as our example, how can we manipulate a string in PL-SQL with as few calls to system functions as possible? Let's aim for the most performant solution - as always.

select stuff( 


( 


    select '123456789' 


    from   table_name for clob path('')


), 1, 4, ''


) as example 


from dual;

And, GO!

This post has been answered by Paulzip on Jul 28 2016
Jump to Answer

Comments

EdStevens

You don't give much to go on. Please copy and paste the text of the entire statement/procedure/script. Please explain in detail how it is being submitted.

User_DDYV1

Due to security issues, I may not be able to copy the entire scripts here. But I'm trying to run a merge statement which is something like below. Usually this statement takes a minute to run and merge the data into the dimension table, but every first of month it runs forever and hangs there.
merge into table d using
( select * from (
select key , id , id1 , sysdate , row_number() over (partition by key order by v.id) as row_num
from f_transaction f,
(select ID from f_import v where date = 'Today'sDate' ) v
where f.id = v.id
and f.date = 'today's date' )
where row_num = 1 ) x
on (d_key = x.key
when matched then update set
d.id = x.id
, d.dw_update_date = x.dw_update_date
when not matched then
insert ( id , Key, Date)
values ( x.id , x.key , x.date);

When running this merge statement in toad, it executes till 23 seconds, then it stays there and stops counting. Toad then get hanged. I checked in the session monitor for any locks and made sure no other jobs are running parallelly.
So need some help here to know how to troubleshoot this.

JohnWatson2

And if you run the statement from SQL*Plus?

tsangsir

And are the numbers of rows inf_transaction and f_import more or less the same throughout the month?

User_DDYV1

@johnwatson2 , The same issue with SQL plus as well.
@tsangsir , the rows gets added each day , so these two tables keeps growing. These two tables already have millions of records, we do have index on these tables as well.

JohnWatson2

JohnWatson2 , The same issue with SQL plus as well.
Do you mean that it runs to completion in 23 seconds? Or do you mean that it hangs forever?
A simple way to gather a bit more information, in SQL*Plus, would be to

set timing on
set autotrace on
merge into......

and copy/paste it all here.

EdStevens

and copy/paste it all here.
And please be sure to post it as code-formatted

User_DDYV1

@johnwatson2 , @edstevens1 Thanks , when I run the same query in SQL plus, it hangs there as well.
the thing is, I ran it today, the same merge statement and I got the result in 23 seconds. But I'm facing this issue only during the first of month (Every time).

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 25 2016
Added on Jul 28 2016
#code, #migration, #performance, #pl-sql, #plsql
22 comments
17,521 views