Re: Oracle Newbie: XML Handling in Oracle 9i

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Fri, 31 Jan 2003 08:38:28 -0800
Message-ID: <3E3AA684.160486D7@exesolutions.com>

Larry wrote:

> Basically, the question I'm faced with answering is:
>
> Is it poor practice, or generally thought to be inefficient, to pass
> XML (clobs) into SPs versus simple parameters into SPs?
>
> Also, if it is acceptable to use the approach of passing XML docs, is
> it best practice to use an XML Schema as well?
>
> ----Start High Level----
>
> Generally speaking, in n-tier design, the middle-tier components, as
> well as stored procedures, change anytime a new field which must be
> populated, is added to a DB table.
>
> However, if I use XML docs or strings representing XML docs, passed
> into SPs as clobs, I can easily add new parameters to my XML document
> and create a single SP, for example, called "UpdateSP" to handle
> updates.
>
> ----End High Level----
>
> I believe Oracle uses Java packages to assist in the execution of XML
> docs, and I'm wondering if operating in the above scenario is
> recommended, discouraged, or simply a matter of preference.
>
> A newbie like myself might easily think that Oracle receiving XML in a
> clob would mean, automatically, that it would generate Dynamic SQL
> under the covers, in order to execute the interpreted statement, or
> statements.
>
> Additionally, a someone like myself easily consider this approach to
> be inefficient, compared to simply passing in a set of parameters,
> even in large numbers.
>
> Please forgive my inability to correctly pose this very important
> question in a way which makes sense. I appreciate, very much, your
> level of granularity and your patience.
>
> Regards,
>
> Larry

I agree with Mark's statements except that, from my experience, it is always better to pass in parameters. Here's why.

XML developers don't write good SQL. And SQL in XML is hard to tune during development ... nearly impossible to tune after deployment.

Another consideration I ran into doing just this was that once one had to pass CLOBs rather than VARCHARs it became necessary to go from native dynamic SQL's execute immediate to DBMS_SQL and use its ability to parse CLOBs into a table of 256K strings and then execute the table. Doable but not exactly a performance enhancer.

Here's a genericized version of the code if you are interested.

PROCEDURE execute_plsql_block_32K(

                                p_return_code OUT INTEGER,
                                p_plsql_block IN CLOB,
                                p_result_set OUT rc_type.t_ref_cursor,
                                p_method_name IN VARCHAR2)
                              IS

                              -- Variable definitions
                              ds_cur      INTEGER := DBMS_SQL.OPEN_CURSOR;

                              sql_table   DBMS_SQL.VARCHAR2S;
                              v_accum   PLS_INTEGER := 0;
                              v_beg       PLS_INTEGER := 1;
                              v_end       PLS_INTEGER := 256;
                              v_loblen    PLS_INTEGER;
                              v_RetVal   PLS_INTEGER;
                              c_BUF_LEN   CONSTANT BINARY_INTEGER := 256;

                              -- local function to the execute_plsql_block
procedure
                              FUNCTION next_row (
                                clob_in IN CLOB,
                                len_in IN INTEGER,
                                off_in IN INTEGER)
                                RETURN VARCHAR2
                              IS

                              BEGIN
                                RETURN DBMS_LOB.SUBSTR(clob_in, len_in,

off_in);

                              END next_row;
                              -- end of local function nested inside

procedure

• beginning of procedure BEGIN
  • Execute the requested PL/SQL block as dynamic SQL
  • get the length of the CLOB v_loblen := DBMS_LOB.GETLENGTH(p_plsql_block);
  • Build the pl/sql table by breaking the code into c_BUF_LEN chunks LOOP
    • Set the length to the remaining size if there are < c_BUF_LEN
    • characters remaining. IF v_accum + c_BUF_LEN > v_loblen THEN

                                       v_end := v_loblen - v_accum;
                                    END IF;

                                    sql_table(NVL(sql_table.LAST, 0) + 1)

:= next_row(p_plsql_block, v_end, v_beg);

                                    v_beg := v_beg + c_BUF_LEN;
                                    v_accum := v_accum + v_end;

                                    IF v_accum >= v_loblen THEN
                                       EXIT;
                                    END IF;
                                 END LOOP;

                                 -- Parse the pl/sql and execute it
                                 DBMS_SQL.PARSE(ds_cur, sql_table,
sql_table.FIRST, sql_table.LAST,
                                                                    FALSE,
DBMS_SQL.NATIVE);
                                 v_RetVal := DBMS_SQL.EXECUTE(ds_cur);
                                 DBMS_SQL.CLOSE_CURSOR(ds_cur);

                                 -- Handling the normal termination of
dynamic SQL
                                 p_return_code := app_constants.c_SUCCESS;

                                 OPEN p_result_set FOR
                                 SELECT * FROM return_status;

                                 DELETE FROM return_status;
                                 COMMIT;

                              EXCEPTION
                                 WHEN OTHERS THEN
                                    -- exception handling code

                              END execute_plsql_block_32K;
                              /

Daniel Morgan Received on Fri Jan 31 2003 - 10:38:28 CST