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
Pass the nulls to a dynamic SQL: Passing Nulls: Assume that you want to pass the nulls to a dynamic SQL statement. For illustration, you may write the EXECUTE IMMEDIATE

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Methods: In normal, a method is a subprogram declared in an object type specification using the keyword MEMBER or STATIC. The method cannot have similar name as the object typ

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement