Parameter modes - pl sql, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Parameter modes - pl sql

Providing results of queries, Providing Results of Queries Expressing ...

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Sql, If two relations R and S are joined, then the non matching tuples of b...

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.

Collections in pl sql, Collections:   The collection is an ordered gr...

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

Conditionals - sql, Conditionals - SQL At first sight SQL does not app...

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Join and and in sql, JOIN and AND in SQL In this Section is all about ...

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Package specification, The Package Specification The package specificat...

The Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to t

Parameter and keyword description - update statement, Parameter and Keyword...

Parameter and Keyword Description:   table_reference: This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for wh

Explicit cursors, Explicit Cursors The set of rows returned by the que...

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Max and min operator in sql, MAX and MIN operator in SQL Example: ...

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

Declaring exceptions - user-defined exceptions, Declaring Exceptions T...

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd