call function from sql script in oracle

Question

In this code I am calling this FSG.REPLACE_STRING function which has 2 parameters, original string and special characters string. The original string is a select query from a table and special character string is 'A'.

I have written the code:

FSG.REPLACE_STRING ( (SELECT CAST(NVL(PRAD_ID            , ' ') AS CHAR(12))
FROM FSG_WRK.FSG_PRCB_AUXDB_PRAD WHERE PRAD_ID= '003204091007'), A );

but this is not working.

what do you have as the output of running script?

severus256
– severus256

2018-09-27 08:11:36 +00:00
Commented Sep 27, 2018 at 8:11 — severus256
– severus256, Commented Sep 27, 2018 at 8:11

Alex Poole · Accepted Answer · 2018-09-27 08:11:29Z

2

You are trying to pass the table column value into the function, so you need to restructure your statement:

SELECT FSG.REPLACE_STRING (CAST(NVL(PRAD_ID, ' ') AS CHAR(12)), 'A')
FROM FSG_WRK.FSG_PRCB_AUXDB_PRAD
WHERE PRAD_ID= '003204091007';

Although the NVL() part seems a bit pointless if you're filtering for a specific (not-null) value in the query. Casting to char looks suspicious too.

answered Sep 27, 2018 at 8:11

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Neha Singh Over a year ago

I used this , but I am getting error ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "FSG.REPLACE_STRING", line 36 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: *Action: I am selecting 20 columns from this select query. Its an exiting code so I am not touching the select statement .

Alex Poole Over a year ago

That's coming from inside your function, and we don't know what that is doing.

Diego Souza Over a year ago

if you're setting the value in the join, you don't need the NVL function

Collectives™ on Stack Overflow

call function from sql script in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related