Passing NULL Values

You will encounter special moments when you want to pass a NULL value as a bind argument, as follows:

EXECUTE IMMEDIATE 'UPDATE employee SET salary =:newsal WHERE hire_date IS NULL' USING NULL;

You will, however, get this error:

PLS-00457: in USING clause, expressions have to be of SQL types

Basically, this is saying that NULL has no datatype, and "no datatype" is not a valid SQL datatype.

So what should you do if you need to pass in a NULL value? You can do one of two things:

· Hide the NULL value behind a variable façade, most easily done with an uninitialized variable, as shown here:

· DECLARE· /* Default initial value is NULL */· no_salary_when_fired NUMBER;· BEGIN· EXECUTE IMMEDIATE · 'UPDATE employee SET salary =:newsal· WHERE hire_date IS NULL' · USING no_salary_when_fired;END;

· Use a conversion function to convert the NULL value to a typed value explicitly:

· BEGIN· EXECUTE IMMEDIATE · 'UPDATE employee SET salary =:newsal· WHERE hire_date IS NULL' · USING TO_NUMBER (NULL);END;

15.4 Working with Objects and Collections

One of the most important advantages of NDS over DBMS_SQL is its support for post-Oracle7 datatypes, such as objects and collections. You don't need to change the structure of the code you write in NDS to use it with these datatypes.

Suppose that I am building an internal administrative system for the national health management corporation Health$.Com. To reduce costs, the system will work in a distributed manner, creating and maintaining separate tables of customer information for each for-profit hospital owned by Health$.Com.

I'll start by defining an object type (person) and VARRAY type (preexisting_conditions), as follows:

CREATE TYPE person AS OBJECT (name VARCHAR2(50), dob DATE, income NUMBER); CREATE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25);

Once these types are defined, I can build a package to manage my most critical health-related information—data needed to maximize profits at Health$.Com. Here is the specification:

/* File on web: health$.pkg */CREATE OR REPLACE PACKAGE health$AS PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2); PROCEDURE add_profit_source (hosp_name IN VARCHAR2, pers IN Person, cond IN preexisting_conditions); PROCEDURE minimize_risk (hosp_name VARCHAR2, min_income IN NUMBER:= 100000, max_preexist_cond IN INTEGER:= 0); PROCEDURE show_profit_centers (hosp_name VARCHAR2); END health$;

With this package, I can do the following:

· Set up a new hospital, which means create a new table to hold information about that hospital. Here's the implementation from the body:

· FUNCTION tabname (hosp_name IN VARCHAR2) IS· BEGIN· RETURN hosp_name || '_profit_center';· END;· · PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2) IS· BEGIN· EXECUTE IMMEDIATE · 'CREATE TABLE ' || tabname (hosp_name) || ' (· pers Person, · cond preexisting_conditions)· NESTED TABLE cond STORE AS cond_st';END;

· Add a "profit source" (formerly known as a "patient") to the hospital, including his or her pre-existing conditions. Here's the implementation from the body:

· PROCEDURE add_profit_source (· hosp_name IN VARCHAR2, · pers IN Person, · cond IN preexisting_conditions)· IS· BEGIN· EXECUTE IMMEDIATE · 'INSERT INTO ' || tabname (hosp_name) || · ' VALUES (:revenue_generator,:revenue_inhibitors)' · USING pers, cond;END;

The use of objects and collections is transparent. I could be inserting scalars like numbers and dates, and the syntax and code would be the same.

· Minimize the risk to the health maintenance organization's bottom line by removing any patients who have too many pre-existing conditions or too little income. This is the most complex of the programs; here is the implementation:

· PROCEDURE minimize_risk (· hosp_name VARCHAR2,· min_income IN NUMBER:= 100000,· max_preexist_cond IN INTEGER:= 1)· IS· cv RefCurTyp;· human Person;· known_bugs preexisting_conditions;· · v_table VARCHAR2(30):= tabname (hosp_name);· v_rowid ROWID;· BEGIN· /* Find all rows with more than the specified number· of preconditions and deny them coverage. */ · OPEN cv FOR· 'SELECT ROWID, pers, cond· FROM ' || v_table || ' alias· WHERE (SELECT COUNT(*) FROM TABLE (alias.cond))· > ' ||· max_preexist_cond || · ' OR· alias.pers.income < ' || min_income; · LOOP· FETCH cv INTO v_rowid, human, known_bugs;· EXIT WHEN cv%NOTFOUND; · EXECUTE IMMEDIATE · 'DELETE FROM ' || v_table ||· ' WHERE ROWID =:rid'· USING v_rowid;· END LOOP;· CLOSE cv;END;

I decided to retrieve the ROWID of each profit source so that when I do the DELETE it would be easy to identify the row. It would be awfully convenient to make the query FOR UPDATE, and then use "WHERE CURRENT OF cv" in the DELETE statement, but that is not possible for two reasons: (1) The cursor variable would have to be globally accessible to be referenced inside a dynamic SQL statement, and (2) You cannot declare cursor variables in packages because they don't have persistent state. See the later section Section 15.5.3 for more details.

15.5 Building Applications with NDS

By now, you should have a solid understanding of how native dynamic SQL works in PL/SQL. This section covers some topics you should be aware of as you start to build production applications with this new PL/SQL feature.