Error "Encountered the symbol "IN" when expecting one of the following" in Oracl-CodePudding

This is the code:

CREATE PROCEDURE print_string(IN input_string VARCHAR(255))

BEGIN

DECLARE num_chars INT DEFAULT 0;

IF input_string IS NULL THEN

SET num_chars = 0;

ELSE

SET num_chars = CHAR_LENGTH(input_string);

END IF;

SELECT UPPER(input_string), num_chars;

END;

I get error:

PLS-00103: Encountered the symbol "IN" when expecting one of the following:     <an identifier> <a double-quoted delimited-identifier>
current delete exists prior
Errors: check compiler log

How do I fix: current delete exists prior?

CodePudding user response：

The immediate error is that you have the argument name and mode the wrong way around - it should be (input_string IN ... not (IN input_string .... But there are other problems:

Oracle recommends VARCHAR2 over VARCHAR.
arguments just have the data type, not a size (or precision/scale), so it should be (input_string IN VARCHAR2) not (input_string IN VARCHAR2(255).
you are missing the IS/AS keyword.
DECLARE comes before BEGIN in a PL/SQL block; having a nested block here would be valid, but you're missing a BEGIN and END; if you do that, and it isn't necessary so I don't think it's what you meant. And you don't need the DECLARE at all for a procedure, it's implied.
if you want a default value for a PL/SQL variable then assign it, rather than using DEFAULT. (You don't really need to do this here, as you always assign a value later anyway, but I'm sticking with your general approach.)
it's probably better to use native Oracle types, so NUMBER or PLS_INTEGER instead of INT.
assignment of values is with :=, not SET ... = ....
CHAR_LENGTH should just be LENGTH (unless you have your own function with that name).
in PL/SQL you have to select into something, and from something. But if you do that here, you still have to return it to the caller somehow.
given that you want to 'print' the string, you probably want dbms_output - though that relies on the client showing the result, which most don't by default, and it's generally only used for debugging...

So this would work:

CREATE PROCEDURE print_string(input_string IN VARCHAR2) AS
  num_chars PLS_INTEGER := 0;
BEGIN
  IF input_string IS NULL THEN
    num_chars := 0;
  ELSE
    num_chars := LENGTH(input_string);
  END IF;

  DBMS_OUTPUT.PUT_LINE(UPPER(input_string) || ': ' || num_chars);
END;
/

BEGIN
  DBMS_OUTPUT.ENABLE;
  print_string('This is a test');
END;
/

1 rows affected

dbms_output:
THIS IS A TEST: 14

fiddle

But again, dbms_output isn't ideal. And it could be done much more simply (@Mto has shown one way), or without using PL/SQL at all.

CodePudding user response：

You can fix the issues (listing in @Alex Poole's answer) and simplify the procedure to:

CREATE PROCEDURE print_string(
  input_string IN VARCHAR2
)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(UPPER(input_string) || ', ' || COALESCE(LENGTH(input_string), 0));
END;
/

Then:

BEGIN
  DBMS_OUTPUT.ENABLE;
  print_string('This is a test');
  print_string(NULL);
END;
/

Outputs:

THIS IS A TEST, 14
, 0

fiddle

CodePudding user response：

The code syntax is incorrect here. It should be something like

CREATE OR REPLACE PROCEDURE print_string(input_string IN VARCHAR2) 
IS
BEGIN