Difficulty creating Function in oracle

Question

Difficulty creating Function in oracle

Asked 5 years, 4 months ago

Viewed 445 times

1

I’m trying to create a function in the oracle, but I’m having a hard time creating her return.

CREATE OR REPLACE function simple_function(
     state             VARCHAR2,
     city_id           NUMBER)
RETURN VARCHAR2 
IS ret VARCHAR2(255);
BEGIN
    SELECT 'XXX ' || state || ' - ' || city_id INTO ret FROM DUAL;
    RETURN ret;
END;

When I try the simplified version, it works

CREATE OR REPLACE function simple_function(
     state             VARCHAR2,
     city_id           NUMBER)
RETURN VARCHAR2 
BEGIN
    RETURN 'That''s All Folks!';
END;

I saw some documentations, and I don’t know what I’m doing wrong about the return variable. There are some similar functions in the system, and they work, but the return is NUMBER, What I need is a return VARCHAR2, I don’t know if there’s any differential to this case.

In the parameter you are passing state and this using the variable Uf, which is not declared.

– Confundir

2019/02/15 at 16:43
@Confuse Sorry, I was translating to post on stackoverflow, but I thought it would be simpler if you post here. the nomenclature is the same in my example. If you run the example, with the same variables, the error will be the same.

– Michel Ayres

2019/02/15 at 16:53

1 answer

Browser other questions tagged sql oracle function pl-sql

You are not signed in. Login or sign up in order to post.

by Sorack • **27,430** points · Answer 1 · 2019-02-15T18:52:24+00:00

When you create a function on Oracle you should inform if the parameter is input or output. In your example it would look like this:

CREATE OR REPLACE function simple_function(
   state IN VARCHAR2,
   city_id IN NUMBER
  )
RETURN VARCHAR2 
AS ret VARCHAR2(255);
BEGIN
  SELECT 'XXX ' || state || ' - ' || TO_CHAR(city_id)
    INTO ret
    FROM DUAL;

  RETURN ret;
END;

Functions

When you create a Procedure or Function, you may define Parameters. There are three types of Parameters that can be declared:

IN - The Parameter can be referenced by the Procedure or Function. The value of the Parameter can not be overwritten by the Procedure or Function.

OUT - The Parameter can not be referenced by the Procedure or Function, but the value of the Parameter can be overwritten by the Procedure or Function.

IN OUT - The Parameter can be referenced by the Procedure or Function and the value of the Parameter can be overwritten by the Procedure or Function.

In free translation:

When you create a precedent or function, you can define parameters. There are three types of parameters that can be declared:

IN - The parameter can be referenced by the Procedure or Function. The value of the parameter cannot be overridden by the Procedure or Function.

OUT - The parameter cannot be referenced by the Procedure or Function, but the value of the parameter can be overridden by the Procedure or Function.

IN OUT - The parameter can be referenced by the Procedure or Function and the value of the parameter can be overridden by the Procedure or Function.