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
Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

Name Resolution In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g.

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

query to Find the account numbers of all customers whose balance is more than 10,000 $