Create an application that will upload student information to the \"StudentRecor

Question

Create an application that will upload student information to the "StudentRecord" database that was created in Project1.  The application must perform the following.

       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"

·         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,

Create BFILE locators for each of the image files

Create 5 INSERT statements into the table

Explanation / Answer

-- DDL query to create StudentRecord table.

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

-- if needed you can create directory using below query

-- and use ADPHOTO_DIR instead of 'C:Temp' in below PL/SQL block

-- PL/SQL block to insert into StudentRecord table

DECLARE
/* Initialize the BFILE locators for downloaded files */
Lob_loc1 BFILE := BFILENAME('C:Temp', 'pict1.jpg');
Lob_loc2 BFILE := BFILENAME('C:Temp', 'pict2.jpg');
Lob_loc3 BFILE := BFILENAME('C:Temp', 'pict3.jpg');
Lob_loc4 BFILE := BFILENAME('C:Temp', 'pict4.jpg');
Lob_loc5 BFILE := BFILENAME('C:Temp', 'pict5.jpg');
BEGIN
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('John', 'Snow', '07-AUG-95', Lob_loc1);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Kevin', 'Lam', '12-JAN-90' Lob_loc2);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Peter', 'Mozaic', '27-MAR-83' Lob_loc3);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Kim', 'Clarks', '27-FEB-87' Lob_loc4);
INSERT INTO StudentRecord (FirstName, LastName, DateOfBirth, Photo) VALUES ('Johnson', 'Ket', '27-JUN-81' Lob_loc5);
COMMIT;
END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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