Oracle SQL function / function execution giving error code 900 SQL Developer

Question

I have the following Function which I have compiled in SQL Developer without error on an Oracle 11gR2 DB. It's mostly just a test function which will be built upon later on but its purpose is to take in a text value and a category value where text might be Hello and category is French and the result should be Bonjour.

create or replace FUNCTION testTranslate
    (
      originaltext   IN VARCHAR2,
      fineoscategory IN VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2
  AS
    translatedttext VARCHAR2(255) := '';
  BEGIN
    SELECT distinct tti.translatedtex
    INTO translatedttext
    FROM toctranslationitem tti,
      todomaininstance tdi
    WHERE tti.translationty   = fineoscategory
    AND tti.originaltext    = originaltext;
    RETURN translatedttext;
  END;

I've never used SQL Developer before and I'm just getting to grips with creating functions so I'm not entirely sure how to run this.

I've tried the following:

testTranslate('Hello', 'French');

execute testTranslate('Hello', 'French');

Begin
    testTranslate('Hello', 'French');
end;

The first two give me Error 900 invalid SQL Statement and the third one gives me error 6550 testTranslate is not a procedure or is undefined

When I try to debug it in SQL developer I get a separate problem. The script seems to run but I get error message 1422 exact fetch returns more than the requested number of rows. The select statement within the function when run by itself without the distinct after the select returns quite a few values but with the distinct it just returns the one so not sure what this is giving out about.

Any pointers ?

Ankit Bajpai · Accepted Answer · 2014-06-05 09:00:05Z

1

In Oracle the functions executes with the select keyword. So try to execute the function like this:-

SELECT testTranslate('Decision', 'Text') FROM DUAL;

May be this can help you.

answered Jun 5, 2014 at 9:00

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

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

1 Comment

Predz Over a year ago

Both correct answers but you got there 1 min earlier :).

Jacob · Accepted Answer · 2014-06-05 08:59:50Z

1

Try as

SELECT testTranslate ('Hello', 'French') FROM DUAL;

answered Jun 5, 2014 at 8:59

Jacob

14.8k69 gold badges215 silver badges323 bronze badges

Collectives™ on Stack Overflow

Oracle SQL function / function execution giving error code 900 SQL Developer

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related