Re: Oracle Stored Procedure Troubleshooting

From: James Cummings <no-spam-jc1635_at_attbi.com>
Date: Tue, 03 Dec 2002 22:54:38 GMT
Message-ID: <OCaH9.228493$WL3.76998@rwcrnsc54>

The show errors would be more useful, but one thing I see is you declared the cursor and "numresult" AFTER the "begin" statement in the body of your procedure. I suspect you'll want to move that between the "IS" and the "BEGIN". Like so:

> 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
> 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;
> BEGIN

>


>          -- 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;

---

James Cummings
"Chaos, panic, disorder.... my work is done."

"Mark" <mfaine_at_knology.net> wrote in message news:329dd608.0212030700.6c4a1974_at_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

>

> (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.

>

> 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 - 16:54:38 CST