Oracle Stored Procedure Troubleshooting

From: Mark <mfaine_at_knology.net>
Date: 3 Dec 2002 07:00:52 -0800
Message-ID: <329dd608.0212030700.6c4a1974@posting.google.com>

I'm trying to create a stored procedure on an Oracle 7.3.2.1.0 Server. I am accessing this server via SQL Plus. I'm not sure that it makes any difference but I am accessing this server through a link from an Oracle 8.1.7 Server.

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Dec 3 08:46:20 2002

Connected to:
Oracle7 Server Release 7.3.2.1.0 - Production Release PL/SQL Release 2.3.2.0.0 - Production

Description of table that is relevant to this procedure SQL> describe drgi_sheets

 Name                            Null?    Type
 ------------------------------- -------- ----
 DOCUMENT_ID                     NOT NULL VARCHAR2(17)
 VOLUME                                   VARCHAR2(5)
 SECTION                                  VARCHAR2(5)
 ISSUE                                    VARCHAR2(8)
 CHANGE                                   VARCHAR2(7)
 SHEET                           NOT NULL NUMBER
 PATH                            NOT NULL VARCHAR2(254)
 ID                                       NUMBER(10)
---------------------------------------------------------

The package specification is being successfully created but the package body is not. I get nothing more than a vague error message:

Warning: Package Body created with compilation errors.

Can anyone spot what is the problem with this package body? Also, is there any way I can get usefull debugging information while I'm attempting to create the stored procedures?

Thanks,

CREATE OR REPLACE PACKAGE DRGIUTIL
IS
TYPE tbl_volume is TABLE of VARCHAR2(5)

      INDEX BY BINARY_INTEGER;
    TYPE tbl_section is TABLE of VARCHAR2(5)       INDEX BY BINARY_INTEGER;
    TYPE tbl_issue is TABLE of VARCHAR2(8)       INDEX BY BINARY_INTEGER;
    TYPE tbl_change is TABLE of NUMBER(7)       INDEX BY BINARY_INTEGER;
    TYPE tbl_sheet is TABLE of NUMBER
      INDEX BY BINARY_INTEGER;
    TYPE tbl_path is TABLE of VARCHAR2(254)

      INDEX BY BINARY_INTEGER;
               
      PROCEDURE drgisearch(docnumber IN VARCHAR2,
                               volume OUT tbl_volume,
                               section OUT tbl_section, 
                               issue OUT tbl_issue,  
                               change OUT tbl_change, 
                               sheet OUT tbl_sheet, 
                               path OUT tbl_path ); 
                                    
END DRGIUTIL;
                  
         
            
CREATE OR REPLACE PACKAGE BODY DRGIUTIL 
   AS      
   PROCEDURE drgisearch(docnumber IN VARCHAR2, 
                        volume OUT tbl_volume, 
                        section OUT tbl_section, 
                        issue OUT tbl_issue, 
                        change OUT tbl_change, 
                        sheet OUT tbl_sheet, 
                        path OUT tbl_path )
   IS
     BEGIN
       CURSOR drgicursor
       IS


         -- do query here 

         SELECT VOLUME, SECTION, ISSUE, CHANGE, SHEET, PATH
         FROM DRGI_SHEETS 
         WHERE DOCUMENT_ID LIKE '%docnumber%';
         numresult NUMBER DEFAULT 1;
         


         -- loop through assigning values

         FOR C IN drgicursor LOOP
          volume (numresult) := C.VOLUME;
          section (numresult) := C.SECTION;
          issue (numresult) := C.ISSUE;
          change (numresult) := C.CHANGE;
          sheet (numresult) := C.SHEET;
          path (numresult) := C.PATH;
          
        -- increment number of results
        numresult := numresult + 1;
        END LOOP;
        
     END drgicursor;

END DRGIUTIL;

END --

Received on Tue Dec 03 2002 - 09:00:52 CST