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;
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.