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:

DECLARE

sql_stmt VARCHAR2(100);

old_loc VARCHAR2(15);

BEGIN

sql_stmt :=

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

EXECUTE IMMEDIATE sql_stmt USING OUT old_loc;

...

END;

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 (

deptno IN OUT NUMBER,

dname IN VARCHAR2,

loc IN VARCHAR2) AS

BEGIN

deptno := deptno_seq.NEXTVAL;

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

END;

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:

DECLARE

plsql_block VARCHAR2(200);

new_deptno NUMBER(2);

new_dname VARCHAR2(15) := 'ADVERTISING';

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

BEGIN

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

EXECUTE IMMEDIATE plsql_block

USING IN OUT new_deptno, new_dname, new_loc;

IF new_deptno > 90 THEN ...

END;

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
UNION and OR - SQL SQL supports UNION explicitly but differently from the way it supports JOIN explicitly. As we have seen, JOIN is used exclusively within the FROM clause, su

Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

IF Statement The IF statement executes a series of statement conditionally. Whether the series is executed or not depends on the value of the Boolean expression. Syntax:

Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate

Loop Labels Like the PL/SQL blocks, loops can also be labeled. The label, an undeclared identifier enclosed by double angle brackets, should appear at the beginning of the LOOP

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

Effects of NULL The numeric variable X, perhaps of type INTEGER, might be assigned NULL. In that case the result of evaluating X + 1 is NULL, and so SET Y = X + 1 assigns NULL