Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Oracle returning a cursor from a function or procedure
Answers
-
@bede,
There's always the case of "horses for courses". You always pay something somewhere.
The benefit of the pipelined function is that you only write the complicated bit once, even though it costs you in row-by-row processing. You might note, though, that the OP's query had an output of 83 rows (when I ran it), which is a lot more row-by-row than the 7 rows that had to be processed in the function.
An alternative (since the OP may be on a sufficiently recent releast of 19c (19.10 might be okay, I've just tested 19.11) is the SQL Macro:
create or replace function generate_dates( p_from in date, p_to in date ) return varchar2 sql_macro -- NOTE THIS is v_sql varchar2(4000); begin v_sql := ' with calendar (start_date, end_date ) as ( select date ''' || to_char(p_from,'yyyy-mm-dd') || ''' start_date, date ''' || to_char(p_to ,'yyyy-mm-dd') || ''' end_date from dual ' || 'union all select start_date + 1, end_date from calendar where start_date + 1 <= end_date ) select start_date as date_val from calendar '; -- dbms_output.put_line(v_sql); return v_sql; end generate_dates; /
This has the effect of allowing the complex code to be written once (and then made totally incomprehensible because of the need to make it a generated string!) and used many times. The call to the function effectively generates the correct inline text before the optimizer does a pure SQL optimisation.
(As it stands creating this function and then re-running the final SQL with "date_val" will get the correct result with no row-by-row procssing).
Regards
Jonathan Lewis
-
Thanks for the feedback.
If you're running a recent release of 19c then there is another option available - the SQL Macro - which might have been Oracle Corps. response to exactly this type of problem.
Regards
Jonathan Lewis
-
@Jonathan Lewis Regarding your SQL Macro example:
I don't think it's needed to format the incoming arguments and concatenate it in the SQL statement, I think you can reference the incoming arguments directly like:
create or replace function generate_dates( p_from in date, p_to in date ) return varchar2 sql_macro is v_sql varchar2(4000); begin v_sql := ' with calendar (start_date, end_date ) as ( select generate_dates.p_from as start_date --<-- directly referencing the argument ,generate_dates.p_to as end_date --<-- from dual union all select start_date + 1, end_date from calendar where start_date + 1 <= end_date ) select start_date as date_val from calendar '; return v_sql; end generate_dates; /
Only problem is that I don't have access to any other version than a Oracle 19.0... and in that version there is a bug where you can't use the WITH clause (Bug 32212976: USING SCALAR ARGUMENTS IN WITH CLAUSE IN SQL TABLE MACRO RAISES ORA-06553 PLS-306 )
So I had to rewrite it to not use recursive subquery factoring to "prove my point"
create or replace function generate_dates( p_from in date, p_to in date ) return varchar2 sql_macro -- NOTE THIS is v_sql varchar2(4000); begin v_sql := 'select trunc (generate_dates.p_from) -1 + level as startdate from dual connect by level <= (generate_dates.p_to - generate_dates.p_from)'; return v_sql; end generate_dates; /
-
Alex,
Thanks for that - it didn't occur to me that the problem was the WITH subquery (my testbed is 19.11.0.0), so I switched to this messy character substitution without thinking about why I was getting that ORA-06553 error. (The substitution method I used on the corresponding blog note - which is in its final draft ***, but which is now going to have an update - is a little tidier than this one.)
Regards
Jonathan Lewis
*** Update: published at https://jonathanlewis.wordpress.com/2021/07/22/sql-macro/
-
@Johnathan Lewis I modified my code to use your new function generate_dates_pipelined and it works great!!! Kudos for the excellent idea and detailed write up.
A quick question if you don't mind. In order to make the calling code truly generic would it make sense to pull out the hard coded dates and do something like this first in order not to possibly mess up the SQL by editing it every time it needs to run.
This way the caller can always pass pipelined_start_date, pipelined_end_date. Your thoughts please. Using bind variables would require input from the user and it could not be run non interactively.
variable pipelined_start_date varchar2(30)
variable pipelined_end_date varchar2(30)
exec :pipelined_start_date := '20210722';
exec :pipelined_end_date := '20210731';
Or set to SYSDATE, SYSDATE + N
-
That seems perfectly reasonable to me; the pipeline method certainly works with incoming bind variables, and it would probably have some effect of reducing total parse costs.
Regards
Jonathan Lewis