This discussion is archived
5 Replies Latest reply: Jan 18, 2013 3:08 AM by 985411 RSS

dynamically create reference cursor and pass it to function

985411 Newbie
Currently Being Moderated
Hi ,

Function parallel_export (
p_source IN SYS_REFCURSOR,
p_filename IN VARCHAR2,
p_directory IN VARCHAR2,
i_file_seq IN NUMBER
) RETURN dump_ntt
PIPELINED
PARALLEL_ENABLE (PARTITION p_source BY ANY)
< code >
End Function

Procedure A
begin
....
for rows_x in(
SELECT * from TABLE(parallel_export(CURSOR(SELECT /*+ PARALLEL(big_tab,4) */ ABC||','||XYZ
FROM big_table partition(p_20130101) big_tab),
'big_tab_out_file',
'EXPORT_DIR',
12)) sv)
loop
DBMS_OUTPUT.PUT_LINE(rows_x.file_name ||' ' || rows_x.no_records);
end loop;
........
end

When I run Procedure A, it runs successfully and creates the 4 files with the parallelism hint. This works perfectly..
1) Is it possible to write another procedure to build the "Select" query and pass it into this function.
2) I could hard code this query in the procedure, but there are other tables where the partition name will change daily, therefore I will need something dynamic to change the
partition name in the query.

Any assistance will be appreciated.

Thank you.

Saila.
  • 1. Re: dynamically create reference cursor and pass it to function
    odie_63 Guru
    Currently Being Moderated
    2) I could hard code this query in the procedure, but there are other tables where the partition name will change daily, therefore I will need something dynamic to change the
    partition name in the query.
    If you add a filter predicate on the column the partitioning is based on then Oracle will automatically targets the correct partition, and you won't need dynamic SQL, only a bind variable.

    Assuming "p_20130101" contains rows with trunc(DT) = 01/01/2013, then :
    SELECT /*+ PARALLEL(big_tab,4) */  ABC||','||XYZ  
    FROM big_table big_tab
    WHERE big_tab.dt >= trunc(p_date)
    AND big_tab.dt < trunc(p_date) + 1
  • 2. Re: dynamically create reference cursor and pass it to function
    985411 Newbie
    Currently Being Moderated
    Thanks for the feedback.

    As you higlighted, "Assuming "p_20130101" contains rows with trunc(DT) = 01/01/2013" -- unfortunately, the data looks as follows "01/01/2013 19:52:52".

    Is there any other way to overcome this issue, cause I noticed that as soon as I add a filter predicate to the "date" field, the optimizer chooses a "PARTITION RANGE ITERATOR" or "PARTITION RANGE ALL" depending if I use ">" or "=" comparison operator.

    Thank you.
  • 3. Re: dynamically create reference cursor and pass it to function
    985411 Newbie
    Currently Being Moderated
    I created another procedure to address the dynamic sql requirement - which works, but it fails to run in parallel, unlike how the explicit "CURSOR(Select....)" does.

    Procedure WRAPPER
    v_sql varchar2(1000);
    l_cursor sys_refcursor;

    v_sql:='SELECT /*+ PARALLEL(big_tab,4) */ ABC||'',''||XYZ
    FROM big_table partition(p_20130101) big_tab';

    open l_cursor for v_sql;
    for rowsx in (Select * from table(parallel_dump(l_cursor, 'utl_file_mots60', 'CEMS_UTL_DUMP', 14)))
    loop
    ...
    end loop
    end
    /

    I'm open for any ideas and would even a consider taking a new approach, if needed, without sacrificing performance.
  • 4. Re: dynamically create reference cursor and pass it to function
    985581 Newbie
    Currently Being Moderated
    Hi,

    You forgot to close the single quote in your query :)
  • 5. Re: dynamically create reference cursor and pass it to function
    985411 Newbie
    Currently Being Moderated
    Thanks for the feedback. I'm not experiencing compilation errors, but trying to build a dynamic query and pass it to the ref cursor.

    I'm considering building a procedure at runtime with the "SQL" query, as a workaround. The procedure "create syntax" including the "SQL query excluding the partition name" will be stored in a table, and I will change the partition name as I build the procedure.

    e.g.
    SELECT * from TABLE(parallel_export(CURSOR(SELECT /*+ PARALLEL(big_tab,4) */ ABC||','||XYZ
    FROM big_table partition(p_20130101) big_tab),

    where (p_20130101) will change to (p_sysdate).

    This way the procedure will contain the "hard coded" partition name in the CURSOR.

    If anyone has other suggestion, please share your thoughts.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points