Re: Stored procedure problems

From: M. Armaghan Saqib <armaghan_at_yahoo.com>
Date: Mon, 14 Feb 2000 17:30:15 GMT
Message-ID: <889e37$qli$1@nnrp1.deja.com>

Hi,

SQL*Plus SHOW ERRORS command can list the errors in the compiled module. Although it is useful but bit limited since it does not show you the source code lines where error occured.

You can use EXEC S.LSTERR command of my SQLPlusPlus (freeware) to list compilation errors along with the source code lines. e.g.

SQL> EXEC S.LSTERR
| ----------------------------------------------------------------------

---

| Errors for PROCEDURE GIVE_RAISE
| ----------------------------------------------------------------------

---

| p_raise_percent in number )
| as
| begin
| update emp set sal = sal + (sal * p_raise_percent * .01)
| where deptno p_deptno;
| -----------------^----------------------------------------------------

---

| PLS-00103: Encountered the symbol "P_DEPTNO" when expecting one of
the following:

|

| . ( * @ % & = - + ; < / > in mod not rem return RETURNING_
| an exponent (**) <> or != or ~= >= <= <> and or like between
| is null is not || is dangling
| The symbol "." was substituted for "P_DEPTNO" to continue.
| ----------------------------------------------------------------------

---

regards,
M. Armaghan Saqib

+---------------------------------------------------------------


| 1. SQL PlusPlus => Add power to SQL Plus command line

| 2. SQL Link for XL => Integrate Oracle with XL
| 3. Oracle CBT with sample GL Accounting System
| Download free: http://www.geocities.com/armaghan/

+---------------------------------------------------------------

<kirk_at_kaybee.org> wrote in message news:<8898kn$3q7$1_at_news- int.gatech.edu>...

> I am trying to create a stored procedure to create a new account.  The
> tables are all in the 'udb' schema.  My problem is that this procedure
> has errors, but I don't know where they are or how to determine where
> they are.
>
> Here is the commands I used to create the pertinent DB elements:
>
> CREATE TABLE users (u_id int primary key not null, u_name varchar2(20)
>    not null, u_index int not null);
>
> CREATE SEQUENCE uid_seq INCREMENT BY 1 START WITH 1 MAXVALUE

2147483648

>    nocycle nocache noorder;
>
> CREATE FUNCTION create_account (newname IN VARCHAR2)
> RETURN number
> AS
>    max number;
> BEGIN
>    SELECT max(u_index)
>    INTO max
>    FROM udb.users
>    WHERE u_name = newname;
>
>    max := (max + 1);
>
>    INSERT INTO udb.users (u_id, u_name, u_index)
>    VALUES (udb.uid_seq.nextval, newname, max);
>
>    RETURN max;
>
> END;
>
> When I run the last command (CREATE FUNCTION) in 'sqlplus' it just
> tells me:
>    Warning: Function created with compilation errors.
>
> I would appreciate any help you could give me on this.
>
> --
>  Kirk Bauer -- CmpE, Georgia Tech -- kirk_at_kaybee.org -- Avid Linux
User
>     GT Sport Parachuting Club!  http://cyberbuzz.gatech.edu/skydive

>      Opinions expressed are my own, but they should be everybody's.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 14 2000 - 11:30:15 CST