 |
|
EnterpriseDB:
LPAD and RPAD Functions
Oracle Tips by Burleson Consulting
|
In
addition, in EnterpriseDB, LPAD and RPAD expect different types of
values such as TEXT and INTEGER. You can modify all of your
existing code to perform explicit type casts, or you can write a
simple set of procedures. In this case, I will create my own LPAD
and RPAD functions using the data types I expect. I will explicitly
cast those types to EnterpriseDB types.
CREATE OR REPLACE FUNCTION lpad(
p_in_string IN VARCHAR2,
p_in_length IN NUMBER,
p_in_fill IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
RETURN lpad(p_in_string::TEXT,
p_in_length::INTEGER,
p_in_fill::TEXT );
END;
CREATE OR REPLACE FUNCTION rpad(
p_in_string IN VARCHAR2,
p_in_length IN NUMBER,
p_in_fill IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
RETURN rpad(p_in_string::TEXT,
p_in_length::INTEGER,
p_in_fill::TEXT );
END;
In
this way, I add a couple of very easy functions and I do not have to
modify my code.
With
that change, the code compiles and runs. I can now add records and
generate reports. That means that everything is working except the
XML interface. As a final step, I will take a closer look at that.
The
only changes needed to the TT_MANAGER_USER_RECORDS package is the
PROCESS_XML_DATA procedure.
The
ADD_XML_RECORD uses an anchored declaration, p_xml_data IN
tt_xml_interface.xml_data%TYPE. Because I have changed the table
and used an anchored declaration, there is no reason to change this
code. This is exactly why using anchored declarations is a best
practice.
There
are two changes required in the PROCESS_XML_DATA procedure. The
first is one I looked at above. The code uses a cursor for loop
with the cursor declared in the loop. I will have to move that to
the declaration section and use a named cursor.
The
other change is the extract code:
ci.xml_data.Extract('/interface/what/text()').getStringVal(),
ci.xml_data.Extract('/interface/who/text()').getStringVal(),
ci.xml_data.Extract('/interface/hours/text()').getStringVal(),
ci.xml_data.Extract('/interface/when/text()').getStringVal(),
Everything else can stay the same. I will refer back to the XML
section in chapter 4 to work through this effort. The Extract
function above looks much like the xpath_string function that I
talked about in the previous chapter.
I can
modify the above code to:
xpath_string(ci.xml_data, '/interface/what')::TEXT,
xpath_string(ci.xml_data, '/interface/who')::TEXT,
xpath_number(ci.xml_data, '/interface/hours')::INTEGER,
to_date(xpath_string(ci.xml_data, '/interface/when'),
'DD-MON-YYYY')::TIMESTAMP WITHOUT TIME ZONE,
'Y'::TEXT);
However, we also have to modify the insert statement to ensure the
data types are consistent.
Therefore, while it is not a problem per se, the needs of our
program force us to change the ADD_NEW_ITEM_UPDATE procedure to
explicitly cast the expected data types, thus:
INSERT INTO
tt_task_item_update (
task_id, resource_id, item_date,
hours_worked, active_flag)
VALUES ( tt_manage_projects.get_task_id(p_task_name),
tt_manage_resources.get_resource_id(p_resource_name),
trunc(p_item_date),
p_hours_worked,
p_active_flag);
Becomes this:
INSERT INTO tt_task_item_update (
task_id, resource_id, item_date,
hours_worked, active_flag)
VALUES ( tt_manage_projects.get_task_id(p_task_name)::numeric,
tt_manage_resources.get_resource_id(p_resource_name)::numeric,
trunc(p_item_date)::timestamp without time zone,
p_hours_worked::numeric,
p_active_flag::text);
After
recompiling, I test by adding a new XML record:
begin
tt_manage_user_records.add_xml_record('<?xml version="1.0"?>
<interface>
<who>Lewis Cunningham</who>
<what>Task1</what>
<when>02-JAN-2007</when>
<hours>8</hours>
</interface>');
end;
I then
run the PROCESS_XML_DATA
procedure and check the results in the TT_TASK_ITEM_UPDATE table.
Before
turning on the application for my users, I need to create them. As
they do not have any objects associated with their Oracle accounts,
it would be easy enough to migrate them using the Online Migration
tools. Alternatively, it is also easy enough to just recreate the
users with the TT_USER_ADMIN package. That choice I leave to you.
That
ends the migration. All of the migrated source code can be found in
the code depot. The EnterpriseDB version of the source code has the
same name as the Oracle version except that it replaces the word
oracle with the letters edb. The EnterpriseDB list also contains
additional code. The code for UTL_FILE is listed in Chapter 8. An
additional file, edb_functions.sql is also included to create the
padding functions. So, the code for EnterpriseDB includes:
*
chap8_utl_file.sql
*
chap9_functions.sq
*
chap9_edb_create_traker_schema
*
chap9_edb_create_traker_db_objects.sql
*
chap9_edb_traker_views.sql
*
chap9_edb_traker_packages.sql
*
chap9_edb_traker_grants_synonyms.sql
*
chap9_edb_traker_sample_data.sql
*
chap9_functions.sql
CREATE OR
REPLACE FUNCTION public.add_months (
p_in_date TIMESTAMP,
p_in_months NUMERIC
)
RETURN timestamp AS
v_date DATE;
BEGIN
EXECUTE IMMEDIATE
'SELECT cast( to_date(''' ||
to_char(p_in_date, 'DD-MON-YYY HH24:MI:SS') ||
''', ''DD-MON-YYY HH24:MI:SS'')' ||
' + interval ''' || to_char(p_in_months) ||
' month'' as date)'
INTO v_date;
RETURN v_date;
END;
CREATE OR
REPLACE FUNCTION public.lpad (
p_in_string VARCHAR,
p_in_length NUMERIC,
p_in_fill VARCHAR
)
RETURN varchar AS
BEGIN
RETURN lpad(p_in_string::TEXT,
p_in_length::INTEGER,
p_in_fill::TEXT );
END;
CREATE OR
REPLACE FUNCTION public.rpad (
p_in_string VARCHAR,
p_in_length NUMERIC,
p_in_fill VARCHAR
)
RETURN varchar AS
BEGIN
RETURN rpad(p_in_string::TEXT,
p_in_length::INTEGER,
p_in_fill::TEXT );
END;
chap9_edb_create_traker_schema
CREATE
SCHEMA traker
AUTHORIZATION enterprisedb;
CREATE USER traker SUPERUSER;
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
