3i Infotech Placement: Sample Questions 605 - 607 of 1245

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 605

Edit

Write in Short

Short Answer▾

What are stored-procedures? And what are the advantages of using them.

Explanation

  • Database objects that perform a user-defined operation- like data-validation.
  • Extensive or complex processing requiring execution of several SQL statement is saved into store procedures, with all applications calling the procedures.

Advantages of stored-procedures:

  • Easier to maintain and solution of problem as they are modular.
  • Better tuning and performance
  • Easier from a GUI end than using complex queries.
  • Can be part of a separate layer, which allows separating the concerns.
  • Improved security
  • Support automatic execution.
  • Reduced development cost and increased reliability.

Question 606

Edit

Describe in Detail

Essay▾

What is 5NF?

Explanation

  • A relation R is in Fifth Normal Form (5NF) if and only if the following conditions are satisfied one by one:
    • R is already in 4NF.
    • It cannot be further non-loss decomposed.
  • 5NF is useful from a theoretical point of view to complete the picture of the further normal forms.
Example of Fifth Normal Form

Question 607

Edit

Describe in Detail

Essay▾

What is DYNAMIC SQL method 4?

Explanation

  • A Method 4 scenario is one in which any dynamic statement can be programmatically understood, executed and fetched without knowing any of the structures involved at the time of compilation.
  • Does not support object types, cursor variables, arrays of structs, DML returning clauses, Unicode variables, and LOBs.
  • Imagine the various IDEs and middle-tier applications we use to query data from Oracle.
  • Whether they be sqlplus, TOAD, SQL Developer or homegrown applications in Java, PHP, C ++ etc, one thing that these tools and languages have in common is the ability to take a cursor or ref cursor and fully display its associated result set, regardless of the structure of the SQL statement we execute.
  • These tools and languages are able to understand the record structures that are being returned. This is possible due to the various protocols used to access Oracle (OCI, OCCI, JDBC, ODBC etc.) .
  • We can easily prepare and parse a dynamic statement. We can also programmatically handle bind variables without knowing how many we are going to bind (if we use DBMS_SQL) .
  • We can execute this statement without needing to know its form and structure.
  • However, what do we fetch the returning data into- In PL/SQL, we regularly fetch into variables, records and collections of records, but regardless of which variable type we use, we need to know its structure at compile time.
  • For this reason, true Method 4 dynamic SQL is not possible in native, static PL/SQL.
  • With the Oracle Data Cartridge framework, we have an alternative method of achieving Method 4 scenarios for dynamic statements that return datasets.

Developed by: