SQL Procedure compiling errors

Question

we have two tables : Account and Client

CREATE TABLE Client ( NumClient NUMBER(3) NOT NULL PRIMARY KEY,
                      ClientName VARCHAR (25) NOT NULL,
                      City VARCHAR (25) NOT NULL
                    );
                    
CREATE TABLE Compte ( NumCompte NUMBER(1) NOT NULL PRIMARY KEY,
                      NumClient NUMBER(3) NOT NULL REFERENCES Client(NumClient),
                      DateOpening DATE NOT NULL, 
                       balance FLOAT ,
                      PMVR NUMBER DEFAULT 0
                    );

procedure is as follows:

OpenAccount(NumCli in number, Amount in number)

This procedure creates a new account for a customer (NumCli) with a first balance (Amount):

NumCaccount is automatically assigned by a sequence;
DateOpen is the system date;
Amount > 0;
PMVR is initialized to 0;

If the customer does not exist, there is an error.

I have a sequence called :

CREATE SEQUENCE seqClient START WITH 101 INCREMENT BY 1;    

CREATE SEQUENCE seqAccount START WITH 1 INCREMENT BY 1;

Here is text of procedure

CREATE OR REPLACE PROCEDURE OpenAccount(NumCli IN NUMBER, Amount in NUMBER)
IS
    non-existent_client EXCEPTION;
   PRAGMA EXCEPTION_INIT (non-existent_client, -2291);
BEGIN
   IF (Amount < 0)
   THEN
      RAISE_APPLICATION_ERROR (-20002,'the amount must be greater than 0');
   ELSE
      INSERT INTO Account (AccountNumber,
                           ClientNumber,
                           DateOpening date,
                           Balance,
                           PMVR)
           VALUES (seqCount.NEXTVAL,
                   NumCli,
                   TO_DATE (sysdate,'DD.MM.YY'),
                   Amount,
                   0);
   END IF;
EXCEPTION
   WHEN 
    non-existent_customer
   THEN
      DBMS_OUTPUT.PUT_LINE (
         Client No' ||| TO_CHAR (NumCli) ||| ' non-existent');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         Oracle error:' |||| SQLCODE ||| '; Oracle message: ||||| SQLERRM);
END;

When I run it like this

 execute OpenAccount(101,1600);

I get this error :

9/9 PL/SQL: SQL Statement ignored
10/81 PL/SQL: ORA-00984: Column not permissible here

Translated with www.DeepL.com/Translator (free version)

Please don't tag Oracle-specific questions with `sql-server`. — AlwaysLearning, Dec 14 '19 at 11:52
Edit the question and share the structure of table `account` — Popeye, Dec 14 '19 at 15:12
Your posted code (still) won't compile so it's hard for us to give you proper help. The ORA-00984 error means you have a value in the VALUES clause of your INSERT statement which is a column name of the ACCOUNT table. So, if that hint is not enough for you to fix your code, please post the DDL for ACCOUNT. — APC, Dec 16 '19 at 12:51

Littlefoot · Answer 1 · 2019-12-14T12:34:04.290

1

A few objections:

this doesn't seem to be the whole code. What is supposed to raise non_existent_client exception?
where do values you're inserting come from?
what is the new function? In Oracle, we use sysdate
you do love pipes, that's obvious, but - don't use them that much (hint: their usage in dbms_output call)
spaces aren't allowed while naming table columns (insert statement)
minus shouldn't be used as word separator (exception name)
when others is useless; I suggest you remove it. Or, if you insist, raise immediately after dbms_output

This looks better; will it actually compile, no idea as I don't have your tables.

CREATE OR REPLACE PROCEDURE OpenAccount(NumCli IN NUMBER, Amount in NUMBER)
IS 
   non_existent_client  EXCEPTION;
   PRAGMA EXCEPTION_INIT (non_existent_client, -2291);
BEGIN
   IF (Amount < 0)
   THEN
      RAISE_APPLICATION_ERROR (-20002, 'the amount must be greater than 0');
   ELSE
      INSERT INTO Account (Account_Number,
                           Client_Number,
                           OpeningDate,
                           Balance,
                           PRM)
           VALUES (seqCompte.NEXTVAL,
                   NumCli,
                   TO_DATE (NOW (), 'DD.MM.YY'),
                   Amount,
                   PMVR);
   END IF;
EXCEPTION
   WHEN non_existent_client
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Client No' || TO_CHAR (NumCli) || ' non-existent');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Oracle error:' || SQLCODE || '; Oracle message: ' || SQLERRM);
END;

edited Dec 14 '19 at 12:34

answered Dec 14 '19 at 11:57

Littlefoot

131,892
15
35
57

I have a table Account where i insert values. – Dec 14 '19 at 12:25
So replace my DECLARE with your CREATE PROCEDURE. That's more or less the only change you need to convert an anonymous PL/SQL block into a procedure. – Littlefoot Dec 14 '19 at 12:26
Anyway i got the same error at the lines 10: and 19 – Dec 14 '19 at 12:34
I have a feeling that you didn't read objections I wrote **above** code I posted. – Littlefoot Dec 14 '19 at 12:36
Anyway i got the same error at the lines 10/7 PL/SQL: SQL Statement ignore and 19/20 : PL/SQL: ORA-00984: Column not allowed here – Dec 14 '19 at 12:42
I read it sorry, but because i have make this code with a solution of an exercice in class. That's why i maybe have missread it. – Dec 14 '19 at 12:44
@Littlefoot - after correctly catching the reference to the non-existent (in Oracle) 'now' function, you repeated its use in your solution. As you know, but the OP doesn't, the entire "TO_DATE (NOW (), 'DD.MM.YY')" should be replaced with simply "sysdate". – EdStevens Dec 14 '19 at 13:26
That's the 3rd point I wrote in my answer, @EdStevens.I don't know what NOW might, be; why not `function now return date is begin return sysdate; end;` – Littlefoot Dec 14 '19 at 13:35

SQL Procedure compiling errors

1 Answers1