What is the difference between a procedure and a function ?

ORACLE Interview Questions and Answers (Part 5)

(Continued from previous question...)

3.What is the difference between a procedure and a function ?

Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

(Continued on next question...)

Other Interview Questions

• Is it possible to split the print reviewer into more than one region?
• Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
• For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
• Can a field be used in a report without it appearing in any data group?
• The join defined by the default data link is an outer join yes or no?
• Can a formula column referred to columns in higher group?
• Can a formula column be obtained through a select statement?
• Is it possible to insert comments into sql statements return in the data model editor?
• Is it possible to disable the parameter from while running the report?
• When a form is invoked with call_form, Does oracle forms issues a save point?
• Explain the difference between a hot backup and a cold backup and the benefits associated with each.
• You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
• How do you switch from an init.ora file to a spfile?
• Explain the difference between a data block, an extent and a segment.
• Give two examples of how you might determine the structure of the table DEPT.
• Where would you look for errors from the database engine?
• Compare and contrast TRUNCATE and DELETE for a table.
• Give the reasoning behind using an index.
• Give the two types of tables involved in producing a star schema and the type of data they hold.
• What type of index should you use on a fact table?
• Give two examples of referential integrity constraints.
• A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
• Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
• What command would you use to create a backup control file?
• Give the stages of instance startup to a usable state where normal users may access it.
• What column differentiates the V$ views to the GV$ views and how?
• How would you go about generating an EXPLAIN plan?
• How would you go about increasing the buffer cache hit ratio?
• Explain an ORA-01555
• Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
• How would you determine the time zone under which a database was operating?
• Explain the use of setting GLOBAL_NAMES equal to TRUE.
• What command would you use to encrypt a PL/SQL application?
• Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
• Explain the use of table functions.
• Name three advisory statistics you can collect.
• Where in the Oracle directory tree structure are audit traces placed?
• Explain materialized views and how they are used.
• When a user process fails, what background process cleans up after it?
• What background process refreshes materialized views?
• How would you determine what sessions are connected and what resources they are waiting for?
• Describe what redo logs are.
• How would you force a log switch?
• Give two methods you could use to determine what DDL changes have been made.
• What does coalescing a tablespace do?
• What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
• Name a tablespace automatically created when you create a database.
• When creating a user, what permissions must you grant to allow them to connect to the database?
• How do you add a data file to a tablespace
• How do you resize a data file?
• What view would you use to look at the size of a data file?
• What view would you use to determine free space in a tablespace?
• How would you determine who has added a row to a table?
• How can you rebuild an index?
• Explain what partitioning is and what its benefit is.
• You have just compiled a PL/SQL package but got errors, how would you view the errors?
• How can you gather statistics on a table?
• How can you enable a trace for a session?
• What is the difference between the SQL*Loader and IMPORT utilities?
• Name two files used for network connection to a database.
• What is the function of Optimizer ?
• What is Execution Plan ?
• Can one resize tablespaces and data files? (for DBA)
• What is SAVE POINT ?
• What are the values that can be specified for OPTIMIZER MODE Parameter ?
• Can one rename a tablespace? (for DBA)
• What is RULE-based approach to optimization ?
• What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ? )
• How does one create a standby database? (for DBA)
• How does one give developers access to trace files (required as input to tkprof)? (for DBA)
• What are the responsibilities of a Database Administrator ?
• What is a trace file and how is it created ?
• What are the roles and user accounts created automatically with the database?
• What are the minimum parameters should exist in the parameter file (init.ora) ?
• Why and when should I backup my database? (for DBA
• What strategies are available for backing-up an Oracle database? (for DBA
• What is the difference between online and offline backups? (for DBA
• What is the difference between restoring and recovering? (for DBA
• How does one backup a database using the export utility? (for DBA
• What are the built_ins used the display the LOV?
• How do you call other Oracle Products from Oracle Forms?
• What is the main diff. bet. Reports 2.0 & Reports 2.5?
• What are the Built-ins to display the user-named editor?
• How many number of columns a record group can have?
• What is a Query Record Group?
• What does the term panel refer to with regard to pages?
• What is a master detail relationship?
• What is a library?
• What is an anchoring object and what is its use? What are the various sub events a mouse double click event involves?
• Use the add_group_column function to add a column to record group that was created at a design time?
• What are the various sub events a mouse double click event involves? What are the various sub events a mouse double click event involves?
• What is the use of break group? What are the various sub events a mouse double click event involves?
• What tuning indicators can one use? (for DBA
• 813. What tools/utilities does Oracle provide to assist with performance tuning? (for DBA
• What is STATSPACK and how does one use it? (for DBA
• What are the common RMAN errors (with solutions)? (for DBA
• How can you execute the user defined triggers in forms 3.0 ?
• What ERASE package procedure does ?
• What is the difference between NAME_IN and COPY ?
• What package procedure is used for calling another form ?
• When the form is running in DEBUG mode, If you want to examine the values of global variables and other form variables, What package procedure command you would use in your trigger text ?
• The value recorded in system.last_record variable is of type a. Number b. Boolean c. Character. ?
• What is mean by Program Global Area (PGA) ?
• What is hit ratio ?
• How do you implement the If statement in the Select Statement
• How many types of Exceptions are there
• What are the inline and the precompiler directives
• How do you use the same lov for 2 columns
• How many minimum groups are required for a matrix report
• What is the difference between static and dynamic lov
• How does one manage Oracle database users? (for DBA
• 831. How does one tune Oracle Wait events? (for DBA
• What is the difference between DBFile Sequential and Scattered Reads?(for DBA
• What is the use of PARFILE option in EXP command ?
• What is the use of TABLES option in EXP command ?
• What is the OPTIMAL parameter?
• How does one use ORADEBUG from Server Manager/ SQL*Plus? (for DBA
• Are there any undocumented commands in Oracle? (for DBA
• If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
• What are the different objects that you cannot copy or reference in object groups?
• What is an OLE?
• Can a repeating frame be created without a data group as a base?
• Is it possible to set a filter condition in a cross product group in matrix reports?
• What is Overloading of procedures ?
• What are the return values of functions SQLCODE and SQLERRM ? What is Pragma EXECPTION_INIT ? Explain the usage ?
• What are the datatypes a available in PL/SQL ?
• What are the two parts of a procedure ?
• What is the basic structure of PL/SQL ?
• What is PL/SQL table ?
• WHAT IS RMAN ? (for DBA
• WHY USE RMAN ? (for DBA
• Explain UNION,MINUS,UNION ALL, INTERSECT ?
• Should the OEM Console be displayed at all times (when there are scheduled jobs)? (for DBA
• Difference between SUBSTR and INSTR ?
• What kind of jobs can one schedule with OEM? (for DBA
• What are the pre requisites ?
• How does one backout events and jobs during maintenance slots? (for DBA
• What are the types of SQL Statement ?
• What is the Oracle Intelligent Agent? (for DBA
• How does one start the Oracle Intelligent Agent? (for DBA
• Where can one get more information about TCL? (for DBA
• Are there any troubleshooting tips for OEM? (for DBA
• What is import/export and why does one need it? (for DBA
• what is a display item?
• How does one use the import/export utilities? (for DBA
• What are the types of visual attribute settings?
• Can one export a subset of a table? (for DBA
• What are the two ways to incorporate images into a oracle forms application?
• Can one monitor how fast a table is imported? (for DBA
• Can one import tables to a different tablespace? (for DBA
• What do you mean by a block in forms4.0?
• How is possible to restrict the user to a list of values while entering values for parameters?
• What is SQL*Loader and what is it used for? (for DBA
• How does one use the SQL*Loader utility? (for DBA
• How can a cross product be created?
• Is there a SQL*Unloader to download data to a flat file? (for DBA
• Can one load variable and fix length data records? (for DBA
• Can one modify data as it loads into the database? (for DBA
• Can one load data into multiple tables at once? (for DBA
• What is the difference between boiler plat images and image items?
• What are the triggers available in the reports?
• Why is a Where clause faster than a group filter or a format trigger?
• Can one selectively load only the records that one need? (for DBA
• Can one skip certain columns while loading data? (for DBA
• How does one load multi-line records? (for DBA
• How can get SQL*Loader to COMMIT only at the end of the load file? (for DBA
• Can one improve the performance of SQL*Loader? (for DBA
• How does one use SQL*Loader to load images, sound clips and documents? (for DBA
• What is the difference between the conventional and direct path loader? (for DBA
• What are the various types of Exceptions ?
• Can we define exceptions twice in same block ?
• What is the difference between a procedure and a function ?
• Can you have two functions with the same name in a PL/SQL block ?
• Can you have two stored functions with the same name ?
• Can you call a stored function in the constraint of a table ?
• What are the various types of parameter modes in a procedure ?
• What is Over Loading and what are its restrictions ?
• Can functions be overloaded ?
• Can 2 functions have same name & input parameters but differ only by return datatype
• What are the constructs of a procedure, function or a package ?
• Why Create or Replace and not Drop and recreate procedures ?
• Can you pass parameters in packages ? How ?
• What are the parts of a database trigger ?
• What are the various types of database triggers ?
• What is the advantage of a stored procedure over a database trigger ?
• What is the maximum no. of statements that can be specified in a trigger statement ?
• Can views be specified in a trigger statement ?
• What are the values of :new and :old in Insert/Delete/Update Triggers ?
• What are cascading triggers? What is the maximum no of cascading triggers at a time?
• What are mutating triggers ?
• What are constraining triggers ?
• Describe Oracle database's physical and logical structure ?
• Can you increase the size of a tablespace ? How ?
• What is the use of Control files ?
• What is the use of Data Dictionary ?
• What are the advantages of clusters ?
• What are the disadvantages of clusters ?
• Can Long/Long RAW be clustered ?
• Can null keys be entered in cluster index, normal index ?
• Can Check constraint be used for self referential integrity ? How ?
• What are the min. extents allocated to a rollback extent ?
• What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
• What is the difference between unique key and primary key ?
• An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
• Can you define multiple savepoints ?
• Can you Rollback to any savepoint ?
• What is the maximum no. of columns a table can have ?
• What is the significance of the & and && operators in PL SQL ?
• Can you pass a parameter to a cursor ?
• What are the various types of RollBack Segments ?
• Can you use %RowCount as a parameter to a cursor ?
• Is the query below allowed : Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15))
• Is the assignment given below allowed : ABC = PQR (Where ABC and PQR are records)
• Is this for loop allowed : For x in &Start..&End Loop
• How many rows will the following SQL return : Select * from emp Where rownum
• How many rows will the following SQL return : Select * from emp Where rownum = 10;
• Which symbol preceeds the path to the table in the remote database ?
• Are views automatically updated when base tables are updated ?
• Can a trigger written for a view ?
• If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
• A table has the following data : [[5, Null, 10]]. What will the average function return ?
• Is Sysdate a system variable or a system function?
• Consider a sequence whose currval is 1 and gets incremented by 1 ...
• Definition of relational DataBase by Dr. Codd (IBM)?
• What is Multi Threaded Server (MTA) ?
• Which are initial RDBMS, Hierarchical & N/w database ?
• What is Functional Dependency
• What is Auditing ?
• While designing in client/server what are the 2 imp. things to be considered ?
• When to create indexes ?
• How can you avoid indexes ?
• What is the result of the following SQL ...
• Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
• Can you alter synonym of view or view ?
• Can you create index on view
• What is the difference between a view and a synonym ?
• What is the difference between alias and synonym ?
• What is the effect of synonym and table name used in same Select statement ?
• What's the length of SQL integer ?
• What is the difference between foreign key and reference key ?
• Can dual table be deleted, dropped or altered or updated or inserted ?
• If content of dual is updated to some value computation takes place or not ?
• If any other table same as dual is created would it act similar to dual?
• For which relational operators in where clause, index is not used ?
• Assume that there are multiple databases running on one machine. How can you switch from one to another ?
• What are the advantages of Oracle ?
• What is a forward declaration ? What is its use ?
• What are actual and formal parameters ?
• What are the types of Notation ?
• What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
• If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
• What are various types of joins ?
• What is a package cursor ?
• If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ?
• What are the various types of queries ??
• What is a transaction ?
• What is implicit cursor and how is it used by Oracle ?
• Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
• What is PL/SQL?
• Is there a PL/SQL Engine in SQL*Plus?
• Is there a limit on the size of a PL/SQL block?
• Can one read/write files from PL/SQL?
• How can I protect my PL/SQL source code?
• ORACLE Interview Questions and Answers (Part 5)
• Oracel Interview Question Only (1)
• Oracel Interview Question Only (2)