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
PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Stored Subprograms Normally, tools (like Oracle Forms) which incorporate the PL/SQL engine can store subprograms locally for later, strictly local execution. Though, to become

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

THEORY OF SPONTANEOUS GENERATION - ABIOGENESIS OR AUTOGENESIS - According to this theory, the existing living communities have originated from non-living organic matter with

SQL Database: So, an SQL database is one whose symbols are organized into a collection of tables. Now, shows an SQL table as the current value of an SQL variable, ENROLMENT, b

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

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

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.