Home » SQL & PL/SQL » SQL & PL/SQL » Update in EXECUTE IMMEDIATE (Oracle 11G)
Update in EXECUTE IMMEDIATE [message #649953] Mon, 11 April 2016 07:51 Go to next message
RaShi.Raj
Messages: 81
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 #649954 is a reply to message #649953] Mon, 11 April 2016 08:01 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
RaShi.Raj wrote on Mon, 11 April 2016 05:51
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


Above could be a problem since column MY_COL2 is added to table MY_TABLE & UPDATE operates against APPOINTMENTS table.

Why are you duplicating data?
Re: Update in EXECUTE IMMEDIATE [message #649955 is a reply to message #649954] Mon, 11 April 2016 08:03 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
Sorry, it's 'My_Table' in the 2nd update, not appointments.
Re: Update in EXECUTE IMMEDIATE [message #649956 is a reply to message #649955] Mon, 11 April 2016 08:05 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
BEGIN
execute immediate 'ALTER TABLE My_Table ADD My_Col2 VARCHAR2(1)';

execute immediate 'UPDATE My_Table SET My_Col2 = My_Col1';
END;

Re: Update in EXECUTE IMMEDIATE [message #649957 is a reply to message #649956] Mon, 11 April 2016 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
RaShi.Raj wrote on Mon, 11 April 2016 06:05

BEGIN
execute immediate 'ALTER TABLE My_Table ADD My_Col2 VARCHAR2(1)';

execute immediate 'UPDATE My_Table SET My_Col2 = My_Col1';
END;


should work if MY_COL1 is also VARCHAR2(1) datatype; below is same

BEGIN
execute immediate 'ALTER TABLE My_Table ADD My_Col2 VARCHAR2(1)';

UPDATE My_Table SET My_Col2 = My_Col1;
END;

& don't forget COMMIT; after the UPDATE statement
Re: Update in EXECUTE IMMEDIATE [message #649958 is a reply to message #649956] Mon, 11 April 2016 08:23 Go to previous messageGo to next message
John Watson
Messages: 8052
Registered: January 2010
Location: Global Village
Senior Member
Your problem seems to be that you do not know the syntax of the ALTER TABLE..ADD command.
Re: Update in EXECUTE IMMEDIATE [message #649959 is a reply to message #649957] Mon, 11 April 2016 08:26 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
MY_COL1 is char(1) and My_Col2 is VARCHAR2(1).

If I run that update outside PL/SQL block it works. But in a block, after adding column using Execute Immediate and the very next statement to update doesn't seem to be considering new column 'My_Col2'. I get the following error in that case

PL/SQL: ORA-00904: "My_Col2": invalid identifier
Re: Update in EXECUTE IMMEDIATE [message #649960 is a reply to message #649958] Mon, 11 April 2016 08:27 Go to previous messageGo to next message
John Watson
Messages: 8052
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 #649961 is a reply to message #649958] Mon, 11 April 2016 08:29 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
John Watson wrote on Mon, 11 April 2016 18:53
Your problem seems to be that you do not know the syntax of the ALTER TABLE..ADD command.


I think that statement is fine. I just checked
Re: Update in EXECUTE IMMEDIATE [message #649962 is a reply to message #649959] Mon, 11 April 2016 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
RaShi.Raj wrote on Mon, 11 April 2016 06:26
MY_COL1 is char(1) and My_Col2 is VARCHAR2(1).

If I run that update outside PL/SQL block it works. But in a block, after adding column using Execute Immediate and the very next statement to update doesn't seem to be considering new column 'My_Col2'. I get the following error in that case

PL/SQL: ORA-00904: "My_Col2": invalid identifier


Perfect example of Oracle Read Consistency
UPDATE retains state of DB as it existed at the BEGIN statement & does not see the results from EXECUTE IMMEDIATE ALTER

BEGIN
execute immediate 'ALTER TABLE My_Table ADD My_Col2 VARCHAR2(1)';
END;
UPDATE My_Table SET My_Col2 = My_Col1;
COMMIT;
Re: Update in EXECUTE IMMEDIATE [message #649963 is a reply to message #649962] Mon, 11 April 2016 08:47 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
So, you suggest to use BEGIN-END block for ALTER and then UPDATE ? I tried that but still same problem..

BEGIN

BEGIN
execute immediate 'ALTER TABLE My_Table ADD My_Col2 VARCHAR2(1)';
END;

UPDATE My_Table SET My_Col2 = My_Col1;

END;

ORA-06550: line 7, column 29:
PL/SQL: ORA-00904: "My_Col2": invalid identifier
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
Re: Update in EXECUTE IMMEDIATE [message #649964 is a reply to message #649963] Mon, 11 April 2016 08:50 Go to previous messageGo to next message
John Watson
Messages: 8052
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 #649965 is a reply to message #649964] Mon, 11 April 2016 09:02 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
oops!!! I have missed execute immediate for update in my code but while posting here gave that and confused you...my stupid. Sorry BlackSwan and thank you...you too John
Re: Update in EXECUTE IMMEDIATE [message #649968 is a reply to message #649965] Mon, 11 April 2016 09:30 Go to previous messageGo to next message
cookiemonster
Messages: 13721
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 #676118 is a reply to message #649968] Wed, 15 May 2019 08:07 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
2019, and it still does not work. Placing "UPDATE" in "EXECUTE" bears no effect.
And why do we need "EXECUTE IMMEDIATE" anyway?
Re: Update in EXECUTE IMMEDIATE [message #676119 is a reply to message #676118] Wed, 15 May 2019 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
mc0re wrote on Wed, 15 May 2019 06:07
2019, and it still does not work. Placing "UPDATE" in "EXECUTE" bears no effect.
And why do we need "EXECUTE IMMEDIATE" anyway?

DDL is NOT allowed from inside PL/SQL procedure, so first EXECUTE IMMEDIATE is required to issue ALTER (DDL) statement
EXECUTE IMMEDIATE is required for the UPDATE statement since the column to be changed did NOT exist at the start of the PL/SQL procedure.

If both ALTER & UPDATE were done a plain SQL, not inside PL/SQL, then neither EXECUTE IMMEDIATE is required.

You are free to abuse the code any way you desire as long as you comply with Oracle's rules for each language.

P.S.
You don't get a second chance to make a first impression & language requirements, limitations, & restrictions for have have not changed within the last 20+ years.
Re: Update in EXECUTE IMMEDIATE [message #676120 is a reply to message #676119] Wed, 15 May 2019 09:25 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
Well, 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;
/
Re: Update in EXECUTE IMMEDIATE [message #676121 is a reply to message #676120] Wed, 15 May 2019 09:34 Go to previous messageGo to next message
John Watson
Messages: 8052
Registered: January 2010
Location: Global Village
Senior Member
This syntax is wrong:

UPDATE DATE_LIST SET RunDate = TO_DATE(DT1, ''YYYYMMDD'')


Re: Update in EXECUTE IMMEDIATE [message #676122 is a reply to message #676121] Wed, 15 May 2019 09:38 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
What is wrong there? Apostrophes must be doubled inside a string, if this is what you're pointing out...
Re: Update in EXECUTE IMMEDIATE [message #676123 is a reply to message #676120] Wed, 15 May 2019 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
mc0re wrote on Wed, 15 May 2019 07:25
Well, 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.
Re: Update in EXECUTE IMMEDIATE [message #676124 is a reply to message #676121] Wed, 15 May 2019 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
John Watson wrote on Wed, 15 May 2019 07:34
This syntax is wrong:

UPDATE DATE_LIST SET RunDate = TO_DATE(DT1, ''YYYYMMDD'')


NEVER, Ever use TO_DATE() function on any DATE datatype.
TO_DATE() function is designed to convert or transform a STRING in a DATE datatype.
Since DT1 is already a DATE, is it totally nonsensical & useless to TO_DATE(DT1)
TO_DATE(DT1) requires Oracle to convert DT1 to a STRING & then convert the STRING back to a DATE datatype!

DON'T do that!
Re: Update in EXECUTE IMMEDIATE [message #676125 is a reply to message #676123] Wed, 15 May 2019 09:47 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
Don't be that ruse. I was forced to switch to Oracle from T-SQL, and I'm trying to find out how I can do in Oracle what in T-SQL is done in two lines. So I'm very well aware that the problem is my lack of understanding of Oracle.

I tried adding BEGIN/END around "ALTER" and COMMIT after it to no avail.
The article you sent contains no "EXECUTE IMMEDIATE" phrase, so I probably need to dig my way through it while you laugh knowing exactly what I need to do but prefering to keep it to yourself.
Re: Update in EXECUTE IMMEDIATE [message #676126 is a reply to message #676124] Wed, 15 May 2019 09:48 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
Nope, DT1 is a string (VARCHAR(20)), which contains a date.
Re: Update in EXECUTE IMMEDIATE [message #676127 is a reply to message #676126] Wed, 15 May 2019 09:58 Go to previous messageGo to next message
cookiemonster
Messages: 13721
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 Go to previous messageGo to next message
cookiemonster
Messages: 13721
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 #676129 is a reply to message #676127] Wed, 15 May 2019 10:03 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
The count returned 0, thank you for the hint. Now I have to figure out why USER_TAB_COLUMNS only contains TMPTBL.X column :-/
Re: Update in EXECUTE IMMEDIATE [message #676130 is a reply to message #676123] Wed, 15 May 2019 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13721
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Wed, 15 May 2019 15:38
mc0re wrote on Wed, 15 May 2019 07:25
Well, 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 #676131 is a reply to message #676130] Wed, 15 May 2019 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 26626
Registered: January 2009
Location: SoCal
Senior Member
>What uncomitted DML do you think is relevant here?
The UPDATE statement below
>EXECUTE IMMEDIATE 'UPDATE DATE_LIST SET RunDate = TO_DATE(DT1, ''YYYYMMDD'')';
Re: Update in EXECUTE IMMEDIATE [message #676132 is a reply to message #676131] Wed, 15 May 2019 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 13721
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 #676133 is a reply to message #676126] Wed, 15 May 2019 10:33 Go to previous messageGo to next message
John Watson
Messages: 8052
Registered: January 2010
Location: Global Village
Senior Member
mc0re wrote on Wed, 15 May 2019 15:48
Nope, DT1 is a string (VARCHAR(20)), which contains a date.
Oh, right - I'm sorry, I had thought that it was a date.

Is it possible for you to show what you are doing and what the result is? You'll have seen how I did it here
http://www.orafaq.com/forum/mv/msg/200579/649964/#msg_649964
can you do the same?
Re: Update in EXECUTE IMMEDIATE [message #676134 is a reply to message #676129] Wed, 15 May 2019 10:43 Go to previous messageGo to next message
cookiemonster
Messages: 13721
Registered: September 2008
Location: Rainy Manchester
Senior Member
mc0re wrote on Wed, 15 May 2019 16:03
The 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 #676135 is a reply to message #676134] Wed, 15 May 2019 10:51 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
Does USER_TAB_COLUMNS contain the tables from the current schema or current user?
Re: Update in EXECUTE IMMEDIATE [message #676136 is a reply to message #676135] Wed, 15 May 2019 10:59 Go to previous messageGo to next message
cookiemonster
Messages: 13721
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 #676137 is a reply to message #676136] Wed, 15 May 2019 11:50 Go to previous messageGo to next message
mc0re
Messages: 8
Registered: May 2019
Junior Member
This is the setup here - a lot of schemas, and user <> schema. So I need to look at ALL_TAB, that was my mistake Smile
Re: Update in EXECUTE IMMEDIATE [message #676140 is a reply to message #676137] Wed, 15 May 2019 12:39 Go to previous message
cookiemonster
Messages: 13721
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
Previous Topic: Extract clob xml data to get node value
Next Topic: How to get or concatenate a value from inner most query
Goto Forum:
  


Current Time: Mon Nov 18 15:29:07 CST 2019