d-_-b
d-_-b

Reputation: 4502

Oracle 12c: ORA-06575 Package or function name_of_func is in an invalid state

I am trying to create a function that takes an input(character data) and replaces a set of specific characters.
The function I have created so far is the following

CREATE FUNCTION name_of_func(input VARCHAR(4000)
  RETURN VARCHAR(4000) IS
  BEGIN
    return replace(replace(replace(replace(replace(input, '\', '\\'), CHR(10), '\n'), CHR(13) || CHR(10), '\n'), CHR(13), '\n'),'"', '\"');
  END name_of_func;

How can I get this function to be compiled?

Upvotes: 0

Views: 328

Answers (2)

Aleksej
Aleksej

Reputation: 22949

You can't specify the size of parameters in the function definition:

SQL> CREATE OR REPLACE FUNCTION name_of_func(input VARCHAR)
  2    RETURN VARCHAR IS
  3    BEGIN
  4      return replace(replace(replace(replace(replace(input, '\', '\\'), CHR(10), '\n'), CHR(13) || CHR(10), '\n'), CHR(13), '\n'),'"', '\"');
  5    END name_of_func;
  6  /

Function created.

SQL> select name_of_func('dfghjk') from dual;

NAME_OF_FUNC('DFGHJK')
--------------------------------------------------------------------------------
dfghjk

SQL>

Upvotes: 1

MT0
MT0

Reputation: 167972

You cannot specify variable sizes in the function declaration, also, you need to swap the order of the replacements so that \r\n is replaced before \n or \r are handled individually.

CREATE FUNCTION name_of_func(
  input VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
  return replace(
           replace(
             replace(
               replace(
                 replace( input, '\', '\\'),
                 CHR(13)||CHR(10),
                 '\n'
               ),
               CHR(10),
               '\n'
             ),
             CHR(13),
             '\n'
           ),
           '"',
           '\"'
         );
END name_of_func;

Upvotes: 1

Related Questions