dynamic SQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: dynamic SQL

  1. #1
    NJMrBig Guest

    dynamic SQL


    Hi all,

    I am using Oracle 8i and writing a stored procedure to select data from multiple
    tables given an array of IDs. I would like to use PL/SQL's dynamic SQL capability
    in order to build the select statement in a stored procedure and execute
    it putting the results in a REF CURSOR.

    Anyone have any idea on how to do this? My current code is below (I have
    changed the names to protect the innocent):

    ....
    TYPE somecur IS REF CURSOR;
    ....
    PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
    p_errorcode OUT NUMBER,
    p_cursor OUT somecur)
    IS
    ....
    BEGIN
    vSQL := 'SELECT * FROM contracts' ||
    'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
    = ' || vYear || 'ORDER BY ID';

    /* What I want to do something like this */
    OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL

    ....
    END;

  2. #2
    Boris Milrud Guest

    Re: dynamic SQL


    Hi NJMrBig,

    Here is your code with some modifications:

    TYPE somecur IS REF CURSOR;
    ....
    PROCEDURE retrieveDisplayData(
    p_ContractData IN VARCHAR2,
    p_errorcode OUT NUMBER,
    p_cursor OUT somecur)
    IS
    ....
    BEGIN

    vSQL := 'SELECT * FROM contracts ' ||
    'WHERE client = :bClient AND ' ||
    'number = :bNumber AND ' ||
    'year = :bYear ' ||
    'ORDER BY ID';

    OPEN p_cursor FOR vSQL USING vClient, vNumber, vYear;
    ....

    END;

    Hope it helps.
    Boris Milrud.


    "NJMrBig" <NJMrBig@aol.com> wrote:
    >
    >Hi all,
    >
    >I am using Oracle 8i and writing a stored procedure to select data from

    multiple
    >tables given an array of IDs. I would like to use PL/SQL's dynamic SQL capability
    >in order to build the select statement in a stored procedure and execute
    >it putting the results in a REF CURSOR.
    >
    >Anyone have any idea on how to do this? My current code is below (I have
    >changed the names to protect the innocent):
    >
    >....
    >TYPE somecur IS REF CURSOR;
    >....
    >PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
    >p_errorcode OUT NUMBER,
    >p_cursor OUT somecur)
    >IS
    >....
    >BEGIN
    >vSQL := 'SELECT * FROM contracts' ||
    >'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
    >= ' || vYear || 'ORDER BY ID';
    >
    >/* What I want to do something like this */
    >OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL
    >
    >....
    >END;



  3. #3
    NJMrBig Guest

    Re: dynamic SQL


    Hi Boris,
    Thank you for the code suggestions. After posting my original question I
    went out and bought PL/SQL for Dummies (just kidding) and figured it out
    on my own.
    One question you may be able to answer is that my WHERE clause is being set
    by the inputs fed into the stored procedure. Therefore, it looks more like
    this:

    > vSQL := 'SELECT * FROM contracts ' ||
    > 'WHERE (client = 0001 AND ' ||
    > 'number = 10 AND ' ||
    > 'year = 1994) ' ||

    OR (client = 0002 AND ' ||
    > 'number = 11 AND ' ||
    > 'year = 1995) ' ||
    > 'ORDER BY ID';


    Could potentially OR up to 100 different ANDs if you know what I mean. When
    I parse my inputs, create my SQL and execute it in-line dynamically using
    the technique you describe below, it is unacceptably slow. My expected output
    was about 2600 rows of data.

    Is there anyway to optimize this? or am I using the wrong technique to solve
    this particular problem?

    Thanks,
    Big

    "Boris Milrud" <milrud@hotmail.com> wrote:
    >
    >Hi NJMrBig,
    >
    >Here is your code with some modifications:
    >
    >TYPE somecur IS REF CURSOR;
    >....
    >PROCEDURE retrieveDisplayData(
    > p_ContractData IN VARCHAR2,
    > p_errorcode OUT NUMBER,
    > p_cursor OUT somecur)
    >IS
    >....
    >BEGIN
    >
    > vSQL := 'SELECT * FROM contracts ' ||
    > 'WHERE client = :bClient AND ' ||
    > 'number = :bNumber AND ' ||
    > 'year = :bYear ' ||
    > 'ORDER BY ID';
    >
    > OPEN p_cursor FOR vSQL USING vClient, vNumber, vYear;
    > ....
    >
    >END;
    >
    >Hope it helps.
    >Boris Milrud.
    >
    >
    >"NJMrBig" <NJMrBig@aol.com> wrote:
    >>
    >>Hi all,
    >>
    >>I am using Oracle 8i and writing a stored procedure to select data from

    >multiple
    >>tables given an array of IDs. I would like to use PL/SQL's dynamic SQL

    capability
    >>in order to build the select statement in a stored procedure and execute
    >>it putting the results in a REF CURSOR.
    >>
    >>Anyone have any idea on how to do this? My current code is below (I have
    >>changed the names to protect the innocent):
    >>
    >>....
    >>TYPE somecur IS REF CURSOR;
    >>....
    >>PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
    >>p_errorcode OUT NUMBER,
    >>p_cursor OUT somecur)
    >>IS
    >>....
    >>BEGIN
    >>vSQL := 'SELECT * FROM contracts' ||
    >>'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
    >>= ' || vYear || 'ORDER BY ID';
    >>
    >>/* What I want to do something like this */
    >>OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL
    >>
    >>....
    >>END;

    >



  4. #4
    Boris Milrud Guest

    Re: dynamic SQL


    NJMrBig,

    1. Check if you have a composite index on (client, number, year).
    2. Analyze the execution path for that SQL. It would help if CBO uses INLIST
    iterator instead of CONCATENATION one.
    3. Another way to form this query would be with IN clause:
    SELECT *
    FROM contracts
    WHERE (client, number, year)
    IN ((0001,10,1994), (0002,11,1995))

    I think it looks better. But the real question is if the execution path would
    get better.

    See if it helps, though I doubt: you mentioned that you have up to 100 sets
    of (client, number, year). Another factor is the selectivity of your data.

    I think you may need to rethink the whole strategy for this task. It's hard
    for me to suggest anything beyond that since I don't know anything about
    your data set.

    Boris Milrud.

    "NJMrBig" <NJMrBig@aol.com> wrote:
    >
    >Hi Boris,
    >Thank you for the code suggestions. After posting my original question I
    >went out and bought PL/SQL for Dummies (just kidding) and figured it out
    >on my own.
    >One question you may be able to answer is that my WHERE clause is being

    set
    >by the inputs fed into the stored procedure. Therefore, it looks more like
    >this:
    >
    >> vSQL := 'SELECT * FROM contracts ' ||
    >> 'WHERE (client = 0001 AND ' ||
    >> 'number = 10 AND ' ||
    >> 'year = 1994) ' ||

    >OR (client = 0002 AND ' ||
    >> 'number = 11 AND ' ||
    >> 'year = 1995) ' ||
    >> 'ORDER BY ID';

    >
    >Could potentially OR up to 100 different ANDs if you know what I mean. When
    >I parse my inputs, create my SQL and execute it in-line dynamically using
    >the technique you describe below, it is unacceptably slow. My expected output
    >was about 2600 rows of data.
    >
    >Is there anyway to optimize this? or am I using the wrong technique to solve
    >this particular problem?
    >
    >Thanks,
    >Big
    >
    >"Boris Milrud" <milrud@hotmail.com> wrote:
    >>
    >>Hi NJMrBig,
    >>
    >>Here is your code with some modifications:
    >>
    >>TYPE somecur IS REF CURSOR;
    >>....
    >>PROCEDURE retrieveDisplayData(
    >> p_ContractData IN VARCHAR2,
    >> p_errorcode OUT NUMBER,
    >> p_cursor OUT somecur)
    >>IS
    >>....
    >>BEGIN
    >>
    >> vSQL := 'SELECT * FROM contracts ' ||
    >> 'WHERE client = :bClient AND ' ||
    >> 'number = :bNumber AND ' ||
    >> 'year = :bYear ' ||
    >> 'ORDER BY ID';
    >>
    >> OPEN p_cursor FOR vSQL USING vClient, vNumber, vYear;
    >> ....
    >>
    >>END;
    >>
    >>Hope it helps.
    >>Boris Milrud.
    >>
    >>
    >>"NJMrBig" <NJMrBig@aol.com> wrote:
    >>>
    >>>Hi all,
    >>>
    >>>I am using Oracle 8i and writing a stored procedure to select data from

    >>multiple
    >>>tables given an array of IDs. I would like to use PL/SQL's dynamic SQL

    >capability
    >>>in order to build the select statement in a stored procedure and execute
    >>>it putting the results in a REF CURSOR.
    >>>
    >>>Anyone have any idea on how to do this? My current code is below (I have
    >>>changed the names to protect the innocent):
    >>>
    >>>....
    >>>TYPE somecur IS REF CURSOR;
    >>>....
    >>>PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
    >>>p_errorcode OUT NUMBER,
    >>>p_cursor OUT somecur)
    >>>IS
    >>>....
    >>>BEGIN
    >>>vSQL := 'SELECT * FROM contracts' ||
    >>>'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
    >>>= ' || vYear || 'ORDER BY ID';
    >>>
    >>>/* What I want to do something like this */
    >>>OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL
    >>>
    >>>....
    >>>END;

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center