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.
• 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.

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