12 Replies Latest reply: Jul 31, 2012 9:48 PM by 905711 RSS

    cannot compile package from query window.

    905711
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                can you please let me know if you are planning to fix this issue for oracle10?
                                thank you