· In SQL Developer, create a table \"StudentRecord\" that contains fields, First

Question

·       In SQL Developer, create a table "StudentRecord" that contains fields, FirstName, LastName, DateOfBirth (Date datatype)and Photo (BFILE datatype)

      Create a directory, "C:Temp", and download five "jpeg" files that contains photographs for individuals.  Files must be named "pict1.jpg" - "pict5.jpg"

.     Create a virtual directory for the physical directory created above (i.e., use CREATE DIRECTORY statement)

·         Write an Anonymous PL/SQL block that will insert five records into the database; the records must include the "jpeg" binary files that contains the photographs associated with each record.  One technique would be,

Explanation / Answer

-- Query to create StudentRecord table.

CREATE TABLE StudentRecord (
FirstName VARCHAR2(20),
LastName VARCHAR2(20),
DateOfBirth DATE,
Photo BFILE
);

-- You can create temp directory handler also using below query

-- CREATE DIRECTORY ADPHOTO_DIR AS 'C:Temp';
-- and use ADPHOTO_DIR instead of 'C:Temp' in below code block

-- PL/SQL code block for insertion of records into StudentRecord table
-- First Initialize BFILE locators for downloaded picture files
-- Then Insert 5 records in StudentRecord table

DECLARE
Lob_loc_1 BFILE := BFILENAME('C:Temp', 'pict1.jpg');
Lob_loc_2 BFILE := BFILENAME('C:Temp', 'pict2.jpg');
Lob_loc_3 BFILE := BFILENAME('C:Temp', 'pict3.jpg');
Lob_loc_4 BFILE := BFILENAME('C:Temp', 'pict4.jpg');
Lob_loc_5 BFILE := BFILENAME('C:Temp', 'pict5.jpg');
BEGIN
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Killy', 'Ben', '17-AUG-95', Lob_loc_1);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Paul', 'Sean', '22-JAN-90' Lob_loc_2);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Tom', 'Harry', '29-MAR-83' Lob_loc_3);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Jack', 'Bone', '05-FEB-87' Lob_loc_4);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Salman', 'Khan', '02-JUN-81' Lob_loc_5);
COMMIT;
END;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.