Oracle begin expected got create

Question

I'm writing a PL/SQL program, I've created a procedure and the syntax is correct. Running this on DataGrip. `

declare
create or replace procedure salutation(x OUT number) is begin
    x:= x*10;
end salutation;
begin
    SYS.DBMS_OUTPUT.PUT_LINE('hello');
end;

` I get error messages when I execute the code: BEGIN expected, got 'create'. [2022-12-04 23:58:09] [65000][6550] [2022-12-04 23:58:09] ORA-06550: line 1, column 7: [2022-12-04 23:58:09] PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: [2022-12-04 23:58:09] begin function pragma procedure subtype type [2022-12-04 23:58:09] current cursor delete [2022-12-04 23:58:09] exists prior

I don't think there's a problem with the syntax. Also why does the DataGrip not allow DBMS_OUTPUT.PUT_LINE without the SYS. ? even though I've enabled the DBMSOUTPUT.

Answer 1

You can't have static DDL statements (like create procedure) within PL/SQL (you'd need to use dynamic SQL, but it's very rarely necessary anyway).

But if you're trying to declare a local procedure within your anonymous block - not create a permanent, stored procedure, then you don't need the create part:

declare
  y number := 42;

  procedure salutation(x IN OUT number) is begin
    x:= x*10;
  end salutation;
begin
    SYS.DBMS_OUTPUT.PUT_LINE('hello');
    -- call salutation here if you want...
    salutation(y);
    dbms_output.put_line(to_char(y));
end;
/

1 rows affected

dbms_output:
hello
420

fiddle

Note that I changed the argument to IN OUT - otherwise it would always be reset to null.

If you want to create a permanent stored procedure then do that separately, before you try to run your anonymous block:

create or replace procedure salutation(x IN OUT number) is begin
  x:= x*10;
end salutation;
/

declare
  y number := 42;
begin
    SYS.DBMS_OUTPUT.PUT_LINE('hello');
    -- call salutation here if you want...
    salutation(y);
    dbms_output.put_line(to_char(y));
end;
/

1 rows affected

dbms_output:
hello
420

fiddle

Also why does the DataGrip not allow DBMS_OUTPUT.PUT_LINE without the SYS. ?

That suggests your database is missing a public synonym for the package; not a DataGrip thing, you'd see the same behaviour using any client. You'd need to ask your DBA why it's missing and whether it can be reinstated. (I haven't included the schema prefix in the extra calls I added, but if those don't work for you then you'll need to add it.)