What is a Trigger? Explain Types of PL/SQL Triggers.

Correct Answer: Named PL/SQL blocks of code which can be invoked using parameters are called PL/SQL sub programs Advantages of PL/SQL subprograms are - The application makes a single call to the database to run a block of statements which improves performance against running SQL multiple times This will reduce the number of calls between the database and the application - PL/SQL is secure since the code resides inside the database thus hiding internal database details from the application The application will only make a call to the PL/SQL sub program - PL/SQL and SQL go hand in hand so there would be no need of any translation required between PL/SQL and SQL

Correct Answer: Cursors help you manipulate the information retrieved by select statements This can be done by assigning a name to the cursor Example: CURSOR emp_cur IS SELECT emp_number from employee_tbl where employee_name = name_in; Types of Cursors: Implicit cursors- These cursors are not declared by the programmer They are issued when the SQL statement is executed The open, close and fetching is done by itself Example: UPDATE employee SET salary = salary * 21; Here, an implicit cursor is issued to identify the set of rows in the table which would be affected by the update Explicit cursors- These cursors are defined by programmer They are used in queries that return multiple rows Example: CURSOR emp_cur IS SELECT emp_number from employee_tbl where employee_name = name_in;

Correct Answer: - Mask the real name and owner of an object - Provide public access to an object - Provide location transparency for tables, views or program units of a remote database - Simplify the SQL statements for database users

Correct Answer: ANALYZE command is used to perform various functions on index, table, or cluster, as listed below: - It helps in dentifying migrated and chained rows of the table or cluster - It helps in validating the structure of the object - It helps in collecting the statistics about object used by the optimizer They are then stored in the data dictionary - It helps in deleting statistics used by object from the data dictionary

Correct Answer: $filename = "/home/user/guest/newfiletxt"; $file = fopen( $filename, "w" ); if( $file == false ) { echo ( "Error in opening new file" ); exit(); } fwrite( $file, "This is a simple test\n" ); fclose( $file );

Correct Answer: Oracle supports 3 transaction isolation levels: - Read committed (default) - Serializable transactions - Read only

Correct Answer: Insert image into a table Create the following table: create table pics_table ( bfile_id number, bfile_desc varchar2(30), bfile_loc bfile, bfile_type varchar2(4)) TABLESPACE appl_data storage (initial 1m next 1m pctincrease 0) Insert Query: INSERT INTO pics_table VALUES(4,'test image',bfilename('GIF_FILES','TestJPG'),'JPEG');

Correct Answer: All the above

Correct Answer: DocumentDB is a NoSQL document-oriented database and records saves in key-value pairs It's same as the other NoSQL document-oriented such as MongoDB

Correct Answer: 12

Explanation:

We can apply at max of 12 triggers in a table.