This discussion is archived
12 Replies Latest reply: Jul 31, 2012 7:48 PM by 905711 RSS

cannot compile package from query window.

905711 Newbie
Currently Being Moderated
Hi, I cannot create or replace package if I run sql in "query window". Is it a bug or query window wasn’t designed to create packages? This issue is huge and prevents my team to use dev tools to develop package code!
Thanks in advance for your time.

More details:
I can create or edit db package when i open "new package window" or when i click on package specification/body. However, I am getting error when I am trying to create a package from “query window”. If i execute the same sql file in other client (rapid sql) then I create the package just fine.
Even when I create a new package using "new package window" the tool generates sql script. Then I run this sql in "query window" and i am getting an error.

CREATE PACKAGE "LNPKG" AUTHID CURRENT_USER IS
PROCEDURE "GETID" (
"ID" IN VARCHAR2);
PROCEDURE "DELETEID" (
"ID" IN VARCHAR2);
END "LNPKG";
/
CREATE PACKAGE BODY "LNPKG" IS
PROCEDURE "GETID" (
"ID" IN VARCHAR2) IS
BEGIN
NULL;
END "GETID";
PROCEDURE "DELETEID" (
"ID" IN VARCHAR2) IS
BEGIN -- executable part starts here
NULL;
END "DELETEID";
END "LNPKG";
/
!
  • 1. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    What error are you getting?

    Try highlighting the part you want to run before you execute it.

    Also, I can't recall, but I don't think you need the slash at the end.

    Query window is not using SQL Plus underneath like our script window does so some syntax will not work.
  • 2. Re: cannot compile package from query window.
    905711 Newbie
    Currently Being Moderated
    thank you for looking into it!
    1.     The error is not very informative: “ORA-24344: success with compilation error”
    2.     the code you see was generated by oracle developer tools “create new package” function (with slashes)
    3.     when removed slashes getting error anyways.
    4.     Highlighting code before run doesn’t help.
  • 3. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Ok, what you really need in this case is the sql plus command "Show errors".

    Unfoirtunately, the query window is not based on SQL Plus so you cannot do that here.

    Cut and paste the code into a text file, add the line "show errors" and from the menu choose Tools-->Run SQL Plus script.

    In the VS output window you should then see the true compilation error.

    You can also do this from SQLPLUS.EXE instead if you want.
  • 4. Re: cannot compile package from query window.
    905711 Newbie
    Currently Being Moderated
    hi, the code itself is not bad. I can compile it successfully using other clients including sql plus successfully. The issue is that it is not compiling in oracle developers tool query window! It is not only one machine, but multiple machines behaving the same way. Can you simulate it?
  • 5. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    You need to take the exact same code in the Query window, cute and paste to notepad, and add the line "show errors" to the end. Then run that in SQL Plus.

    That's the only way we will know what is going wrong. It could something that is not readily noticeable.
  • 6. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    It seems like you have a naming collision. When I execute the package body, I get "ORA-00955: name is already used by an existing object"

    Change the package body portion of the script to say "CREATE OR REPLACE" and the error will go away.
  • 7. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    In the future, rather than running whole PL/SQL scripts in Query window, use our SQL Script editor along with "Show errors" sprinkled within.

    When doing adhoc PL/SQL creation, Query window is fine, but highlight each portion of the code and execute individually, that way you can see the error as it occurs - since we don't currently support "show errors". That's how I found your error.
  • 8. Re: cannot compile package from query window.
    905711 Newbie
    Currently Being Moderated
    hi, I appreciate very much your help!
    Can you please confirm that when you run it in "query windows" it DOES compile for you?
    It doesn’t for me or any of my dev peers. Yes, I am aware I had to add "replace" when i run creation not first time in db, but this is not an issue!
    The issue is that the same code compiles in sql plus but does not compile in query window.

    Below is sql plus run
    SQL> CREATE or replace PACKAGE "LNPKG" AUTHID CURRENT_USER IS
    2 PROCEDURE "GETID" (
    3 "ID" IN VARCHAR2);
    4 PROCEDURE "DELETEID" (
    5 "ID" IN VARCHAR2);
    6 END "LNPKG";
    7 /

    package created.

    QL> CREATE or replace PACKAGE BODY "LNPKG" IS
    2 PROCEDURE "GETID" (
    3 "ID" IN VARCHAR2) IS
    4 BEGIN
    5 NULL;
    6 END "GETID";
    7 PROCEDURE "DELETEID" (
    8 "ID" IN VARCHAR2) IS
    9 BEGIN -- executable part starts here
    10 NULL;
    11 END "DELETEID";
    12 END "LNPKG";
    13 /

    package body created.

    SQL> show errors
    No errors.
    SQL>
  • 9. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Yes, it is working for me. I'm using ODT 11.2.0.3 and Oracle XE (11.2) with Visual Studio 2010

    Are you using standard engliish windows? Or some other language?

    What version of ODT are you using?

    Are you using Visual Studio 2010?
  • 10. Re: cannot compile package from query window.
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Immediately after you receive the error, please execute the following:
    SELECT LINE, TEXT FROM ALL_ERRORS WHERE NAME = 'LNPKG'

    This is the equivalent of "show errors" in sql plus I think and should tell you what is going wrong.
  • 11. Re: cannot compile package from query window.
    905711 Newbie
    Currently Being Moderated
    Now, i see that the issue happends only in oracle 10g version, the 11g compiles fine. Since we are going to support 10g for some time, i was hoping you can look into it..



    1. standard english
    2. the error happends in 10g db.
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    3. no error in 11g db:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options


    Microsoft Visual Studio 2010
    Version 10.0.40219.1 SP1Rel
    Microsoft .NET Framework
    Version 4.0.30319 SP1Rel
    Installed Version: Ultimate
    Microsoft Office Developer Tools 01019-315-4422995-70693
    Microsoft Office Developer Tools
    Oracle Developer Tools for Visual Studio 11.2.0.3.0
    Oracle Developer Tools for Visual Studio Copyright (c) 2005, 2011


    *******
    code and output
    ***********
    CREATE or replace PACKAGE LNPKG AUTHID CURRENT_USER IS
    PROCEDURE GETID (
    ID IN VARCHAR2);
    PROCEDURE DELETEID (
    ID IN VARCHAR2);
    END LNPKG;
    /
    CREATE or replace PACKAGE BODY LNPKG IS
    PROCEDURE GETID (
    ID IN VARCHAR2) IS
    BEGIN
    NULL;
    END GETID;
    PROCEDURE DELETEID (
    ID IN VARCHAR2) IS
    BEGIN -- executable part starts here
    NULL;
    END DELETEID;
    END LNPKG;
    /
    SELECT LINE, TEXT FROM ALL_ERRORS WHERE NAME = 'LNPKG';
    /

    ORA-24344: success with compilation error


    ORA-24344: success with compilation error


    SQL query executed:
    SELECT LINE, TEXT FROM ALL_ERRORS WHERE NAME = 'LNPKG'

    ROW NO. LINE TEXT
    ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 1 PLS-00103: Encountered the symbol "" when expecting one of the following:

    end function package pragma private procedure subtype type
    use <an identifier> <a double-quoted delimited-identifier>
    form current cursor
  • 12. Re: cannot compile package from query window.
    905711 Newbie
    Currently Being Moderated
    can you please let me know if you are planning to fix this issue for oracle10?
    thank you

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points