Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Question Unanswered: Dynamic Method 4 and Stored Procedures

    I'm developing in C (via PRO*C) utilizing Dynamic Method 4 (i.e. unknown number of input/output variables) for ORACLE. I would like to move some of the inline embedded SQL into stored procedures. I've been reading through all the documentation ( ) and it seems to indicate that I would need to use DBMS_SQL instead of PL/SQL to accomplish this. Is this true?

    If not, can someone direct me to documentation so I can see how it is done?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm developing in C (via PRO*C) utilizing Dynamic Method 4 (i.e. unknown number of input/output variables) for ORACLE.
    Too bad you did not provide a URL which documents Dynamic Method 4.
    Are you re-inventing the wheel?
    Oracle Dynamic SQL: Method 4


    >I would like to move some of the inline embedded SQL into stored procedures.
    Why?
    SQL is the gas for any RDBMS engine.
    Adding stored procedures (PL/SQL) adds overhead.

    >I've been reading through all the documentation ( ) and it seems to indicate that I would need to use DBMS_SQL instead of PL/SQL to accomplish this.
    What URL to said documentation?
    DBMS_SQL is PL/SQL!

    >Is this true?
    NO

    >If not, can someone direct me to documentation so I can see how it is done?
    asked & answered?

    The whole Oracle Doc set can be found at Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Excuse me if I wasn't totally clear.

    >I'm developing in C (via PRO*C) utilizing Dynamic Method 4 (i.e. unknown number of input/output variables) for ORACLE.
    >>You said:
    >>Too bad you did not provide a URL which documents Dynamic Method 4.
    >>Are you re-inventing the wheel?
    >>Oracle Dynamic SQL: Method 4

    The documentation I went through IS the ORACLE documentation Oracle Database Online Documentation 10g Release 2 (10.2) - PL/SQL User's Guide and Reference, Pro*C/C++ Programmer's Guide, ORACLE Database Application Developer's Guide.

    Not sure what you mean by re-inventing the wheel - I'm using the methodology that ORACLE provides to implement Dynamic Method 4 in my code.

    >I would like to move some of the inline embedded SQL into stored procedures.
    >>You said:
    >>Why?
    >>SQL is the gas for any RDBMS engine.
    >> Adding stored procedures (PL/SQL) adds overhead.
    Not sure where you are getting this assumption from - perhaps you did not read everything about my implementation thoroughly.

    Actually, utilizing stored procedures versus embedded SQL in code will result in performance improvements - 1. the stored procedure is compiled once (dependent of course on implementation) and 2. the number of network roundtrips to the SQL server is reduced as the stored procedure performs multiple steps on the server. As an example, take a simple dynamic SQL statement (not Method 4) to insert a row. In the inline SQL code, I create my statement, then do an EXEC SQL PREPARE desc_prep from :insert_stmt (first trip) then do the EXEC SQL EXECUTE desc_prep (second trip). As a part of that process, it has to re-compile ech time. If I replace it with a stored procedure (passing a parameter containing the statement to be executed)that does EXECUTE IMMEDIATE :insert_stmt, at the time I created the stored procedure in the SQL server, it was compiled (once only at time of creation - not every time). The PREPARE and EXECUTE implied by the EXECUTE IMMEDIATE are done in one trip.

    I wrote a prototype to confirm this information - it connects to the database, runs an insert of 100000 records as stored procedure, and runs an insert of 100000 records as embedded SQL (not PL/SQL) spitting out elapsed time for each method. It took 2.89 seconds for the stored procedure to insert the 100K records versus 6.87 for the embedded SQL. I'd say that was a pretty significant improvement????

    I saw similiar disparity when using anonymous block of PL/SQL in the code versus stored procedure. By the way, these performance savings are discussed in the ORACLE documentation (see URL above) that I read - they are discussed in Chapter 7 (Coding PL/SQL Procedures and Packages) of the Application Developer's Guide and Chapter 8 of the PL/SQL User's Guide and Reference - two of many places. If you need for me to, I can find every reference but I think those are good starting places.

    >I've been reading through all the documentation ( ) and it seems to indicate >that I would need to use DBMS_SQL instead of PL/SQL to accomplish this. Is this true?
    >>You said:
    >>What URL to said documentation?
    >>DBMS_SQL is PL/SQL!
    >> NO
    First, my apologies - at the time I posted this message I was still going through the documentation and didn't realize that DBMS_SQL was a PL/SQL package. I have not ever used it before - so sorry for being ignorant.

    So given that it is a part of PL/SQL, I should have correctly phrased my inquiry as: "....it seems to indicate that I would need to use DBMS_SQL instead of native dynamic SQL to accomplish this. Is this true?"

    Here is the URL: Performing SQL Operations with Native Dynamic SQL - Look at the section: Why Use Dynamic SQL with PL/SQL? To save you the trip to the URL, here is the note that led me to that conclusion:
    ================================================== ======
    Note:

    Native dynamic SQL using the EXECUTE IMMEDIATE and OPEN-FOR statements is faster and requires less coding than the DBMS_SQL package. However, the DBMS_SQL package should be used in these situations:
    •There is an unknown number of input or output variables, such as the number of column values returned by a query, that are used in a dynamic SQL statement (Method 4 for dynamic SQL).


    •The dynamic code is too large to fit inside a 32K bytes VARCHAR2 variable.
    ================================================== =======

    So you are saying that is NOT true? Or that it is true but I just phrased my original question incorrectly?

    If it is not true, would you be able to provide a specific chapter documentation or discussion forum website or how-to article URL that shows an example of using Dynamic Method 4 using native dynamic SQL (non-DBMS_SQL package) PL/SQL?

    If it is true, would you be able to provide a specific chapter documentation or discussion forum website or how-to article URL that shows an example of using Dynamic Method 4 with the PL/SQL DBMS_SQL package?

    I hope this clarifies my question and satisfies your need for specific pointers to the documentation. I do try to do my research before posing a question.

    Thanks in advance for any additional insight you can supply.

    Any other responses are welcome.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Native dynamic SQL using the EXECUTE IMMEDIATE and OPEN-FOR statements is faster and requires less coding than the DBMS_SQL package. However, the DBMS_SQL package should be used in these situations:
    >•There is an unknown number of input or output variables, such as the number of column values returned by a query, that are used in a dynamic SQL statement (Method 4 for dynamic SQL).
    >
    what (too) many folks do is to build the SQL statement "dynamically" into a single VARCHAR2 variable (i.e. V_SQL).
    Next they invoke EXECUTE IMMEDIATE V_SQL
    This approach can handle the case of unknown number of inputs & outputs.

    >•The dynamic code is too large to fit inside a 32K bytes VARCHAR2 variable.
    I am of the opinion that if any SQL statement exceeds 32K, the design is flawed

    Enjoy!
    Last edited by anacedent; 09-23-09 at 17:08.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    >>You said:
    >>what (too) many folks do is to build the SQL statement "dynamically" into >>a single VARCHAR2 variable (i.e. V_SQL).
    >>Next they invoke EXECUTE IMMEDIATE V_SQL
    >>This approach can handle the case of unknown number of inputs & outputs.

    Oh really? Then please provide me with an example of implementing method 4 in this manner. Just as a reminder, method 4 means you have no idea until run time what columns, data types, tables, or statement format will be.

  6. #6
    Join Date
    Sep 2009
    Posts
    4

    Lightbulb

    No snappy reply?

    Well, never mind - I finally found an example and an answer. The answer is: Yes it is true that you can not use native dynamic SQL in PL/SQL to perform dynamic method 4 and yes you must use DBMS_SQL package to implement this methodology.

    Here is a quote from a book, ORACLE Built-In Packages by Steven Feuerstein, Charles Dye & John Beresniewicz, just for your reading pleasure, that talks about Dynamic Method 4 and the challenge of implementing in PL/SQL:

    "2.5.4 Displaying Table Contents with Method 4 Dynamic SQL
    This section examines the kind of code you need to write to perform dynamic SQL Method 4. Method 4, introduced early in this chapter, supports queries that have a variable (defined only at runtime) number of items in the SELECT list and/or a variable number of host variables. Here is an example of Method 4 dynamic SQL:

    'SELECT ' || variable_select_list ||
    ' FROM ' || table_name ||
    ' WHERE sal > :minsal
    ' AND ' || second_clause ||
    order_by_clause

    Notice that with this SQL statement, I do not know how many columns or expressions are returned by the query. The names of individual columns are "hidden" in the variable select list. I also do not know the full contents of the WHERE clause; the minsal bind variable is obvious, but what other bind variable references might I find in the second_clause string? As a result of this uncertainty, Method 4 dynamic SQL is the most complicated kind of dynamic query to handle with DBMS_SQL.

    What's so hard about that? Well, if I am going to use the DBMS_SQL package to execute and fetch from such a query, I need to write and compile a PL/SQL program. Specifically, to parse the SQL statement, I need to define the columns in the cursor with calls to DEFINE_COLUMN -- yet I do not know the list of columns at the time I am writing my code. To execute the query, I must associate values to all of my bind variables (identifiers with a ":" in front of them) by calling BIND_VARIABLE -- yet I do not know the names of those bind variables at the time I write my code. Finally, to retrieve data from the result set of the query I also need to call COLUMN_VALUE for each column. But, again, I do not know the names or datatypes of those columns up front.

    Sounds challenging, doesn't it? In fact, working with these incredibly dynamic SQL strings requires some interesting string parsing and some even more creative thinking.

    When would you run into Method 4? It arises when you build a frontend to support ad-hoc query generation by users, or when you want to build a generic report program, which constructs the report format and contents dynamically at runtime. I also encountered it recently when I decided to build a PL/SQL procedure to display the contents of a table -- any table, as specified by the user at runtime."


    So go to the URL above if you need information on implementing dynamic methods - it is very informative, helpful, and worthwhile. That is also where you will find the example in section 2.5.4.

    It took me some time (2 days) to find it which is why I asked the question here initially figuring someone would already have done this and save me some time/frustration - no sense in "reinventing the wheel", eh?
    Last edited by ZippyD; 09-23-09 at 19:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •