Creating a package

[JPCogAdmin]

Ok,

I'm back on Oracle after years and want to create a basic package but I'm getting error:

PLS-00103

The code is below:
=======================================================
CREATE OR REPLACE
PACKAGE BODY LIST AS

procedure Get(fac IN table1.FAC_ID%TYPE) AS

declare
c1_rec table1%ROWTYPE;

BEGIN

/* TODO implementation required */

select T.fac_id,T.phone,T.calls
from
(select fac_id, phone, count(*) calls,dense_rank() over(order by count(*) desc) as Rank INTO c1_rec From table1 where fac_id='Western' group by fac_id,phone) T
order by T.calls desc;

END Get;


END LIST;
====================================================

Can someone lead me into the right direction which is where I need to go.
;-)
Thanks.

-J

 

[SantaMufasa]

JP,

Since few of us have all of the ORA- and PLS- error memorised, it's always helpful to err on the side of including too much information, such as the full description of the error.

In your error message, "PLS-00103: Encountered the symbol "<something special>" when expecting one of the following:...", what was the <something special>?

Amongst the things that I notice that either you did not include here or were missing altogether was your "CREATE OR REPLACE PACKAGE..." statement that must precede your "CREATE OR REPLACE PACKAGE BODY..." statement. Could you include that for us please?

Also, your code:

    select T.fac_id,T.phone,T.calls 
      from
          (select fac_id, phone, count(*) calls,dense_rank() over(order by count(*) desc) as Rank INTO c1_rec From table1 where fac_id='Western' group by fac_id,phone) T 
            order by T.calls desc;

...to pass syntax muster, must have the INTO appear in the outermost SELECT, not the in-line VIEW.

If your correcting these issues does not clean up your errors, and if you cannot identify the causes of the remaining errors, please let us know.

We will be able to complete the troubleshooting with you if you also include in your subsequent post, minimum "CREATE TABLE..." and "INSERT INTO..." statements to populate the rows and columns of tables to which your package refers.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPCogAdmin]

MUfasa,

Here is the create package statement:

===============================================
create or replace PACKAGE LIST AS

procedure Get(fac IN table1.FAC_ID%TYPE);
/* TODO enter package declarations (types, exceptions, methods etc) here */

END LIST;
====================================================

That's the package creation. The errors I get in the package body are:

-----------------------------------------------------
Error(9,12): PLS-00103: Encountered the symbol "C2" when expecting one of the following: := . ( @ % ;


Error(10,36): PLS-00103: Encountered the symbol "INTO" when expecting one of the following: . ( , * @ % & - + / at mod remainder rem <an identifier> <a double-quoted delimited-identifier> <an exponent (**)> as from || multiset The symbol "INTO" was ignored.


Error(12,65): PLS-00103: Encountered the symbol "(" when expecting one of the following: , from

-----------------------------------------------------

As you can see there are 3 error messages I get. I have just changed the code and now the package body looks like this:

=======================================================

CREATE OR REPLACE
PACKAGE BODY LIST AS

procedure Get(fac IN table1.FAC_ID%TYPE) AS

declare
cursor c2 table1%ROWTYPE;

BEGIN

/* TODO implementation required */

open c2 for

select T.fac_id,T.phone,T.calls
from
(select fac_id, phone, count(*) calls,dense_rank() over(order by count(*) desc) as Rank From table1 where fac_id='Western' group by fac_id,phone) T
order by T.calls desc;

END Get;


END LIST;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-JP

 

[Thargy]

jp,

you must declare the cursor in the declaration section of your code. It should be something like

cursor c2 RETURN table1%ROWTYPE
IS
      SELECT * FROM table1
       WHERE jpcog = 3;

Also, if you declare it as returning a ROWTYPE, then the cursor must indeed do so. If you want to use a custom format, declare a cursor based on your select statement, and then declare a record as being of that cursor's type.

Regards

Tharg


Grinding away at things Oracular

 

[JPCogAdmin]

Thanks Thrg,

I'll do that.

-JP

 

[JPCogAdmin]

I believe the problem is with the dense_rank() function.
I've created different version of the package and procedure and I'm sure the problem is with the dense_rank.

-JP