Re: Library Cache

Comments embedded.
On Dec 30, 3:47 pm, mrdjmag..._at_aol.com wrote:
>
> Mark,
>
> One issue is that our entire application is written this way.   Most
> of the queries are stored in a table.  An acronym is passed into the
> package and the query is retrieved from the table and a cursor is
> opened for PHP to use.  Take a look at the procedure:
>
> PROCEDURE get_data (
>    p_line        IN OUT  REF_CURSOR,
>    p_var      IN      VARCHAR2 DEFAULT NULL,
>    p_name IN      VARCHAR2) IS
>
> BEGIN
>    SELECT query, order_by
>    INTO v_query, v_order
>    FROM report_sql
>    WHERE name = p_name;
>
>    IF p_var IS NULL THEN
>       NULL;
>    ELSE
>       v_query := v_query || p_var || v_order;
>    END IF;
>
> OPEN p_line FOR v_query;
> END;
> /
>
> So, if I had a query like this:  SELECT portfolio_id, portfolio_name
> FROM portfolio WHERE customer_id =
> The code above would create this:  SELECT portfolio_id, portfolio_name
> FROM portfolio WHERE customer_id = 12345
>

This is where the problems arise, as you're not generating a query which uses bind variables.

> If I changed the query to this:  SELECT portfolio_id, portfolio_name
> FROM portfolio WHERE customer_id = :1
> Would I be able to use bind variables???

The :1 IS a bind variable, however you'd also need to change how you use execute immediate with such a query as the calling program/ procedure would need to use

execute immediate p_line using p_var;

in place of the existing

execute immediate p_line;

>  Would it properly perform
> the substitution?

Not until you change how that query string is executed, illustrated above.

>
> Much Thanks!

David Fitzjarrell Received on Wed Dec 31 2008 - 08:50:26 CST