What are the various types of parameter modes in a procedure ?

Question

What are the various types of parameter modes in a procedure ?

asked Aug 21, 2021 by anonymous

1 Answer

Related questions

What are the different modes of mounting a Database with the Parallel Server?

Description : What are the different modes of mounting a Database with the Parallel Server?

Last Answer : Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is ... parallel mode, other instances that are started in parallel mode can also mount the database.

Can you use %RowCount as a parameter to a cursor

Description : Can you use %RowCount as a parameter to a cursor

Last Answer : Yes

Can you pass a parameter to a cursor ?

Description : Can you pass a parameter to a cursor ?

Last Answer : Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median

What is lexical parameter?

Description : What is lexical parameter?

Last Answer : Lexical Parameter is used to replace the where, order by ………conditions at run time

What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command?

Description : What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command?

Last Answer : The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? –

Description : What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? –

Last Answer : What are the factors that affect OPTIMIZER in choosing an Optimization approach? – Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?

Description : Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?

Last Answer : All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

What are the various types of RollBack Segments ?

Description : What are the various types of RollBack Segments ?

Last Answer : Public Available to all instances Private Available to specific instance

What are the various types of database triggers ?

Description : What are the various types of database triggers ?

Last Answer : There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers. Before and After Triggers. Row and Statement Triggers. (3*2*2=12)

What are the various types of Exceptions ?

Description : What are the various types of Exceptions ?

Last Answer : User defined and Predefined Exceptions.

What is the advantage of a stored procedure over a database trigger ?

Description : What is the advantage of a stored procedure over a database trigger ?

Last Answer : We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What are the constructs of a procedure, function or a package ?

Description : What are the constructs of a procedure, function or a package ?

Last Answer : The constructs of a procedure, function or a package are : variables and constants cursors exceptions

What is the difference between a procedure and a function ?

Description : What is the difference between a procedure and a function ?

Last Answer : 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

What are different types of column in reports?

Description : What are different types of column in reports?

Last Answer : There are three types of columns in the report 6i these are: 1) Placeholder Column – Placeholder column is used to store a value for a variable. 2) Formula Column 3) Summary Column

How many types of columns are there and what are they

Description : How many types of columns are there and what are they

Last Answer : Formula columns: For doing mathematical calculations and returning one value Summary Columns: For doing summary calculations such as summations etc. Place holder Columns: These columns are useful for storing the value in a variable

How many rows will the following SQL return

Description : How many rows will the following SQL return

Last Answer : Select * from emp Where rownum < 10; 9 rows.

What is the significance of the & and && operators in PL SQL ?

Description : What is the significance of the & and && operators in PL SQL ?

Last Answer : The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously ... span across different rollback segments or will it terminate ? It will terminate (Please check ).

What is the maximum no. of columns a table can have ?

Description : What is the maximum no. of columns a table can have ?

Last Answer : 254.

Can you Rollback to any savepoint ?

Description : Can you Rollback to any savepoint ?

Last Answer : Yes.

Can you define multiple savepoints ?

Description : Can you define multiple savepoints ?

Last Answer : Yes.

An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?

Description : An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?

Last Answer : No.

What is the difference between unique key and primary key ?

Description : What is the difference between unique key and primary key ?

Last Answer : Unique key can be null; Primary key cannot be null.

What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

Description : What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

Last Answer : The various states of a rollback segment are : ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.

What are the min. extents allocated to a rollback extent ?

Description : What are the min. extents allocated to a rollback extent ?

Last Answer : Two

Can Check constraint be used for self referential integrity ? How ?

Description : Can Check constraint be used for self referential integrity ? How ?

Last Answer : ? Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

Can null keys be entered in cluster index, normal index ?

Description : Can null keys be entered in cluster index, normal index ?

Last Answer : Yes.

Can Long/Long RAW be clustered ?

Description : Can Long/Long RAW be clustered ?

Last Answer : No.

What are the disadvantages of clusters ?

Description : What are the disadvantages of clusters ?

Last Answer : The time for Insert increases.

What are the advantages of clusters ?

Description : What are the advantages of clusters ?

Last Answer : Access time reduced for joins.

What is the use of Data Dictionary ?

Description : What is the use of Data Dictionary ?

Last Answer : Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, et

What is the use of Control files ?

Description : What is the use of Control files ?

Last Answer : Contains pointers to locations of various data files, redo log files, etc.

Can you increase the size of datafiles ? How ?

Description : Can you increase the size of datafiles ? How ?

Last Answer : No (for Oracle 7.0)

Can you increase the size of a tablespace ? How ?

Description : Can you increase the size of a tablespace ? How ?

Last Answer : Yes, by adding datafiles to it.

Describe Oracle database's physical and logical structure

Description : Describe Oracle database's physical and logical structure

Last Answer : Physical : Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.

What are constraining triggers ?

Description : What are constraining triggers ?

Last Answer : A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

What are mutating triggers ?

Description : What are mutating triggers ?

Last Answer : A trigger giving a SELECT on the table on which the trigger is written.

What are cascading triggers? What is the maximum no of cascading triggers at a time?

Description : What are cascading triggers? What is the maximum no of cascading triggers at a time?

Last Answer : When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.

Can views be specified in a trigger statement ?

Description : Can views be specified in a trigger statement ?

Last Answer : No

What is the maximum no. of statements that can be specified in a trigger statement?

Description : What is the maximum no. of statements that can be specified in a trigger statement?

Last Answer : One.

What are the parts of a database trigger ?

Description : What are the parts of a database trigger ?

Last Answer : The parts of a trigger are: A triggering event or statement A trigger restriction A trigger action

Can you pass parameters in packages ? How ?

Description : Can you pass parameters in packages ? How ?

Last Answer : Yes. You can pass parameters to procedures or functions in a package.

Why Create or Replace and not Drop and recreate procedures ?

Description : Why Create or Replace and not Drop and recreate procedures ?

Last Answer : So that Grants are not dropped.

Can 2 functions have same name & input parameters but differ only by return datatype

Description : Can 2 functions have same name & input parameters but differ only by return datatype

Last Answer : No.

Can functions be overloaded ?

Description : Can functions be overloaded ?

Last Answer : Yes.

What is Over Loading and what are its restrictions ?

Description : What is Over Loading and what are its restrictions ?

Last Answer : OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.

Can you call a stored function in the constraint of a table ?

Description : Can you call a stored function in the constraint of a table ?

Last Answer : no

Can you have two stored functions with the same name ?

Description : Can you have two stored functions with the same name ?

Last Answer : Yes.

Can you have two functions with the same name in a PL/SQL block

Description : Can you have two functions with the same name in a PL/SQL block

Last Answer : Yes.

Can we define exceptions twice in same block ?

Description : Can we define exceptions twice in same block ?

Last Answer : No.