Pass the nulls to a dynamic sql, PL-SQL Programming

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 statement as shown below:

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;

Though, this statement fails with a bad expression error as the literal NULL is not allowed in the USING clause. To work around this constraint, just replace the keyword NULL with an uninitialized variable, which is as shown below:

DECLARE

a_null CHAR(1); -- set to NULL automatically at run time

BEGIN

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;

END;

 

Posted Date: 10/6/2012 8:34:23 AM | Location : United States







Related Discussions:- Pass the nulls to a dynamic sql, Assignment Help, Ask Question on Pass the nulls to a dynamic sql, Get Answer, Expert's Help, Pass the nulls to a dynamic sql Discussions

Write discussion on Pass the nulls to a dynamic sql
Your posts are moderated
Related Questions
Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

V ariables and Constants in PL/SQL The PL/SQL permits you to declare constants and variables, and then use them in SQL and procedural statements anywhere in the expression. Th

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

write the program for traffic control system with 10 second, 15 secod, and 20 second delay

PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you