Re: Need help with bind variables and SP

"Kev" <java2e_at_yahoo.com> wrote in message news:5ftaitcgc3teu2qjlaealk17grhv2h6ihp_at_4ax.com...
> Hello all,
>
> I am trying to get this sample SP to work with bind varialbes as the
> CPU get pegged at 100%. This is not the query being used but if I can
> get some help on how to convert this to use bind variables I would
> greatly appreciate it. Now I can use the bind variables in a PLSQL
> block but or the life of me cant get it to work in a SP.
>
>
> CREATE OR REPLACE PACKAGE PKG_CAT
> AS
> CURSOR cur_CAT
> IS
> SELECT TABLE_NAME FROM CAT;
>
> TYPE t_CUR_CAT IS REF CURSOR RETURN cur_CAT%ROWTYPE;
>
> PROCEDURE sp_CAT
> (
> p_TABLE_TYPE VARCHAR2,
> IO_CURSOR IN OUT t_cur_CAT
> );
>
> END PKG_CAT;
>
> CREATE OR REPLACE PACKAGE BODY PKG_CAT
> AS
> PROCEDURE sp_CAT
> (
> p_TABLE_TYPE VARCHAR2,
> IO_CURSOR IN OUT t_CUR_CAT
> )
> AS
> v_CURSOR t_CUR_CAT;
> BEGIN
> OPEN IO_CURSOR FOR
> SELECT TABLE_NAME FROM CAT WHERE TABLE_TYPE = p_TABLE_TYPE;
> END sp_CAT;
>
> END PKG_CAT;
>
>
> I need the variable p_Table_Type to be the bind variable. If you can
> point me where I can find some information on this or some samples I
> would greatly appreacite it.
>
> Again Thanks,
>
> Kev

This is already a bind variable. All pl/sql variables are bind variables, always. Only if there would have been hardcoded literals in your statement, they are not automatically transformed to a bind variable.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Jun 11 2001 - 23:16:30 CDT