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

Kamal Kishore

Try adding the PRAGMA to the function.

FUNCTION function_name(...) RETURN VARCHAR2 ;
    PRAGMA RESTRICT_REFERENCES(function_name, WNDS, RNDS, WNPS,RNPS);

APC

There are a number of things it could be.

My #1 candidate is, we can only use functions in SQL statements that are public i.e. declared in a package spec. This is the case even for SQL statements executed within the same Package Body. The reson is that SQL statements are executed by a different engine which can only see publicly declared functions.

Cheers, APC

32685

Hi Andrew

That's exactly it! I moved the functions back to the original package, published them in the spec and it worked. Excellent!

Cheers

David

446518

Great!!

Helped me a lot

Though the message - function XX may not be used in SQL, is not relevent.

Krishna

564378

Wow, that cost me some time.
Thanks a lot, it saved my day.

450441

My god, users who search the forum before posting.

Surely it's a sign of the End of Days.

Amritpal

Thanks a lot, I just Googled the error PLS-00231, the very first link brought me here, and you were spot on. Declare the functions in the Package Spec to make them Global, and that solved the issue.

Thanks.
Amrit

659130

I also have this problem.

Making the function public helps ... but I can not make the function public. This violates the design.

The PRAGMA also says that it must be right behind the function definition AND in the package specification.

Is there a other - a private - way?

(Oracle 8i / PLSQL 8.1.7.4.0)

I also can not split the call from the SQL Statement, because it is part of a cursor loop:

FOR a IN (SELECT asd,
                         asaa,
                         asdasda,
                         h.sssde,
                         HLP_FNC_GET_BLABLUBB(j.adasdassss) dasdasssss
                  FROM   T_fhexxxsadfast h,
                         t_FFasee112w_SA   j
                  WHERE  h.soasde = j.soasde) 
        LOOP

anonymized code.

Edited by: user5828099 on 10.09.2008 07:57

Edited by: user5828099 on 10.09.2008 08:02

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 25 2016

Added on Jul 28 2016

#code, #migration, #performance, #pl-sql, #plsql

22 comments

17,566 views

SQL & PL/SQL

Equivalents To SQL Server's STUFF() Function

Comments

Post Details