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
Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

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

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

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

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