CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle10g
Sesuraj
Sesuraj

Reputation: 29

Warning: Procedure created with compilation errors in oracle

CREATE TABLE:

create table customer (Name varchar2(10), Address varchar(40), Contact number);

CREATE PROCEDURE FOR INSERT:

CREATE OR REPLACE PROCEDURE SP_CUSTOMER (
p_name customer.Name%TYPE,
p_address customer.Address%TYPE,
p_contact customer.Contact%TYPE)
IS
BEGIN
INSERT INTO customer ("Name", "Address", "Contact")
VALUES (p_name, p_address, p_contact);
COMMIT;
END;
/

ERROR: IT SHOWS: Warning: Procedure created with compilation errors.

CREATE PROCEDURE FOR SELECT:

CREATE OR REPLACE PROCEDURE SP_SELECT_CUSTOMER (
p_name customer.Name%TYPE,
p_address customer.Address%TYPE,
p_contact customer.Contact%TYPE)
IS
BEGIN
SELECT Name, Address, Contact FROM customer;
END;
/

ERROR: IT SHOWS: Warning: Procedure created with compilation errors.

What is the problem.? How to solve it.?

Upvotes: 0

Views: 49850

Answers (3)

Yogeshwaran
Yogeshwaran

Reputation: 41

select * from user_errors where name='SP_SELECT_CUSTOMER'

Upvotes: 1

Gaurav Soni
Gaurav Soni

Reputation: 6338

--you need not to put the column in quotes 

CREATE OR REPLACE PROCEDURE SP_CUSTOMER (
p_name customer.Name%TYPE,
p_address customer.Address%TYPE,
p_contact customer.Contact%TYPE)
IS
BEGIN
INSERT INTO customer (Name, Address, Contact)
VALUES (p_name, p_address, p_contact);
COMMIT;
END;
/

--just use a refcursor to return the resultset

CREATE OR REPLACE PROCEDURE SP_SELECT_CUSTOMER (
p_cust_details OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_cust_details for SELECT Name, Address, Contact FROM customer;
END;
/

EDIT if you want to find details based on name ,then pass an IN parameter and use it as filter condition.

CREATE OR REPLACE PROCEDURE SP_SELECT_CUSTOMER (
p_cust_name    IN   customer.Name%TYPE
p_cust_details OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_cust_details for SELECT Name, Address, Contact FROM customer
                         WHERE name=p_cust_name ;
END;
/

Upvotes: 2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

  1. Whenever you compile something in SQL*Plus and you get "compilation errors", the first thing you want to do is to SHOW ERRORS, which will tell you what actually went wrong.

  2. Your first procedure has a problem where the column names will not match the column names from your table. Your table definition has Name, Address and Contact, but because you haven't used double-quotes, the column names will actually be NAME, ADDRESS and CONTACT - but your insert statement uses double-quotes, so it tries to insert into Name etc. Just get rid of the double-quotes, you don't need them here.

  3. In your second procedure, you have a SELECT statement in PL/SQL, but you don't specify where to put the resulting data. You at least need an INTO clause, or perhaps do a LOOP over the resulting records.

Upvotes: 15

Related Questions