Re: Need help with bind variables and SP

Here is the thing though and that is whenever I run any query as below the CPU maxes out 100%.

I also noticed that many people use the where x = :Var.

Now when I used the following in an annonymous block the CPU does not max out but when I run the same query in an SP without :Var the CPU maxes out. It seems it is re-pasring every single time the query is run.

Any suggestions?

On Tue, 12 Jun 2001 06:16:30 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>"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 Tue Jun 12 2001 - 00:47:16 CDT