Insert Data Into Table with Stored Procedure in Oracle SQL

Question

I am working through a homework assignment and am stuck on a problem with stored procedures.

I have the following tables:

create table Restaurant(rID int, name varchar2(100), address varchar2(100), cuisine varchar2(100));
create table Reviewer(vID int, name varchar2(100));
create table Rating(rID int, vID int, stars int, ratingDate date);

For my stored procedure I need to create, I get an input of a restaurant name (unique), reviewer name(unique), star rating, and review date. I need to update the Rating table with the proper information, and add a new reviewer to the Review table if they have not previously existed in the table.

In order to start my stored procedure, I wanted to start with just creating a new table called newReview to get the inputs stored in a new table, before re-writting to update the existing tables.

Here is the newReview Table

CREATE TABLE newReview(
    RestaurantName VARCHAR(100),
    UserName VARCHAR(100),
    Stars Int,
    RatingDate Date
)

This is my AddNewReview procedure, which compiles with success:

CREATE OR REPLACE PROCEDURE AddNewReview(
  RESTAURANTNAME IN VARCHAR2 (100)
, USERNAME IN VARCHAR2 (100)
, Stars IN NUMBER 
, RATINGDATE IN DATE 
) AS 
BEGIN
  INSERT INTO newReview VALUES (RestaurantName, UserName, Stars, RatingDate);
END AddNewReview;
;

However, when I run the stored procedure with inputs as such,

BEGIN
    AddNewReview ('Rangoli', 'Sarah M.', 4, '2020-11-21');
END;

I get the following error:

Error starting at line : 20 in command -
BEGIN
    AddNewReview ('Rangoli', 'Sarah M.', 4, '2020-11-21');
END;
Error report -
ORA-06550: line 2, column 5:
PLS-00905: object TOCONN22.ADDNEWREVIEW is invalid
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I have tried defining the date input as DATE '2020-11-21' and also switching the single quote to double. Where am I going wrong, as this is the first stored procedure I am writing.

Tawfik Yasser · Accepted Answer · 2020-11-22 05:20:08Z

2

Try to change Stars data type from NUMBER to Int

AS:

CREATE OR REPLACE PROCEDURE AddNewReview(
  RESTAURANTNAME IN VARCHAR2 (100)
, USERNAME IN VARCHAR2 (100)
, Stars IN NUMBER 
, RATINGDATE IN DATE 
) AS 
BEGIN
  INSERT INTO newReview VALUES (RestaurantName, UserName, Stars, RatingDate);
END AddNewReview;
;

to

CREATE OR REPLACE PROCEDURE AddNewReview(
  RESTAURANTNAME IN VARCHAR2 (100)
, USERNAME IN VARCHAR2 (100)
, Stars IN Int
, RATINGDATE IN DATE 
) AS 
BEGIN
  INSERT INTO newReview VALUES (RestaurantName, UserName, Stars, RatingDate);
END AddNewReview;
;

answered Nov 22, 2020 at 5:20

Tawfik Yasser

862 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tommy O'Connell Over a year ago

After changing Stars to type INT, I still get the same error unfortunately.

Gordon Linoff · Accepted Answer · 2020-11-22 13:47:51Z

You need to look up the ids for the insertion:

CREATE OR REPLACE PROCEDURE AddNewReview (
  in_RESTAURANTNAME IN VARCHAR2(100),
  in_USERNAME IN VARCHAR2(100),
  in_Stars IN NUMBER, 
  in_RATINGDATE IN DATE 
) AS 
BEGIN
  INSERT INTO newReview (rid, vid, stars, RatingDate)
    SELECT r.id, rr.id, in_stars, in_RatingDate
    FROM restaurant r JOIN
         reviewer rr
         ON r.name = in_RestaurantName AND
            rr.name = in_UserName
END AddNewReview;

This joins to the reference tables to get the ids you need. It will not insert the review if either name does not match. Your question doesn't specify what to do in that case, so that seems like reasonable behavior.

Note that the parameters are named so they don't conflict with column names. And this has listed all columns for the insert -- both are best practices.

Belayer · Accepted Answer · 2020-11-22 17:37:49Z

0

Parameters are defined only by name and data type, they do not contain size specification. So your procedure needs:

create or replace procedure addnewreview(
  restaurantname in varchar2 
, username       in varchar2
, stars          in int
, ratingdate     in date 
...

answered Nov 22, 2020 at 17:37

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Comments

Prashanth Pradeep · Accepted Answer · 2022-08-01 10:19:43Z

0

You need to use To_Date function while calling the stored procedure, like below

 BEGIN
    AddNewReview ('Rangoli', 'Sarah M.', 4, TO_DATE('2020-11-21','YYYY-MM-DD'));
 END;

answered Aug 1, 2022 at 10:19

Prashanth Pradeep

1732 silver badges6 bronze badges

Comments

Adrian Mole · Accepted Answer · 2023-08-06 14:49:29Z

0

The code below might be of some help:

--Create a function (procedure)
    create or replace procedure ADD_NEW_JOB
is
    v_jid jobs.job_id%type;
    v_jtitle jobs.Job_title%type;
begin
    v_jid := 'IT_DBA';
    v_jtitle := 'Database Administrator';
insert into jobs (job_id, job_title) values (v_jid, v_jtitle);
dbms_output.put_line('Inserted '||SQL%ROWCOUNT||' row');
end;
/

--Execute
exec AD_JOB;
select * from jobs;

edited Aug 6, 2023 at 14:49

Adrian Mole

52.1k193 gold badges61 silver badges101 bronze badges

answered Jul 14, 2023 at 13:09

aarux_01

11 bronze badge

1 Comment

Jeremy Caney Over a year ago

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach?

Collectives™ on Stack Overflow

Insert Data Into Table with Stored Procedure in Oracle SQL

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related