sql error generated

0
votes

Erreur Begin at line: 254 of the command -

Error Repport :

ORA-06550: Ligne 2, colonne 21 : PLS-00103: Symbole "DECLARE" rencontré à la place d'un des symboles suivants :

begin function pragma procedure subtype type
current cursor delete exists prior Symbole "begin" a été substitué à "DECLARE" pour continuer. ORA-06550: Ligne 66, colonne 34 : PLS-00103: Symbole "(" rencontré à la place d'un des symboles suivants :

ORA-06550: Ligne 68, colonne 34 : PLS-00103: Symbole "(" rencontré à la place d'un des symboles suivants :

ORA-06550: Ligne 71, colonne 27 : PLS-00103: Symbole "end-of-file" rencontré à la place d'un des symboles suivants :

( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

3
sqloracleplsqloracle-sqldeveloper
you have to open the cursor then loop then inside theloop you open the second cursor and loop, by the way you are using oracle it should be number not numeirc and varchar2 not varchar – Moudiz
Why are you doing this? You should be writing a single SELECT statement that gets the rows you are after, and then you can use that in an INSERT statement. Far, far more efficient and performant than looping through two cursors and doing a row-by-row (aka slow-by-slow) insert. – Boneist
@Boneist - good point, well made. This looks like an attempt to port SQL Server code to Oracle with minimal changes. MSSQL is another country, they do things differently there ;-) – APC

3 Answers

0
votes

You can't open a CURSOR in declaration part, the error belongs to that. You don't need to use DECLARE for each declaration, it should be only once per block. Here I moved those cursor OPEN in execution part it should work now.

DECLARE
CUR_ID_AUTORISATION NUMERIC;
CUR_OPERATION VARCHAR;

CURSOR CURAUTORISATIONS IS 
SELECT  AUT.ID_AUTORISATION 
FROM   PP3_AUTORISATION AUT
WHERE AUT.ID_PLAN IN
        ( SELECT  PDP.ID_PLAN
            FROM   PP3_PLAN_DE_PREVENTION PDP
            WHERE  PDP.ID_PLAN = 3059     )
 AND    AUT.NUM_REVISION_PLAN = 
        (  SELECT MAX(NUM_REVISION_PLAN)       
             FROM  PP3_AUTORISATION 
             WHERE  ID_PLAN = 3059 );
CURSOR CUROPERATIONS IS
     SELECT  OPE.OPERATION 
        FROM PP4_OPERATION ope 
        WHERE ID_AUTORISATION IN
        (select id_autorisation 
            from pp3_autorisation AUT 
            WHERE AUT.ID_PLAN = 3059 
            AND AUT.NUM_REVISION_PLAN =   (
                             SELECT   MAX(NUM_REVISION_PLAN) - 1      
                             FROM pp3_autorisation
                             WHERE ID_PLAN = 3059) );    
BEGIN       
    OPEN CURAUTORISATIONS; 
    OPEN CUR_OPERATION;

   LOOP
        FETCH CURAUTORISATIONS INTO CUR_ID_AUTORISATION;
        EXIT WHEN CURAUTORISATIONS%NOTFOUND;
        FETCH CUROPERATIONS INTO CUR_OPERATION;
        EXIT WHEN CUROPERATIONS%NOTFOUND;

        INSERT INTO PP4_OPERATION 
        VALUES ( SEQ2_Operation.NextVal ,
                 CUR_OPERATION , 
                 CUR_ID_AUTORISATION );
    END LOOP;

    CLOSE CURAUTORISATIONS ;
    CLOSE CUROPERATIONS ;    
END;
0
votes

DECLARE in PL/SQL is a whole block unlike e.g. in T-SQL. You cannot have multiple DECLARE statements but declare multiple variables in that block preceding the BEGIN ... END.

DECLARE
  CUR_ID_AUTORISATION NUMERIC;
  CUR_OPERATION VARCHAR;
BEGIN
  ...
END;
0
votes

I noticed you are using some sql server syntax, (such as numeric, varchar, deallocate) I correct them and I added some comments. I also write the cursor in another way. please check the code and tell me if produce error. 

DECLARE
  CUR_ID_AUTORISATION NUMBER(2); -- it should be number instead and specific the size of the datatype
  CUR_OPERATION       VARCHAR(100); -- it should be varchar2 instead and specific the size 

  CURSOR CURAUTORISATIONS IS
    SELECT AUT.ID_AUTORISATION
      FROM PP3_AUTORISATION AUT
     WHERE AUT.ID_PLAN IN (SELECT PDP.ID_PLAN
                             FROM PP3_PLAN_DE_PREVENTION PDP
                            WHERE PDP.ID_PLAN = 3059)
       AND AUT.NUM_REVISION_PLAN =
           (SELECT MAX(NUM_REVISION_PLAN)
              FROM PP3_AUTORISATION
             WHERE ID_PLAN = 3059);

  CURSOR CUROPERATIONS IS
    SELECT OPE.OPERATION
      FROM PP4_OPERATION ope
     WHERE ID_AUTORISATION IN
           (select id_autorisation
              from pp3_autorisation AUT
             WHERE AUT.ID_PLAN = 3059
               AND AUT.NUM_REVISION_PLAN =
                   (SELECT MAX(NUM_REVISION_PLAN) - 1
                      FROM pp3_autorisation
                     WHERE ID_PLAN = 3059));

  -- OPEN CURAUTORISATIONS;
  -- OPEN CUR_OPERATION;
  --BEGIN

BEGIN
  -- Open first cursor
  for CUR_ID_AUTORISATION in CURAUTORISATIONS -- USING for loop cursor loop instead of open, and here you already fetched the data.
   LOOP
      EXIT WHEN CUROPERATIONS%NOTFOUND; 

    dbms_output.put_line('Hi');
    -- Open second cursor
    for CUR_OPERATION in CUROPERATIONS LOOP
      dbms_output.put_line('Hi second time');
      -- remove the comment.
          INSERT INTO PP4_OPERATION
           VALUES
             (1, CUR_OPERATION.OPERATION, CUR_ID_AUTORISATION.ID_AUTORISATION);

    end loop;
    --close CURAUTORISATIONS;
  --close CUROPERATIONS;
  --DEALLOCATE(CURAUTORISATIONS); -- there is not deallocate in oracle 
  --DEALLOCATE(CUROPERATIONS); -- there is not deallocate in oracle 

  end loop;

end;
/