1 2 Previous Next 18 Replies Latest reply: Dec 23, 2013 9:31 PM by BillyVerreynne Go to original post RSS
  • 15. Re: Writing the results of multiple queries to files in a PACKAGE
    Solomon Yakobson Guru
    Currently Being Moderated

    But in general: dynamic SQL will never be faster than SQL.

    Depending on what your actual queries are (and your database version) they're likely to be slower.

    Why? The only performance diff between using static cursor and cursor variable is parsing. With static cursor it is done at compile time while with ref cursor at run time. Other than that, I see no diff between FETCH static_cursor versus FETCH ref_cursor. You are probably talking about DBMS_SQL where all these DEFINE_COLUMN, BIND_VALIABLE,... are obviously an extra layer.

     

    SY.

  • 16. Re: Writing the results of multiple queries to files in a PACKAGE
    Brian Tkatch Journeyer
    Currently Being Moderated

    Version: 11.2

     

    Most of the queries are not small. All but one query run under 10 seconds (using SQL Developer). The one takes almost 20 seconds, except when i saw it once take five minutes. I'm assuming that had to do with caching, though, it (with the others) will have to be tested for overall performance.

  • 17. Re: Writing the results of multiple queries to files in a PACKAGE
    Brian Tkatch Journeyer
    Currently Being Moderated

    I marked Solomon's sample the answer, as it does show how to do it nicely and securely, plus he later mentioned where the assumed performance hit for dynamic SQL would be. I think this will be the solution that i implement, with a minor adjustment (assuming it is possible) of setting the array elements in different statements, for aesthetic reasons regarding the size of the queries.

     

    I have learned a few things from this thread and i really appreciate _all_ the responses. I am planning to go out of town tomorrow for a week or two, so i may not be checking back here for a bit, but, i will likely check back to see if anymore helpful information is posted.

  • 18. Re: Writing the results of multiple queries to files in a PACKAGE
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Happy holidays, and when you get back have a squiz at the sample code and approach in Re: How to execute a proc and spool files in a database job

     

    I am a big fan of using DBMS_SQL when it comes to server-side rendering data from a SQL cursor into whatever format needed (CSV, HTML, etc). One can fairly easily design and write a single code unit that deals with any SQL thrown its way, as DBMS_SQL provides everything from dynamic binding, to dynamic fetching.

1 2 Previous Next

Legend

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