Re: Passing a 'where in' to stored procedure

From: Alvin Law <alaw_at_oracle.com>
Date: 1995/07/13
Message-ID: <ALAW.95Jul13005654_at_ap226sun.oracle.com>#1/1

In article <3turvc$a6r_at_palm.phcs.com> dhe_at_phcs.phcs.com (David Hermann) writes:
> In article <sarek.25.002A3A5B_at_cais.com>, sarek_at_cais.com (Scott T. Johnson) writes:
> |> Is the following legal? After executing, no data is changed in the
> |> database but SQL reports the PL/SQL procedure completed
> |> successfully.
> |>
> |> create or replace procedure upd_t1(name char, cc3 char, chg char)
> |> is
> |> begin
> |> update t1 set c1 = name, c2 = cc3
> |> where c2 in (chg);
> |> end;
> |> /
> |>
> |> SQL> execute upd_t1('picard','riker','''A1'',''A2'',''A3''')
>
> Your procedure call is legal, but it probably doesn't do what you want.
> In your example, the parameter CHG has the value
>
> 'A1','A2','A3'
>
> i.e. it's a character string that happens to include six singlequotes
> and two commas. Your WHERE ... IN clause gets a single-item list
> following the IN , and you'll only update your table if C2 equals
> CHG. SQL won't parse CHG into a multi-item list.
>
> I see two alternatives:

(both alternatives deleted to save bandwidth...)

If you are using PL/SQL V2.1 (RDBMS V7.1), there exists a third alternative: dynamic sql using dbms_sql package.

declare
  c integer;
  r integer;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,'update t1 set c1=:name, c2=:cc3 where c2 in ('||chg||')',

                 dbms_sql.native);

  dbms_sql.bind_variable(c,'name',name);   dbms_sql.bind_variable(c,'cc3',cc3);
  r := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
end;

Good luck.

--
 Alvin W. Law ........................................... Oracle Corporation
 Project Leader ............................. 300 Oracle Parkway, Box 659305
 Applications Design & Architecture ............... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ...... Voice: +1.415.506.8317 . Fax: +1.415.506.7294

******************************** W a r n i n g ******************************
Microsoft Network is prohibited from redistributing this work in any form, in
whole or in part, without the express written consent of the original author.

Received on Thu Jul 13 1995 - 00:00:00 CEST