5 Replies Latest reply: Jan 18, 2013 5:08 AM by 985411 RSS

    dynamically create reference cursor and pass it to function

    985411
      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
          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
            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
              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
                user8581627
                Hi,

                You forgot to close the single quote in your query :)
                • 5. Re: dynamically create reference cursor and pass it to function
                  985411
                  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.