1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Procedure compiled with errors - help

Discussion in 'SQL PL/SQL' started by FiZor, May 25, 2010.

  1. FiZor

    FiZor Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Croatia, Zagreb
    I need little help with my procedure. Procedure is compiling with errors. What could be a problem? Many thx in advance.
    I submit here full documentation of my procedure:
    http://www.adrive.com/public/1ecf54c03bcfbb89c291c1b72085fb2f4abd44a38dc05a3f055d5e1af8034d19.html

    Code (Text):
    create or replace
    PROCEDURE PROMJENA_CIJENA(MODELPOST_V_ID NUMBER) AS
    LAGER_KOL NUMBER;

    BEGIN
     SELECT STANJE_LAGERA
     INTO LAGER_KOL
     FROM MODEL_VOZILA
     WHERE ID_MODEL_V = MODELPOST_V_ID;
     
     IF LAGER_KOL <= 5
     THEN
     UPDATE MODEL_VOZILA
     SET CIJENA_MODELA = CIJENA_MODELA * 0.2
     WHERE ID_MODEL_V = MODELPOST_V_ID;
     
     COMMIT;
     
     DBMS_OUTPUT.PUT_LINE('IzvrŇ°eno je umanjenje cijene vozila u visini 20% za model'||MODELPOST_V_ID);
     
     ELSE
     
     DBMS_OUTPUT.PUT_LINE('Cijena vozila je ostala nepromijenjena za model'||MODELPOST_V_ID);
     
     END IF;
     
     EXCEPTION
      WHEN NO_DATA_FOUND
      THEN DBMS_OUTPUT.PUT_LINE('Ne postoji takav model vozila.');
     
     END PROMJENA_CIJENA;
    Error is:
    o.d.r.utils.DBObject Could not get schema Object:java.SQLException:ORA-04063: view "SYS.ALL_QUEUE_TABLES" has errors
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Just Paste teh code in SQLPLUS and compile there. And show us the actual Error message. The error message you posted is nothing to do with your piece of code.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    But that error message has everything to do with the recursive SQL necessary to compile it regardless of the tool used. Obviously the view SYS.ALL_QUEUE_TABLES has errors and cannot be accessed; this problem needs to be addressed before any troubleshooting of the procedure code can occur. Either connect as SYS and attempt to compile the view:

    Code (SQL):
    SQL> CONNECT / AS sysdba
    Connected.
    SQL> ALTER VIEW all_queue_tables compile;
     
    VIEW compiled WITH errors.
     
    SQL> SHOW errors VIEW all_queue_tables
    [likely SOME output here]
     
    or get your DBA to do this. Address these errors (in other words, fix the view) then try creating the procedure again.
     
  4. FiZor

    FiZor Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Croatia, Zagreb
    Thank you very much ppl with suggestions. I simply tried on other machine where SQL Developer was installed and compiling and running scrypt went ok, with no error. :)