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
THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

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

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value

Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

Create a procedure named DDPROJ_SP that retrieves project information for a specific project based on a project ID. The procedure should have two parameters: one to accept a projec

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Example of COALESCE operator Example: Give the total of marks for each exam (simplified solution) SELECT CourseId, COALESCE ((SELECT SUM (Mark) FROM EXAM_MARK AS EM