Re: like keyword (full table scan)?

tone wrote:

> hi everyone..
>
> im an intern to a company where oracle is a new addition, so im
> wondering if they way they do sql is the best way. here is my
> question. i've included the sample code, but ill put it in english.
>
> ::question follows ::
> basically, they currently use dynamic sql (concatenate an sql string
> and execute immediate).. from what i read, that is not so good,
> because each time the procedure is called, oracle has to reparse (?)..
> so i thought about using the like keyword. if the search field is
> specified, then replace the '%' (full table scan) with the provided
> keyword.
>
> The big question is, is this a better way of doing things? or am i
> trying to be a smartass.... any opinions?
>
> thanks
> asok (dilbert is my buddy!)
>
> --------sample code -------------------->
> keep in mind this is sort of pseudo-code.. not exact.. just idea
> =====old way============================>
>
> procedure user_search (
> ret_code out number,
> cust_idin in user.cust_id%type,
> user_idin in user.user_id%type,
> ) as
>
> v_cust_id user.cust_id%type;
> v_user_id user.user_id%type;
>
> v_sql varchar2(200);
> v_sql1 varchar2(100) := 'SELECT * FROM schema.table ';
>
> v_sql2 varchar2(50) := ' ORDER BY user_id, cust_id';
>
> v_sql_dyn varchar2(50);
>
> begin
> v_cust_id := cust_idin;
> v_user_id := user_idin;
>
> if (v_user_id is NULL) then
> v_sql_dyn := ' cust_id = :1';
> else if (v_cust_id is NULL) then
> v_sql_dyn := ' user_id = :2';
> else
> v_sql_dyn := ' cust_id = :1 AND user_id = :2';
> end if;
>
> v_sql := v_sql1 || v_sql_dyn || v_sql2;
>
> EXECUTE IMMEDIATE v_sql using v_cust_id, v_user_id;
>
> if SQL%ROWCOUNT = 1 then
> ret_code := 0;
> else
> ret_code := 8;
> end if;
>
> return;
>
> end user_search;
>
> =====my way============================>
>
> procedure Search (userin IN INTEGER, custin IN INTEGER, errorcode OUT
> INTEGER) as
> v_user_id VARCHAR2(13);
> v_cust_id VARCHAR2(13);
> cursor CUR is
> SELECT *
> FROM ippprg.pni
> WHERE acno LIKE v_acno
> AND chkno LIKE v_chkno
> ;
>
> begin
>
> IF userin is NULL THEN
> v_user_id := '%';
> ELSE
> v_user_id := userin;
> END IF;
>
> IF custin is NULL THEN
> v_cust_id := '%';
> ELSE
> v_cust_id := custin;
> END IF;
>
> errorcode := 0;
>
> for each_row in CUR loop
> dbms_output.put_line(each_row.user_id);
>
> end loop;
>
> end Search;
>
> ---------------end sample code ------------->

The problem you are attempting to solve, the parsing, can not be dealt with as you propose. But rather should be dealt with using bind variables. Change your statement to the following form:

EXECUTE IMMEDIATE sqlstring
USING x;

Where x is a variable

Daniel Morgan Received on Tue Aug 13 2002 - 15:57:16 CDT