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
Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Exception handling In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Referencing Records Unlike the elements in a collection, that are accessed using subscripts, the fields in a record are accessed by name. To reference an individual field, you

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Existential Quantification - SQL Existential quantification-stating that something is true of at least one object under consideration-can be expressed by OR(r,c), meaning tha

Deleting Objects You can use the DELETE statement to eradicate objects from an object table. To eradicate objects selectively, you use the WHERE clause, as shown below: BEG

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

to write a heap sort program usin pl-sql