Update in EXECUTE IMMEDIATE [message #649953] |
Mon, 11 April 2016 07:51 |
|
RaShi.Raj
Messages: 84 Registered: May 2014 Location: Bangalore
|
Member |
|
|
Hi,
How can I update one column to another from the same table using execute immediate.
E.G. I have a table My_Table with column My_Col1 with some values. For some reason I need to write a script to create a new column My_Col2 and copy the contents of My_col1 to My_Col2 for all rows.
Something like this...
BEGIN
execute immediate 'ALTER TABLE My_Table ADD My_Col2 VARCHAR2(1)';
execute immediate 'UPDATE appointments SET My_Col2 = My_Col1';
END;
Thanks
|
|
|
|
|
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #649960 is a reply to message #649958] |
Mon, 11 April 2016 08:27 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Well, knock me down with a feather. You ALTER TABLE command does work. I always thought the new column had to be enclosed in brackets. But it doesn't! You code works fine for me.
|
|
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #649964 is a reply to message #649963] |
Mon, 11 April 2016 08:50 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
orcla>
orcla> begin
2 execute immediate 'alter table dept add(c1 number)';
3 execute immediate 'update dept set c1=deptno';
4 end;
5 /
PL/SQL procedure successfully completed.
Commit complete.
orcla> select * from dept;
DEPTNO DNAME LOC C1
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 10
20 RESEARCH DALLAS 20
30 SALES CHICAGO 30
40 OPERATIONS BOSTON 40
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #649968 is a reply to message #649965] |
Mon, 11 April 2016 09:30 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Since this looks to be something that should be a one time activity all you should need is the alter table and update commands on their own, one after the other. No need for PL/SQL blocks or execute immediate.
|
|
|
|
|
|
|
|
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676127 is a reply to message #676126] |
Wed, 15 May 2019 09:58 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If there's a problem with the syntax then oracle will throw an error. Unless there's some other code you haven't shown us that swallows errors, you'll see the error.
So assuming there is no error and the code completes successfully there's only tow options for what happens:
1) The rundate column gets added
2) The count returns 0 so nothing happens.
Have you checked user_tab_columns shows what you think it shows?
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676128 is a reply to message #676127] |
Wed, 15 May 2019 10:01 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Also are you sure all the values in dt1 are in the format YYYYMMDD?
If there's any that aren't then the update will error out. That won't stop the new column getting added though - DML auto-commits.
Might be an idea to add a dbms_output.put_line after the select to see what the count is.
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676130 is a reply to message #676123] |
Wed, 15 May 2019 10:05 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Wed, 15 May 2019 15:38mc0re wrote on Wed, 15 May 2019 07:25Well, what I don't understand is why the statement, executed successfully, doesn't change the table: the RunDate column does not appear...
DECLARE Dt1Exists NUMBER;
BEGIN
SELECT COUNT(*) INTO Dt1Exists FROM USER_TAB_COLUMNS WHERE Table_Name = 'DATE_LIST' AND Column_Name = 'DT1';
IF (Dt1Exists > 0)
THEN
EXECUTE IMMEDIATE 'ALTER TABLE DATE_LIST ADD RunDate DATE';
EXECUTE IMMEDIATE 'UPDATE DATE_LIST SET RunDate = TO_DATE(DT1, ''YYYYMMDD'')';
--EXECUTE IMMEDIATE 'ALTER TABLE DATE_LIST DROP COLUMN DT1';
END IF;
END;
/
It appears that you have no understanding of Oracle's data concurrency model.
No other session can see UNCOMMITTED DML (INSERT, UPDATE, or DELETE) changes made by other session.
Oracle will ALWAYS present to a session the state of the database as it existed when the session started or to the most recent COMMIT.
Consider to Read The Fine Manual below.
https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020
The problem you have is NOT an Oracle problem, but Problem Exists Between Keyboard And Chair.
What on earth are you on about?
What uncomitted DML do you think is relevant here?
If you're going to be rude it helps if you know what you're talking about. Though even then it's better to just not be rude at all.
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676132 is a reply to message #676131] |
Wed, 15 May 2019 10:30 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you're saying that other sessions can't see the results of the update until after the session running it commits you could just say so, but:
1) You have no idea if the OP didn't commit
2) You have no idea if the OP was checking the results from another session
3) You completely missed the fact that the OP said that the column itself wasn't appearing. Which would imply the alter table didn't run.
Given how much you criticise others for not paying attention/thinking things through it's quite ironic how shockingly bad you are at doing those things.
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676134 is a reply to message #676129] |
Wed, 15 May 2019 10:43 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mc0re wrote on Wed, 15 May 2019 16:03The count returned 0, thank you for the hint. Now I have to figure out why USER_TAB_COLUMNS only contains TMPTBL.X column :-/
user_tab_columns lists columns of tables that belong to the current user.
Possibilites:
1) you're logged in as the wrong user
2) you've typoed the name(s)
3) that table just doesn't contain that column
4) you've managed to create a mixed case name - all names in the data dictionary are in upper case unless you wrap the name in double-quotes when you create it - then it's in the specified case:
SQL> CREATE TABLE bobby (normal_col VARCHAR2(10), "mIxEd_CASE_col" VARCHAR2(10));
Table created
SQL> select column_name from user_tab_cols where table_name = 'BOBBY';
COLUMN_NAME
--------------------------------------------------------------------------------
mIxEd_CASE_col
NORMAL_COL
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676136 is a reply to message #676135] |
Wed, 15 May 2019 10:59 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Generally they one and the same unless you've been using ALTER SESSION SET CURRENT_SCHEMA
If so - logged in user is what it shows.
If you want stuff from a different schema to the one you logged in as you would need to look in all_tab_columns or dba_tab_columns if you have access to it.
That said - scripts that alter tables should always be run as the only of the table. Most users don't have the necessary privs to alter other users objects.
|
|
|
|
Re: Update in EXECUTE IMMEDIATE [message #676140 is a reply to message #676137] |
Wed, 15 May 2019 12:39 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Doubtful - unless the user you're running the code as has the alter any table privilege they won't be able to add the new column. You should be running the script as the table owner
|
|
|