Re: Updating a column with PL/SQL proc

From: Ranga Chakravarthi <ranga_at_cfl.rr.com>
Date: Wed, 12 Dec 2001 19:03:01 GMT
Message-ID: <FRNR7.79142$Ga5.12298198@typhoon.tampabay.rr.com>

You can use dynamic sql

stmt varchar2(2000);
begin
stmt := 'update test_plup set ' || clname || ' = ' || val1; execute immediate stmt;
end;

please use a package and overload the proc. if the columns can be of different datatypes (eg DATE)

HTH,
Ranga Chakravarthi

"TurkBear" <jgreco1_at_mn.rr.com> wrote in message news:1f6f1u4jigtf1dh6n5in3lcliv6mq0lbhp_at_4ax.com...
> I want to dynamically specify a column and its value to update with by
calling a Pl/Sql proc ..
> The code ( a very simple test) is
> CREATE OR REPLACE Procedure TEST_PLUPDATE (clname varchar2 ,val1 varchar2)
IS
> begin
> update test_plup
> set clname = val1;
> end;
>
> It will not compile since clname is not, at compile time, a column name in
test_plup..
>
> How can I design a proc that will accept a column name and a value and use
those to update the table?
>
> Thanks
> *( Oracle 8.1.6)
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> Check out our new Unlimited Server. No Download or Time Limits!
> -----== Over 80,000 Newsgroups - 19 Different Servers! ==-----
>
Received on Wed Dec 12 2001 - 13:03:01 CST