Oracle PL/SQL and Insert Statement

  • Thread starter Thread starter Matrixhasu77
  • 4 comments
  • 888 views
Matrixhasu77

Matrixhasu77

Messages
2,491
Hi all, I'm having a little difficulty with a PL/SQL procedure in Oracle I'm working on for a class. I am attempting to insert values into a table using and insert statement with a select query returning multiple values. However, when I attempt to run the entire PL/SQL procedure, it errors out with an error about the lines using the select query not being properly ended.

Here is the entire PL/SQL Procedure:
Code: 
 [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Declare[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_inventory_id number := 10074;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_inventory_type varchar2(20) := 'MOVIE';[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_quantity number := 1;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_reorder_level number := 0;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_movie_id number := 74;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_movie_title varchar2(50) := 'The Revenge of Mothra';[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_movie_rating varchar2(7) := 'PG-13';[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_pass_allowed varchar2(1) := 'N';[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_week_in_run number := 1;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]v_start_date date := '23-SEPT-2005';[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Begin[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into inventory(inventory_id, inventory_type, quantity, reorder_level) values(v_inventory_id, v_inventory_type, v_quantity, v_reorder_level);[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie(movie_id, movie_title, inventory_id, movie_rating, pass_allowed) values(v_movie_id, v_movie_title, v_inventory_id, v_movie_rating, v_pass_allowed);[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie_run(site_id, movie_id, week_in_run, start_date) values(site_id, v_movie_id, v_week_in_run, v_start_date) select site_id from site;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie_run_times(site_id, movie_id, week_in_run, showtime) values(site_id, v_movie_id, v_week_in_run, to_date('23-SEPT-2005 19:00', 'DD-MON-YYYY HH24:MI')) select site_id from site;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie_run_times(site_id, movie_id, week_in_run, showtime) values(site_id, v_movie_id, v_week_in_run, to_date('23-SEPT-2005 21:30', 'DD-MON-YYYY HH24:MI')) select site_id from site;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]select * from inventory where inventory_id=v_inventory_id;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]select * from movie where movie_id=v_movie_id;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]select * from movie_run where movie_id=v_movie_id;[/SIZE][/FONT][/SIZE][/FONT]
  [FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]select * from movie_run_times where movie_id=v_movie_id;[/SIZE][/FONT][/SIZE][/FONT]
[FONT=&quot][FONT=Courier New][SIZE=2]End;[/SIZE][/FONT][/FONT]

The three lines that are giving me difficulty are:
Code: 
[FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie_run(site_id, movie_id, week_in_run, start_date) values(site_id, v_movie_id, v_week_in_run, v_start_date) select site_id from site;[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie_run_times(site_id, movie_id, week_in_run, showtime) values(site_id, v_movie_id, v_week_in_run, to_date('23-SEPT-2005 19:00', 'DD-MON-YYYY HH24:MI')) select site_id from site;[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Courier New][SIZE=2][FONT=&quot][SIZE=2]Insert into movie_run_times(site_id, movie_id, week_in_run, showtime) values(site_id, v_movie_id, v_week_in_run, to_date('23-SEPT-2005 21:30', 'DD-MON-YYYY HH24:MI')) select site_id from site;[/SIZE][/FONT][/SIZE][/FONT]


I think the problem is something with the select statement because it should return multiple values so that the insert statement inserts the data for all site_ids selected. Any suggestions on how to correct this are greatly appreciated. I'm completely stumped on how to fix this problem.

[FONT=&quot]
[/FONT]
 
GilesGuthrie
So you don't need help with this now?
Click to expand...

No. I talked to a professor of mine and he helped me discover the problem. I had the insert written incorrectly. Instead of using the values part of the statement, I needed only a select instead. You can go ahead and lock this thread.

Thank you though!
 
I wasn't looking to lock. I thought I might have something to contribute. I'm in the process of advancing with MySQL at the minute, so may have been of some help. But probably not!!!
 

Latest Posts

Back