execute immediate create table and update table

Question

I am creating a temp table in pl/sql using execute immediate & also inserting in the table why create table.

After that I m updating the table. But i m getting error table doesn't exists as it is not creating the table thr execute immediate

sample code---------

begin
  execute immediate 'create table t23 as  select ''1'' aa from dual'; 
  update t23 set aa ='2' where aa='1';
  COMMIT ;
end;

80% of the time you don't need a temp table at all - you can do it in one SQL. The other 20% of the time, you can use a Global Temporary Table, or PL/SQL arrays. — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 18, 2011 at 13:24

Tony Andrews · Accepted Answer · 2011-04-18 12:04:05Z

11

You are using static SQL to perform the update, and this is validated before the PL/SQL is run, and so finds that it references a table that doesn't currently exist. You could use dynamic SQL to perform the update:

begin
  execute immediate 'create table t23 as  select ''1'' aa from dual'; 
  execute immediate 'update t23 set aa =''2'' where aa=''1''';
  COMMIT ;
end;

However, really it is bad practice in Oracle to dynamically create temporary tables like this in the first place. Why are you doing it? Once we know that perhaps we can suggest a better alternative.

answered Apr 18, 2011 at 12:04

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thomas Jones-Low Over a year ago

Create a global temporary table. No one else can see your data, and you can avoid the execute immediate.

Tony Andrews Over a year ago

@Thomas: I might have suggested that, but at the moment I have no clue whether this is what Namita really needs or not.

Thomas Jones-Low Over a year ago

It haa been my experience that when a pl/sql procedure does the execute immediate create table, it is most likely code moved from another RDBMS. But like you I'm not entirely sure what the request is for. Which is why I left a comment, not an answer.

tbone Over a year ago

if the underlying query is the same each time, you could also create a mat view and simply do a complete refresh as needed. Of course would need more info about whats going on here

Jakub P Over a year ago

helpful answer. in my project we already created some tables (backup some data) with EXECUTE IMMEDIATE and then using those tables to perform DML using static SQL. I have no idea, how it worked, why it's not working today. (Oracle 12c 12.2)

Collectives™ on Stack Overflow

execute immediate create table and update table

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related