How can we call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL?

Correct Answer: The UTL_FILE database package can be used to read and write operating system files You need to have read /write access rights in that directory before the package can be used Example to write file: Fhandler is a variable of type UTL_FILEFILE_TYPE UTL_FILEPUTF(fHandler, 'Im writing to a file\n'); Example to read file: UTL_FILEGET_LINE(fHandler, buf);

Correct Answer: One can debug PL/SQL program by printing the output using DBMS_OUTPUT package Put_line can be used to display a line as shown below: BEGIN dbms_outputput_line(?Sample line'); END;

Correct Answer: Even though both PL/SQL and Java (or) NET can be used, PL/SQL stands above these two in terms of integration overhead This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java

Correct Answer: SQL is a structured query language while PL/SQL is an extension of SQL by introducing a procedural flow PL/SQL has blocks of statements PL/SQL works like other procedural languages and has concepts like control statements, sequential statements, exception handling etc

Correct Answer: - Because of the block nature, multiple statements are processed at once thereby improving performance - PL/SQL handles exceptions on catching which, action can be taken - PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges

Correct Answer: A collection just like an array is an ordered group of elements of the same type Each elements position is determined by a unique subscript Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings They can be declared as: TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)]; INDEX BY key_type; Example: TYPE studenttyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; stud_tab studenttyp; Nested tables:- they hold random number of elements and use sequential numbers as sub scripts They can be declared as: TYPE type_name IS TABLE OF element_type [NOT NULL]; Example: TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); Varrays: Holds a fixed number of elements which can be changed in run time They can be declared as: TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL]; Example: TYPE Calendar IS VARRAY(366) OF DATE;

Correct Answer: A record data type represents a data type for that row in a database table It lets u define your own records and not your own fields

Correct Answer: - %FOUND This is a Boolean variable which evaluates to TRUE if the last row is successfully fetched - %NOTFOUND This is a Boolean variable which evaluates to TRUE if the last row is not successfully fetched This means there are no more rows to fetch - %ROWCOUNT Returns the number of rows fetched by the cursor - %ISOPEN If the cursor is open, it evaluates to TRUE else FALSE

Correct Answer: 1)Divide by zero ? This is raised when any number is attempted to divide by zero 2)TOO MANY ROWS - A SELECT INTO statement returns more than one row 3)CASE_NOT_FOUND - No choice in the WHEN clause of a case statement is selected 4)LOGIN_DENIED - An attempt to login with an invalid username or password 5)PROGRAM_ERROR - An internal PL/SQL problem

Correct Answer: PL/SQL exceptions are raised using the RAISE command This command is used when exceptions are defined by programmer and not implicit exceptions Example: Declare and raising an exception: DECLARE short_of_attendance EXCEPTION; min_attendance NUMBER(4); BEGIN IF min_attendance < 10 THEN RAISE short_of_attendance; END IF; EXCEPTION WHEN short_of_attendance THEN -- handle the error END;