Re: Oracle Stored Procedure Troubleshooting

Mark wrote:
> 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
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> 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.

What does "show errors" say?

>
> 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 --
/Svend Received on Tue Dec 03 2002 - 14:47:51 CST