CODEWITHSUNDEEP
sqloracle-databaseplsql
Tommy O'Connell
Tommy O'Connell

Reputation: 71

Insert Data Into Table with Stored Procedure in Oracle SQL

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.

Upvotes: 1

Views: 35047

Answers (5)

aarux_01
aarux_01

Reputation: 1

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;

Upvotes: 0

Prashanth Pradeep
Prashanth Pradeep

Reputation: 173

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;

Upvotes: 0

Belayer
Belayer

Reputation: 14861

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 
...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 0

Tawfik Yasser
Tawfik Yasser

Reputation: 86

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;
;

Upvotes: 2

Related Questions