OraFAQ Forum: SQL & PL/SQL » How to insert BLOB files into tables

Home » SQL & PL/SQL » SQL & PL/SQL » How to insert BLOB files into tables (Oracle 11g, Release 11.2.0.2.0, Red Hat)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to insert BLOB files into tables [message #534249]

Sun, 04 December 2011 20:14

sandy_bridge_655k
Messages: 55
Registered: November 2011

Member

Hi, I created a music database, and I'm having trouble inserting the audio, video, and lyrics (.doc) into their respective tables. I searched through the forums and found some example code, but I'm not sure how to modify it to fit my purposes.

What I need is a procedure that can insert a complete record into the track table (including an .mp3 file for each row), one that can insert a record into the lyrics table (including .doc file for each row), and a procedure that can insert a single record into the Video table (including an .mv4 file).

Here's the DDL:

CREATE TABLE Artist(
artist_id number(9),
artist_name varchar(30),
country char(2),
num_albums number(3),
num_songs number(5)
);

ALTER TABLE Artist
add CONSTRAINT artist_pk PRIMARY KEY (artist_id);

CREATE TABLE Album(
album_id number(9),
album_title varchar(45),
artist_id number(9),
genre varchar(20),
num_songs_in_album number(3)
);

ALTER TABLE Album
add CONSTRAINT album_pk PRIMARY KEY (album_id);

ALTER TABLE Album
add CONSTRAINT artist_fk FOREIGN KEY (artist_id) REFERENCES Artist(artist_id);

CREATE TABLE Track(
track_id number(9),
artist_id number(9),
album_id number(9),
content BLOB
);

ALTER TABLE Track
add CONSTRAINT track_pk PRIMARY KEY (track_id);

ALTER TABLE Track
add CONSTRAINT album_fk FOREIGN KEY (album_id) REFERENCES Album(album_id);

ALTER TABLE Track
add CONSTRAINT artist2_fk FOREIGN KEY (artist_id) REFERENCES Artist(artist_id);


CREATE TABLE Lyrics (
lyric_id number(9),
track_id number(9),
version varchar(30),
content BLOB
);

ALTER TABLE Lyrics
add CONSTRAINT lyrics_pk PRIMARY KEY ( lyric_id);

ALTER TABLE Lyrics
add CONSTRAINT track_fk FOREIGN KEY (track_id) REFERENCES Track (track_id);

CREATE TABLE Video(
Video_id number(9),
track_id number(9),
producer varchar(25),
director varchar(25),
special_guest varchar(25),
content BLOB,
type char(15)
);

ALTER TABLE Video
add CONSTRAINT video_id PRIMARY KEY (video_id);

ALTER TABLE Video
add CONSTRAiNT track_fk_2 FOREIGN KEY (track_id) REFERENCES Track (track_id);

insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1234,'2-PAC','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1235, 'Blink 182' ,'US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1236,'James Durbin','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1237, 'Jay-Z' ,'US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1238,'Katy Perry','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1239, 'Machine Head' ,'US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1240,'Megadeth','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values(1241, 'Nickelback', 'US', 0, 0);
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1242,'Nirvana','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1243, 'Outkast' ,'US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1244,'Pearl Jam','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1245, 'Stick to Your Guns' ,'US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values (1246,'Switchfoot','US', 0, 0); 
insert into artist (artist_id, artist_name, country, num_albums, num_songs) 
values(1247, 'The Pharcyde', 'US', 0, 0);


insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4321, 'Me Against The World', 1234, 'Hip Hop', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4322, 'Enema of the State', 1235, 'Alternative Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4323, 'Memories of a Beautiful Disaster', 1236, 'Alternative Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4324, 'The Blueprint', 1237, 'Hip Hop', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4325, 'Teenage Dream', 1238, 'Pop', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4326, 'Unto the Locust', 1239, 'Alternative Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4327, 'Th1rt3en', 1240, 'Metal', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4328, 'Here and Now', 1241, 'Pop', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4329, 'Nevermind', 1242, 'Alternative Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4330, 'Southernplayalisticadillacmuzik', 1243, 'Hip Hop', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4331, 'Rearview Mirrors', 1244, 'Alternative Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4332, 'For What Its Worth', 1245, 'Alternative Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4333, 'Vice Verses', 1246, 'Rock', 0);
insert into album (album_id, album_title, artist_id, genre, num_songs_in_album) 
values (4334, 'Bizarre Ride II', 1247, 'Hip Hop', 0);

[Updated on: Sun, 04 December 2011 23:22] by Moderator