Viewing detailed info for Stored Procedure compilation error(PL/SQL: ORA-00933)

Question

This procedure is supposed to copy user info from one table to another. It is executed with spring mybatis, and the spring retrieves some results by procedure's parameters.

There is a compilation error at SELECT ... INTO ... statement.

PL/SQL: SQL Statement ignored, and PL/SQL: ORA-00933: SQL command not properly ended

The create procedure command is:

CREATE OR REPLACE PROCEDURE "SP_MIGRATE_USER" (
    p_ID OUT NUMBER,
    p_USERNAME OUT VARCHAR2,            -- `p_USERNAME OUT VW_OUT_USER.USERNAME%TYPE,` also same error
    p_REG_DATE OUT DATE,
    p_USER_ID IN NUMBER
)
AS
BEGIN
    SELECT T.USERNAME
    INTO p_USERNAME                     -- PL/SQL: SQL Statement ingored
    FROM VW_OUT_USER AS T
    WHERE T.ID = p_USER_ID;             -- PL/SQL: ORA-00933: SQL command not properly ended

    SELECT SEQ_TB_USER.NEXTVAL, SYSDATE
    INTO p_ID, p_REG_DATE
    FROM DUAL;

    INSERT INTO TB_USER (
        ID
        , USERNAME
        , REG_DATE
        , EXT_USER_ID
    ) VALUES (
        p_ID
        , p_USERNAME 
        , p_REG_DATE
        , P_USER_ID
    );
    
END;

I searched but couldn't find an answer.

The Oracle version is Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Answer 1

Although you found the culprit, this is related to comment you posted about viewing detailed errors.

This is an example which simulates what you did - used as with a table alias:

SQL> create or replace procedure p_test as
  2    l_cnt number;
  3  begin
  4    select count(*)
  5      into l_cnt
  6      from emp as e;
  7  end;
  8  /

Warning: Procedure created with compilation errors.

If you used SQL*Plus (like I did), you could simply type show err:

SQL> show err
Errors for PROCEDURE P_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
6/14     PL/SQL: ORA-00933: SQL command not properly ended

It says that you should look at line #6, position (column) #14:

3  begin
<snip>
6      from emp as e;      --> line 6
   12345678901234
                ^
                |
              position 14 - as you can see, it points to "as"

If tool you use doesn't support such a command, you can always query user_errors and get the same information:

SQL> select line, position, text
  2  from user_errors
  3  where name = 'P_TEST'
  4  order by line, position;

      LINE   POSITION TEXT
---------- ---------- -------------------------------------------------------
         4          3 PL/SQL: SQL Statement ignored
         6         14 PL/SQL: ORA-00933: SQL command not properly ended

SQL>