Technical Sample Questions : C |
Java | Unix |
Operating Systems |
Oracle Sample Questions ›› Oracle General Questions
Technical Sample Questions
Oracle Sample Questions : Oracle General Questions
« Previous || Next »
Oracle General Sample Question Number: 1-10|11-20|21-30|31-40|41-50|51-56
- What is difference between a formal and an actual parameter?
Answer: The formal parameters are the names that are declared in the parameter list of the header of a module.The actual parameters are the values or expressions placed in the parameter list of the actual call to the module.
- What are different modes of parameters used in functions and procedures?
Answer: There are three different modes of parameters:
- IN- The IN parameter allows you to pass values in to the module, but will not pass anything out of the module and back to the calling PL/SQL block.In other words, for the purposes of the program, its IN parameters function like constants.Just like constants, the value of the formal IN parameter cannot be changed within the program.You cannot assign values to the IN parameter or in any other way modify its value.
IN is the default mode for parameters.IN parameters can be given default values in the program header.
- OUT - An OUT parameter is the opposite of the IN parameter.Use the OUT parameter to pass a value back from the program to the calling PL/SQL block.An OUT parameter is like the return value for a function, but it appears in the parameter list and you can, of course, have as many OUT parameters as you like.
Inside the program, an OUT parameter acts like a variable that has not been initialised.In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is).During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter.When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter.That value is then available in the calling PL/SQL block.
- IN OUT - With an IN OUT parameter, you can pass values into the program and return a value back to the calling program (either the original, unchanged value or a new value set within the program).The IN OUT parameter shares two restrictions with the OUT parameter:
An IN OUT parameter cannot have a default value.
An IN OUT actual parameter or argument must be a variable.It cannot be a constant, literal, or expression, since these formats do not provide a receptacle in which PL/SQL can place the outgoing value.
- Difference between procedure and function.
Answer: A function always returns a value, while a procedure does not.When you call a function you must always assign its value to a variable.
- Can cursor variables be stored in PL/SQL tables.If yes how.If not why?
Answer: Yes.Create a cursor type - REF CURSOR and declare a cursor variable of that type.
/* Create the cursor type.*/
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
/* Declare a cursor variable of that type.*/
/* Declare a record with same structure as cursor variable.*/
/* Open the cursor variable, associating with it a SQL statement.*/
OPEN company_curvar FOR SELECT * FROM company;
/* Fetch from the cursor variable.*/
FETCH company_curvar INTO company_rec;
/* Close the cursor object associated with variable.*/
- How do you pass cursor variables in PL/SQL?
Answer: Pass a cursor variable as an argument to a procedure or function.You can, in essence, share the results of a cursor by passing the reference to that result set.
- How do you open and close a cursor variable.Why it is required?
Answer: Using OPEN cursor_name and CLOSE cursor_name commands.The cursor must be opened before using it in order to fetch the result set of the query it is associated with.The cursor needs to be closed so as to release resources earlier than end of transaction, or to free up the cursor variable to be opened again.
- What should be the return type for a cursor variable.Can we use a scalar data type as return type?
Answer: The return type of a cursor variable can be %ROWTYPE or record_name%TYPE or a record type or a ref cursor type.A scalar data type like number or varchar can't be used but a record type may evaluate to a scalar value.
- What is use of a cursor variable? How it is defined?
Answer: Cursor variable is used to mark a work area where Oracle stores a multi-row query output for processing.It is like a pointer in C or Pascal.Because it is a TYPE, it is defined as TYPE REF CURSOR RETURN ;
- What WHERE CURRENT OF clause does in a cursor?
Answer: The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
- Difference between NO DATA FOUND and %NOTFOUND
Answer: NO DATA FOUND is an exception which is raised when either an implicit query returns no data, or you attempt to reference a row in the PL/SQL table which is not yet defined.SQL%NOTFOUND, is a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row.