Thanks Guys, I've learned a lot from you. Especially James, your
suggestion was the fix.
-Mark
"James Cummings" <no-spam-jc1635_at_attbi.com> wrote in message news:<OCaH9.228493$WL3.76998_at_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 Wed Dec 04 2002 - 07:44:53 CST
