Forum Stats

  • 3,783,480 Users
  • 2,254,783 Discussions
  • 7,880,428 Comments

Discussions

My code shows error and I am not able to understand the errror

CREATE TABLE credit(

cust_name varchar2(100),

credit_limit NUMBER(10,3),

cust_id NUMBER);

INSERT INTO credit VALUES('Arnab Deb',999990.000,100);

INSERT INTO credit VALUES('Pampita Bhowmik',999991.000,150);

INSERT INTO credit VALUES('Ankur Kumar Das',999992.000,102);

DECLARE 

 l_credit_lmt credit.credit_limit%TYPE;

 l_average_cred l_credit_lmt%TYPE;

 l_max_cred l_credit_lmt%TYPE;

 l_min_cred l_credit_lmt%TYPE;

BEGIN

 SELECT 

 MIN(credit_limit);

 MAX(credit_limit);

 AVG(credit_limit);

 INTO 

 l_min_cred,

 l_max_cred,

 l_average_cred,

 FROM 

 credit;

  

 SELECT

 credit_limit;

 INTO

 l_credit_lmt;

 FROM 

 credit;

 WHERE

 cust_id = 100;

 DBMS_OUTPUT.PUT_LINE('Minim-Credit:-'|| l_min_cred);

 DBMS_OUTPUT.PUT_LINE('Maximum-Credit:-'|| l_max_cred);

 DBMS_OUTPUT.PUT_LINE('Avg-Credit:-'|| l_average_cred);

 DBMS_OUTPUT.PUT_LINE('Cust-Credit:-'|| l_credit_lmt);

 

END;

 The error it shows is :-

ORA-06550: line 3, column 20:

PL/SQL: ORA-00923: FROM keyword not found where expected

 

 

 

 

 

 

 

 

  

  

  

  

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,652 Red Diamond

    You have a comma after:

     l_average_cred,

    which isn't needed

  • BluShadow
    BluShadow Member, Moderator Posts: 41,652 Red Diamond
    edited Sep 16, 2021 9:36AM

    You also have a semicolon after your table name:

    credit;

    which likewise isn't needed. (for the query where you have a WHERE clause following it)

  • BluShadow
    BluShadow Member, Moderator Posts: 41,652 Red Diamond

    In fact you have far too much, or wrong punctuation in your code. Here, try this...

    CREATE TABLE credit(
      cust_name varchar2(100),
      credit_limit NUMBER(10,3),
      cust_id NUMBER
      );
    INSERT INTO credit VALUES('Arnab Deb',999990.000,100);
    INSERT INTO credit VALUES('Pampita Bhowmik',999991.000,150);
    INSERT INTO credit VALUES('Ankur Kumar Das',999992.000,102);
    DECLARE 
      l_credit_lmt credit.credit_limit%TYPE;
      l_average_cred l_credit_lmt%TYPE;
      l_max_cred l_credit_lmt%TYPE;
      l_min_cred l_credit_lmt%TYPE;
    BEGIN
      SELECT MIN(credit_limit),
             MAX(credit_limit),
             AVG(credit_limit)
      INTO   l_min_cred,
             l_max_cred,
             l_average_cred
      FROM   credit;
      
      SELECT credit_limit
      INTO   l_credit_lmt
      FROM   credit
      WHERE  cust_id = 100;
      
      DBMS_OUTPUT.PUT_LINE('Minim-Credit:-'|| l_min_cred);
      DBMS_OUTPUT.PUT_LINE('Maximum-Credit:-'|| l_max_cred);
      DBMS_OUTPUT.PUT_LINE('Avg-Credit:-'|| l_average_cred);
      DBMS_OUTPUT.PUT_LINE('Cust-Credit:-'|| l_credit_lmt);
    END;