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
Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Parameter and Keyword Description: cursor_variable_name: This identifies a cursor variable or the parameter formerly declared within the present scope. host_cursor_va


Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP