Hello this is my first time creating a procedure but I keep getting the SQL STATEMENT IGNORED error.-CodePudding

My Procedure

The errors I am getting.

CodePudding user response：

Check your schema.table_name. On your insert statement, you have MEMBER_ONLY yet on your update, you have MEMBERS_ONLY (Plural).

Also, if I am not mistaken the top part is also incorrect.

CREATE PROCEDURE procedurename ( @paramname int )

CodePudding user response：

This is, obviously, Oracle.

Error stack points to exact error place, e.g.

Error(22,29): PL/SQL: ORA-00942: table or view does not exist     
      -- --
      ^   column 29
      |
      line 22

It would be easier to spot it if you chose to display line numbers in SQL Developer (do so; right-click the left margin and set it). I'd say that it is about member_system.member table. Another one, in the same from clause, is prospect_staging.magi_applicant.

It is unclear which user you're connected to (one, or none of these), but - comment you posted in deleted answer:

It works in the workbench but when I place it in the procedure it says the table or view isn't created

suggests that you might have got access to the table via role (and not directly to your user). Why? Because privileges acquired via roles work at SQL level or in anonymous PL/SQL blocks, but won't work in named PL/SQL procedures or functions - and that's what you have, a procedure named get_magi_applicant_data.

So, what to do? Grant privileges directly.

As of another error you got:

Error(31,9): PLS-00201: identifier 'V_INSERT_OR_UPDATE' must be declared

Looks like it is about if v_insert_or_update is null then line. Error isn't obvious; there is v_insert_or_update local variable declared in the procedure, so I can't guess what might be wrong here.