Oracle Pl/SQl: custom function with intermediate results

Question

I need to transform a string into a number using some oracle built-in methods (parsing, strlength, ...)
I don't know how to assign a value to a variable inside the method, and I don't know where to put the declare section.

CREATE OR REPLACE FUNCTION EXAMPLE (param IN VARCHAR2) 
RETURN NUMBER AS
BEGIN

  SELECT <string_handling_using_param> 
    INTO var 
    FROM DUAL;

  RETURN TO_NUMBER(<some computation using var>);

END EXAMPLE ;

I have tried some variation around this function, like adding an OUT parameter for storing the intermediate var, but then I can not call the function from regular SQL...

Any suggestion on how to achieve this ?

cagcowboy · Accepted Answer · 2011-02-22 16:46:28Z

4

If I understand correctly you just need to define the "var" variable...

create or replace FUNCTION EXAMPLE (param IN VARCHAR2)
RETURN NUMBER
AS
   var VARCHAR2(100);  -- This datatype may need modification
BEGIN
  select <string_handling>
  into   var
  from   dual;

  return to_number(<some computation using var>);
END EXAMPLE ;

Depending on exactly what you're doing, there may be a better approach that doesn't need the SELECT ... FROM DUAL:

create or replace FUNCTION EXAMPLE (param IN VARCHAR2)
RETURN NUMBER
AS
   var VARCHAR2(100);  -- This datatype may need modification
BEGIN
  var := <string_handling>;

  return to_number(<some computation using var>);
END EXAMPLE ;

answered Feb 22, 2011 at 16:46

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

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

2 Comments

Guillaume Over a year ago

You saved my life :) At least my afternoon. I did not found where to put the 'var VARCHAR2(100);' declaration. Thanks a lot ! I need the 'select from dual' because there is a decode() involved in the process.

Michael Broughton Over a year ago

Guillaume, while decode is problematic, bear in mind that in recent ORacle versions the CASE statement CAN be used in a simple statment and is more robust than DECODE(). var:=case when <cond> then <statement1> when <cond2> then <statement2> else <statment3> end;

Michael Broughton · Accepted Answer · 2011-02-22 16:47:35Z

1

create or replace FUNCTION EXAMPLE (param IN VARCHAR2) 
RETURN NUMBER AS 
    -- declare section between AS and BEGIN
    var varchar2(100);
BEGIN   
   select <string_handling> into var from dual;  
   -- also note that many built-in functions can be done directly
   -- without calling a select, so in many cases
   -- var := substr(param,1,10) <or some other string handling>;
   -- is perfectly acceptable too. 
   return to_number(<some computation using var>); 
EXCEPTION -- if you need an exception handler
   when value_error then
      <do something with it or set a default return value or whatever>
END EXAMPLE ;

answered Feb 22, 2011 at 16:47

Michael Broughton

4,05516 silver badges12 bronze badges

Collectives™ on Stack Overflow

Oracle Pl/SQl: custom function with intermediate results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related