0

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

coding monster
  • 384
  • 4
  • 18
  • 2
    Your first query's *from_clause* is syntactically incorrect. It should be `FROM VW_OUT_USER T` and not `FROM VW_OUT_USER AS T`. – Jeff Holt Jan 30 '22 at 14:28
  • The errors you show are not complete. They should include a line *and* column number that point you *precisely* at the word `AS` in that statement. – Jeff Holt Jan 30 '22 at 14:35
  • Your use of double quotes is redundant because the procedure name, without the double quotes, is translated to uppercase. – Jeff Holt Jan 30 '22 at 14:37
  • @JeffHolt I executes the query with Navicat, and I couldn't see any _line and column number_ for error message. Is there any other way ? – coding monster Jan 30 '22 at 14:41
  • Then query `USER_ERRORS`, which is what the sqlplus `show errors` command does or what sqldeveloper does by default. Use the sharpest tool in the shed and you actually willl *not* cut yourself. – Jeff Holt Jan 30 '22 at 14:42
  • @JeffHolt I've just realized that `AS` for table alias is invalid in oracle. :) It was the solution. Thanks. But when I query `USER_ERRORS` it says `ORA-00900: invalid SQL statement` what is the alternative query for detailed errors? – coding monster Jan 30 '22 at 15:13

1 Answers1

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57