|
Replies:
21
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Oct 4, 2007 1:44 PM
Last Post By: Billy Verreynne
|
|
|
Posts:
2
Registered:
05/21/06
|
|
|
|
PL/SQL script that can perform update any table in the schema
Posted:
Aug 24, 2006 6:40 PM
|
|
|
|
Hello,
I want to run a script which takes tablename, column name ,column value as parameters and executes update on that table with value for the given column.
thanks
|
|
|
Posts:
1,246
Registered:
05/01/00
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 26, 2006 11:49 AM
in response to: user511112
|
|
|
a simple script using dynamic sql;
set serveroutput on
declare
atable varchar2(64) := 't';
acolumn varchar2(64) := 'col1';
avalue varchar2(64) := 'VAL' ;
dyn_sql varchar2(64);
begin
dyn_sql := 'update '||atable||' set '||acolumn||' = '''||avalue|| '''' ;
dbms_output.put_line(dyn_sql);
execute immediate dyn_sql;
end;
/
commit ;
SQL> select col1 from t ;
COL1
VAL
VAL
VAL
VAL
VAL
..
|
|
|
Posts:
19,247
Registered:
08/22/02
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 26, 2006 12:03 PM
in response to: user511112
|
|
|
Hello,
I want to run a script which takes tablename,
column name ,column value as parameters and executes
update on that table with value for the given
column.
thanks
Is it really for a production usage ? Dynamic sql is not very optimize for perf and debug, and are you sure that you want update all table with same value each time you run the proc ?
Furthermore, you can have some problem for some datatype (date...).
If it's for training, so... try what TongucY suggested.
Nicolas. |
|
|
Posts:
2
Registered:
05/21/06
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 28, 2006 7:14 PM
in response to: N Gasparotto
|
|
|
|
Hello,
Thanks for your reply. It is used in Production database by selected Users so that they can easily modify data on some fields.
|
|
|
Posts:
1,249
Registered:
12/06/01
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 28, 2006 8:00 PM
in response to: user511112
|
|
|
|
|
|
Posts:
1
Registered:
08/29/06
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 29, 2006 1:51 AM
in response to: scott.swank
|
|
|
|
I'm not sure what I prefer in this thread: the massive update directly written by end-user or the proposed solution blatantly opened to sql injection.
|
|
|
Posts:
978
Registered:
08/09/06
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 29, 2006 2:51 AM
in response to: user511112
|
|
|
declare
table_name varchar2;
column_name varchar2;
value <anytype>;
begin
execute immediate 'update '||table_name||' set ||column_name||' = :1' using value;
end; |
|
|
Posts:
866
Registered:
07/04/99
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 29, 2006 3:17 AM
in response to: happy_SQL
|
|
|
|
My personal favourite is the idea that either of,
a) the base requirement
b) the proposed solutions
might be suitable for training purposes.
Incidentally for the OP, I've always thought that an appropriate solution allowing the end-users to update any table in the schema was technically known as 'the application'.
|
|
|
Posts:
19,247
Registered:
08/22/02
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 29, 2006 4:11 AM
in response to: Niall Litchfield
|
|
|
might be suitable for training purposes.
No, that wasn't, my mind was : try and see what happens. And I already explain earlier that...
Nicolas. |
|
|
Posts:
1,288
Registered:
02/07/02
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 29, 2006 4:17 AM
in response to: N Gasparotto
|
|
|
might be suitable for training purposes.
No, that wasn't, my mind was : try and see what
happens. And I already explain earlier that...
Nicolas.
Fully agreed.
And without any WHERE- condition it can be really dangerous 
Regards
Dmytro |
|
|
Posts:
710
Registered:
03/13/01
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Aug 29, 2006 4:18 AM
in response to: user511112
|
|
|
|
It is not a good practice.
As said by Nicolos you might have issues with datatype.
Also you havent specified about the Where clause - hence all records are going to be updated.
Regards,
S.Muthukumar.
|
|
|
Posts:
2
Registered:
09/06/06
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Sep 6, 2006 11:54 AM
in response to: Niall Litchfield
|
|
|
My personal favourite is the idea that either of,
a) the base requirement
b) the proposed solutions
might be suitable for training purposes.
Incidentally for the OP, I've always thought that an
appropriate solution allowing the end-users to update
any table in the schema was technically known as 'the
application'.
Incidentally for the OP, I've always thought that an appropriate solution allowing the end-users to update any table in the schema was technically known as SQL ... 
Adam |
|
|
Posts:
90
Registered:
02/18/01
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Sep 6, 2006 1:22 PM
in response to: user511112
|
|
|
|
It is not good, but you can use dbms_sql to construct dynamic SQL.
An introduction for dbms_sql :
BIND_ARRAY Procedures on
page 100-25
Binds a given value to a given collection
BIND_VARIABLE Procedures on
page 100-28
Binds a given value to a given variable
CLOSE_CURSOR Procedure on
page 100-32
Closes given cursor and frees memory
COLUMN_VALUE Procedure on
page 100-33
Returns value of the cursor element for a given position
in a cursor
COLUMN_VALUE_LONG
Procedure on page 100-36
Returns a selected part of a LONG column, that has been
defined using DEFINE_COLUMN_LONG
DEFINE_ARRAY Procedure on
page 100-37
Defines a collection to be selected from the given
cursor, used only with SELECT statements
DEFINE_COLUMN Procedure on
page 100-39
Defines a column to be selected from the given cursor,
used only with SELECT statements
DEFINE_COLUMN_LONG
Procedure on page 100-41
Defines a LONG column to be selected from the given
cursor, used only with SELECT statements
DESCRIBE_COLUMNS Procedure
on page 100-42
Describes the columns for a cursor opened and parsed
through DBMS_SQL
DESCRIBE_COLUMNS2
Procedure on page 100-43
Describes describes the specified column, an alternative
to DESCRIBE_COLUMNS Procedure
EXECUTE Function on
page 100-44
Executes a given cursor
EXECUTE_AND_FETCH
Function on page 100-45
Executes a given cursor and fetch rows
FETCH_ROWS Function on
page 100-46
Fetches a row from a given cursor
IS_OPEN Function on page 100-47 Returns TRUE if given cursor is open
LAST_ERROR_POSITION
Function on page 100-48
Returns byte offset in the SQL statement text where the
error occurred
LAST_ROW_COUNT Function on
page 100-49
Returns cumulative count of the number of rows
fetched
LAST_ROW_ID Function on
page 100-50
Returns ROWID of last row processed
LAST_SQL_FUNCTION_CODE
Function on page 100-51
Returns SQL function code for statement
OPEN_CURSOR Function on
page 100-52
Returns cursor ID number of new cursor
PARSE Procedure on page 100-53 Parses given statement
VARIABLE_VALUE Procedures
on page 100-55
Returns value of named variable for given cursor
Quote from Oracle PL/SQL Packages and Types Reference
|
|
|
Posts:
939
Registered:
01/10/01
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Sep 6, 2006 2:23 PM
in response to: Dmytro Dekhtyar...
|
|
|
Fully agreed.
And without any WHERE- condition it can be really
dangerous
There's no danger - who better to know the data, how it is used, and the ramifications of a wildcat update than the end users?
|
|
|
Posts:
7,355
Registered:
09/19/99
|
|
|
|
Re: PL/SQL script that can perform update any table in the schema
Posted:
Sep 6, 2006 5:24 PM
in response to: user511112
|
|
|
It is used in Production database by selected Users so that they can easily modify data on some fields.
Somehow, I always end up reading it as "easily corrupt data" on some fields. |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
