I have been using some borrowed code that Chris Saxon provided on Oracle Live SQL, and last I used it, it worked flawlessly.
Oracle Live SQL - Script: Generating days, months or years between dates (0 Bytes)Statements 15 and 16, which create the package with related functions.
I was contacted by one of my end users who said that she received an error in the APEX Application that she was using. The error was:
ORA-06550: line 24, column 10: PL/SQL: ORA-06575: Package of function DATE_MGR is in an invalid state
I logged into APEX developer and navigated to the DATE_MGR package, and indeed, it looked like it was not compiled/active properly. I compared the code in my database instance to that of what is posted on LIVE SQL, and it looked identical. I tried saving/compiling and received the following error:
Compilation failed,line 7 (23:27:20)
PLS-00103: Encountered the symbol "SQL_MACRO" when expecting one of the following: . @ % ; is default authid as cluster order using external character deterministic parallel_enable pipelined aggregate result_cache accessible rewrite The symbol "." was substituted for "SQL_MACRO" to continue.
I've reduced the original code for the package specification down to only a single function as follows:
create or replace package date_mgr as
function generate_days (
start_date date, end_date date,
day_increment integer default 1
)
return varchar2 sql_macro;
end date_mgr;
I tried removing "sql_macro" from the return statement, specifying only the data type, and that allows me to save/compile with no error.
create or replace package date_mgr as
function generate_days (
start_date date, end_date date,
day_increment integer default 1
)
return varchar2;
end date_mgr;
What can I do differently so that I can name the variables, as demonstrated by Chris in his original script?