Oracle Interview-8

1. What is a pseudo column. Give some examples

It is a column, which is a actual column in the table.

Eg USER, UID, Sysdate, ROWNUM, ROWID, NULL, and level.

Suppose Customer table is there has different columns like Customer no, payments. What would be is the query to select top three max payments.

For top N queries, see http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post

2. What is the purpose of a cluster.

Oracle does not allow a user to specifically locate tables, and since that's a part of the RDBMS. However, for the purpose of increasing performance, Oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placemen T were left to the RDBMS.

3. What is a cursor.

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you Name a work area and access it stored information a cursor is a mechanism used to fetch more than one row in a PL/SQL bl Ock.

4. Difference between a implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all of the data manipulation statements, including quries that return is only one row. However,queries that return to more than one row you must declare an explicit cursor or use a cursor for loop.

EXPLICIT cursor is a cursor in which the cursor name was explicitly assigned to a SELECT statement via the cursor ... is statement. An implicit cursor was used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors was used to process multirow SELECT statements An implicit cursor was used to process INSERT, UPDATE, D ELETE and single row SELECT. . into statements.

5. What is cursor attributes

%rowcount%notfound%found%isopen

6. What's a cursor for loop.

Cursor for Loop was a loop where Oracle implicitly declares a loop variable, the loop index that of the same record type as The cursor ' s record.

7. Difference between NO DATA FOUND and%notfound

NO DATA FOUND is a exception raised only for the SELECT .... into statements when the WHERE clause of the Querydoes no match any rows. When the WHERE clause of the explicit cursor does not match any rows The%notfound attribute are set to TRUE instead.

8. What is a SELECT for UPDATE cursor represent.

SELECT ... From ... For ... Update[of column-reference][nowait] The processing done in a fetch loops modifies the rows that has been retrieved by the Cursor. A convenient the modifying the rows is done by a method with the parts:the for UPDATE clause in the cursor declaration , where current is CLAUSE in an UPDATE or declaration statement.

9. What's the ' WHERE current of ' clause does in a cursor.

LOOP

                         SELECT  num_credits  into  v_numcredits from  classes                         WHERE  dept=123 and course=101;                         UPDATE  students                         SET current_credits=current_credits+v_numcredits                         WHERE Current of  X;                         END  LOOP                         COMMIT;                         END;

What is the use of a cursor variable? How it is defined.

A cursor variable is associated with different statements on run time, which can hold different values at run time. Static cursors can only is associated with one run time query. A cursor variable is reference type (like a pointer in C). Declaring a cursor Variable:type type_name is REF cursor RETURN return_type type_name is the name of the reference type,r Eturn_type is a record type indicating the types of the select list that would eventually be returned by the cursor VARIABL E.

What should is the return type for a cursor variable. Can we use a scalar data type as return type.

The return type for a cursor must is a record type. It can be declared explicitly as a user-defined or%rowtype can be used. Eg TYPE T_studentsref is REF CURSOR RETURN students%rowtype

How do you open and close a cursor variable. Why it is required.

OPEN cursor variable for SELECT ... Statement CLOSE cursor variable in order to associate a cursor variable with a particular SELECT Statement OPEN syntax is Used. In order to free the resources used for the query CLOSE statement is used.

How to were passing cursor variables in PL/SQL 2.2.

In PL/SQL 2.2 Cursor variables cannot is declared in a package. This was because the storage for a cursor variable have to being allocated using PRO*C or OCI with version 2.2,the only means O F passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Can cursor variables is stored in PL/SQL tables. If Yes how. If not why.

No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

Difference between procedure and function.

Functions is named PL/SQL blocks that return a value and can is called with arguments procedure a named block the can be called with parameter. A procedure all is a PL/SQL statement by itself and while a Function call is called as part of an expression.

What is different modes of parameters used in functions and procedures.

In Out INOUT

Difference between a formal and an actual parameter

The variables declared in the procedure and which is passed, as arguments is called actual, the parameters in the proced Ure declaration. Actual parameters contain the values that is passed to a procedure and receive results. Formal parameters is the placeholders for the values of actual parameters

The default values of Can assigned to actual parameters.

Yes

Can a function take out parameters. If not why.

Yes. A function return a value, but can also has one or more out parameters. It is the best practice, however to use a procedure rather than a function if you had multiple values to return.

What's syntax for dropping a procedure and a function. is these operations possible.

              Drop Procedure procedure_name              drop Function function_name

What is ORACLE precompilers.

Using ORACLE precompilers, statements and PL/SQL blocks can be contained inside 3GL programs written in C,c++,cobol,pa SCAL, FORTRAN,PL/1 and ADA. The precompilers is known as Pro*c,pro*cobol,... This form of PL/SQL is known as embedded pl/sql,the language in which PL/SQL is embedded are known as the host language. The Prcompiler translates the embedded SQL and PL $ ststements into calls to the Precompiler Runtime library. The output must is compiled and linked with this library to creater an executable.

What is OCI. What is its uses.

Oracle call Interface are a method of accesing database from a 3GL program. Uses--no Precompiler is required,pl/sql blocks be executed like the other DML statements.

                     The OCI Library provides                    -functions to parse SQL statemets                    -bind input variables                    -bind output Variables                    - Execute statements                    -fetch the results

Difference between database triggers and form triggers.

A) Data base trigger (DBT) fires when a DML operation was performed on a data base table. Form Trigger (FT) Fires when user presses a key or navigates between in the screen B) Can is row level or statement Level No distinction between row level and statement level. c) can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. D) Can is fired from any session executing the triggering DML statements. Can is fired only from the form that define the trigger. e) Can cause other database triggers to fire. Can cause other database triggers to fire,but not the other form triggers.

Utl_file. What is different procedures and functions associated

With it. Utl_file is a package this adds the ability to read and write to operating system files procedures associated with it was FCLOSE, Fclose_all and 5 procedures to output data to a file PUT, Put_Line, New_line, Putf, FFLUSH. PUT, FFLUSH. Put_line,fflush. New_line. Functions associated with it is FOPEN, ISOPEN.

Can you use a COMMIT statement within a database trigger.

No

What is the maximum buffer size, which can be specified using the Dbms_output. ENABLE function?

1,000,000

From:http://www.orafaq.com/wiki/interview_questions

Oracle Interview-8