Oracle and PL/SQL Recipes: A Problem-Solution Approach

6-6. Writing ANSI-Compliant Conversions

Problem

You want to convert strings to dates using an ANSI-compliant methodology.

Solution

Use the CAST function, because it is ANSI-compliant. In this example, a procedure is written that will select each of the rows within the JOB_HISTORY table that fall within a specified date range. The dates will be converted into strings, and other information will be appended to the converted dates. This procedure will produce a simple report to display the JOB_HISTORY.

CREATE OR REPLACE PROCEDURE job_history_rpt(in_start_date IN DATE,                                             in_end_date IN DATE) AS   CURSOR job_history_cur IS   SELECT CAST(hist.start_date AS VARCHAR2(12)) || ' - ' ||          CAST(hist.end_date ...