CODEWITHSUNDEEP
sqloracle-databasedynamiccodeblocks
Lucas Buenahora
Lucas Buenahora

Reputation: 1

Combining a block query and an usual query in oracle sql

Hey I'm new to Oracle SQL but I'm a frequent user of PostGreSQL and SQL Server. I'm currently trying to run a query that will materialize a table every time I run the query. The actual application is more complicated but here is the general idea:

declare
  v_exists number:=0;
BEGIN

    select count(1)
    into v_exists
    from all_tables
    where table_name = 'FFF';

    if v_exists >0 then 
      EXECUTE IMMEDIATE 'DROP TABLE FFF';
      dbms_output.put_line('Table dropped');
    end if;

END;
/


create table fff as 
  select *
  from my_table;

Both blocks of code work if I run them separately but when I run them together it doesn't work. Is there a way to combine these kind of queries in just one script?

Thanks

Upvotes: 0

Views: 135

Answers (3)

BriteSponge
BriteSponge

Reputation: 1054

An alternative is a script in SQLPLUS;

WHENEVER SQLERROR CONTINUE

DROP TABLE fff
/

WHENEVER SQLERROR EXIT FAILURE

CREATE TABLE fff
AS SELECT * FROM my_table
/

This will try to drop the table but if it can't will carry on and then try to create the table. If that fails for some reason then the script will fail.

Upvotes: 0

APC
APC

Reputation: 146209

This is a pattern which is common in SQL Server (and perhaps PostgreSQL too) but which is considered an anti-pattern in Oracle, Oracle provides much better ways of working with data sets than executing DDL on the fly.

One approach is to use PL/SQL collections to cache data in memory. This is suitable when the volumes of data are small, because collections are stored in session memory. Find out more.

Another approach is Global Temporary Tables which are permanent structures with transient data (restricted to scope of transaction or session). Find out more.

The enterprise edition comes with features to pin result sets in memory; this is useful when we want to share the result set across multiple sessions, and the life span of the result set is relatively long (i.e. slowly changing reference data). Find out more.

A further, and perhaps the best, approach is to write efficient queries which obviate the need for caching. DDL is an expensive operation which introduces risk and complexity into applications. The most performative way of doing something is usually to avoid doing it.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Try doing the second as dynamic SQL as well:

declare
  v_exists number := 0;
BEGIN

    select count(1)
    into v_exists
    from all_tables
    where table_name = 'FFF';

    if v_exists >0 then 
      EXECUTE IMMEDIATE 'DROP TABLE FFF';
      dbms_output.put_line('Table dropped');
    end if;

    EXECUTE IMMEDIATE 'create table fff as select * from my_table';
END;
/

Upvotes: 0

Related Questions