CODEWITHSUNDEEP
oracle-databaseplsqlsyntax-errorpackage
Surya
Surya

Reputation: 591

I am not getting the error in the package code of Oracle given below:

When I am trying to execute this code I am getting the following error. I am not able to resolve this problem - what am I missing?

SHOW ERRORS;
Errors for PACKAGE BODY PKG_VIEW_LEDGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/14     PLS-00304: cannot compile body of 'PKG_VIEW_LEDGER' without its
         specification

1/14     PLS-00905: object SYSTEM.PKG_VIEW_LEDGER is invalid

--- code below ---

CREATE OR REPLACE  PACKAGE PKG_VIEW_LEDGER AS
        TYPE cur_view_data is REF CURSOR;

        PROCEDURE sp_view_ledger(person IN VARCHAR2, cur_inout IN OUT cur_view_data);
END PRG_VIEW_LEDGER;
/

CREATE OR REPLACE PACKAGE BODY PKG_VIEW_LEDGER AS
PROCEDURE sp_view_ledger( person IN VARCHAR2, cur_inout IN OUT cur_view_data)
IS
    tmp_cursor cur_view_data;
BEGIN
    OPEN tmp_cursor FOR
        select * from ledger where person like '%" + myArg.ToUpper + "%';

    cur_inout := tmp_cursor;
END sp_view_ledger;
END PKG_VIEW_LEDGER;
/

Upvotes: 0

Views: 5629

Answers (2)

Avrajit
Avrajit

Reputation: 230

I have modified the code and its working fine. Please try it from your side and let me know. Thanks. 

CREATE OR REPLACE  PACKAGE PKG_VIEW_LEDGER AS
        TYPE cur_view_data is REF CURSOR;

        PROCEDURE sp_view_ledger(person IN VARCHAR2, cur_inout IN OUT cur_view_data);
        END PkG_VIEW_LEDGER;
--------------------------------------------------------------
Package  created.
--------------------------------------------------------------
        CREATE OR REPLACE  PACKAGE PKG_VIEW_LEDGER AS
                TYPE cur_view_data is REF CURSOR;

                PROCEDURE sp_view_ledger(person IN VARCHAR2, cur_inout IN OUT cur_view_data);
        END PRG_VIEW_LEDGER;
        /

        CREATE OR REPLACE PACKAGE BODY PKG_VIEW_LEDGER AS
        PROCEDURE sp_view_ledger( person IN VARCHAR2, cur_inout IN OUT cur_view_data)
        IS
            tmp_cursor cur_view_data;
        BEGIN
            OPEN tmp_cursor FOR
                select * from avrajit;

            cur_inout := tmp_cursor;
        END sp_view_ledger;
        END PKG_VIEW_LEDGER;
    /
    -----------------------------------------------------

    Package Body created.

    0.04 seconds

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231861

First, based on the error, you are creating the package in the SYSTEM schema. Don't do that. SYS and SYSTEM are special and should be used only by Oracle for objects delivered by Oracle. You'll need a new schema where you can create your own objects. SYS and SYSTEM are special and do not always behave the same way that normal schemas do so various bits of functionality will appear not to work correctly if you use these schemas.

Second, when you are defining the package specification, your END does not match your CREATE. You are creating PKG_VIEW_LEDGER while you are ending PRG_VIEW_LEDGER. PKG != PRG. If you had a show errors after creating the package specification, the error would tell you this.

Upvotes: 1

Related Questions