Parameter modes - pl sql, PL-SQL Programming

Parameter Modes:

You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Though, you should specify the OUT mode for the output bind arguments used in the RETURNING clause of an INSERT, DELETE, or UPDATE statement. The illustration is as shown below:


sql_stmt VARCHAR2(100);

old_loc VARCHAR2(15);


sql_stmt :=

'DELETE FROM dept WHERE deptno = 20 RETURNING loc INTO :x';




Similarly, when appropriate, you should specify the OUT or IN OUT mode for the bind arguments passed as parameters. For illustration, assume that you want to call the stand-alone procedure as shown below:

CREATE PROCEDURE create_dept (


dname IN VARCHAR2,



deptno := deptno_seq.NEXTVAL;

INSERT INTO dept VALUES (deptno, dname, loc);


To call a procedure from the dynamic PL/SQL block, you should specify the IN OUT mode for the bind argument related with the formal parameter deptno, as shown:


plsql_block VARCHAR2(200);

new_deptno NUMBER(2);

new_dname VARCHAR2(15) := 'ADVERTISING';

new_loc VARCHAR2(15) := 'NEW YORK';


plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';


USING IN OUT new_deptno, new_dname, new_loc;

IF new_deptno > 90 THEN ...


Posted Date: 10/6/2012 8:28:20 AM | Location : United States

Related Discussions:- Parameter modes - pl sql, Assignment Help, Ask Question on Parameter modes - pl sql, Get Answer, Expert's Help, Parameter modes - pl sql Discussions

Write discussion on Parameter modes - pl sql
Your posts are moderated
Related Questions
Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

a. Create a table odetails_new. It has all the attributes of odetails and an additional column called cost, whose values are the product of the quantity and price of the part bein

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

Name Resolution   During the compilation, the PL/SQL compiler relates identifiers like the name of a variable with an address or memory location, actual value, or datatype. Th

Oracle 11 G new features associated with this release:- Enhanced ILM  - Information Lifecycle Management (ILM) has been around for the almost 10 years, but Oracle has made

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean's wants to create a trigger that automatically updates the stock level of all pr

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an