CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
OpenStove
OpenStove

Reputation: 734

Variable in UPDATE oracle in procedure : invalid identifier

I don't understand why service is complaining with Fehler(36,11): PL/SQL: ORA-00904: "FOUND_VP": invalid identifier

Variable is declared in the first begin... Is it not possible to use variable directly in queries ?

when trying store following procedure :

create or replace PROCEDURE fpwl_update_vp(
     my_zn IN NUMBER, my_verwaltung IN VARCHAR2 , my_variante IN NUMBER, my_vp IN NUMBER 
   ) IS



BEGIN

DECLARE
search_VP IFT_INFO_LAUF.VP%TYPE;
found_VP IFT_INFO_LAUF.VP%TYPE;
INFOversion number := 25;


BEGIN -- search SYFA_VP

          SELECT SYFA_VP
          INTO found_VP
          FROM FPWL_VP_MAPPING
          WHERE INFO_VP=search_VP ;


          exception
          when no_data_found then
           dbms_output.put_line ('Kein SYFA VP : Importiere aus SYFA');
          --found_VP:=:=cus_info25.pa_info_data.fn_insert_syfa_vp(my_vp,25);

          WHEN OTHERS THEN
          ROLLBACK;
          RETURN;
END; -- SYFA VP


-- Update VP
UPDATE IFT_INFO_LAUF
 SET vp = found_VP
WHERE id_kopf IN 
  (SELECT id_kopf 
    FROM ift_info_kopf 
    WHERE fahrtnummer= my_zn  
    AND verwaltung= my_verwaltung
    AND variante = my_variante
  )
;


  --COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

END ;

Upvotes: 3

Views: 2097

Answers (3)

Colin 't Hart
Colin 't Hart

Reputation: 7729

Your problem is that found_VP is going out of scope.

Move the contents of the "DECLARE" block to just after the "IS":

create or replace PROCEDURE fpwl_update_vp(
 my_zn IN NUMBER, my_verwaltung IN VARCHAR2 , my_variante IN NUMBER, my_vp IN NUMBER 
) IS
  search_VP IFT_INFO_LAUF.VP%TYPE;
  found_VP IFT_INFO_LAUF.VP%TYPE;
  INFOversion number := 25;
BEGIN
  BEGIN -- search SYFA_VP

  etc

Upvotes: 5

Alex Poole
Alex Poole

Reputation: 191275

Since the error message refers to line 36 and the reference to found_VP in your code sample is on line 18, you've omitted the part of the code that actually has the problem.

It looks like you have a scope problem; you're declaring found_VP in an inner block (one level of DECLARE/BEGIN/END) and referring to it outside that block, either in the parent block or another one at the same level. The issue isn't where you're selecting into found_VP, it's (I think) that you're referring to it again later on, beyond the code you've posted, and therefore outside the block the variable is declared in.

To demonstrate, I'll declare l_name in an inner block, as you seem to have done:

create or replace procedure p42 is
begin
    declare
        l_name all_tables.table_name%TYPE;
    begin
        select table_name
        into l_name        -- this reference is OK
        from all_tables
        where table_name = 'EMPLOYEES';
    end;

    select table_name
    into l_name            -- this reference errors
    from all_tables
    where table_name = 'JOBS';
end;
/

Warning: Procedure created with compilation errors.

show errors

Errors for PROCEDURE P42:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/2     PL/SQL: SQL Statement ignored
13/7     PLS-00201: identifier 'L_NAME' must be declared
14/2     PL/SQL: ORA-00904: : invalid identifier

Notice that the error is reported against line 13, which is in the outer block; it doesn't complain about it in the inner block because it is in-scope there.

So, you need to declare the variable at the appropriate level. As Colin 't Hart says that is probably right at the top, between the IS and the first BEGIN, as that is the procedure-level DECLARE section (it doesn't need an explicit DECLARE keyword).

Upvotes: 0

Russell Gutierrez
Russell Gutierrez

Reputation: 1385

Make sure that

FPWL_VP_MAPPING.SYFA_VP

is the same type with

IFT_INFO_LAUF.VP

and make sure that

SELECT SYFA_VP INTO found_VP FROM FPWL_VP_MAPPING WHERE INFO_VP=search_VP ;

does not return multiple rows. But I doubt that is the case with the error that you have given.

Upvotes: 0

Related Questions