anonymous block in 11g

Question

I need a help on this.

I am trying to create a pl/sql anonymous block and when I run that it shows its completed but it don't run the code. It should give me an error saying name is already used by an existing object. Can someone help me on this. I am actually creating procedures but just trying this code as a sample.

DECLARE 

    V_REF_TBL                       VARCHAR2(100);
    V_SQL                           LONG;

begin
    V_REF_TBL :='My_TABLE';
    v_SQL :='truncate table '||V_REF_TBL ;
    EXECUTE IMMEDIATE V_SQL;

    EXECUTE IMMEDIATE 'CREATE TABLE '|| V_REF_TBL ||' parallel 9 nologging pctfree 0 as 
    select * from dual';  
End;

Sorry, I can't reproduce this. If the table exists, I get a 'name is already used by an existing object' error when I run your code. If the table doesn't exist, I get a 'table or view does not exist' error. — Luke Woodward, Mar 09 '13 at 12:27
Why do you need to truncate a table which doesn't exist? Why are you trying to create a table which already exists? Are you trying to implement a temporary table? If so, oracle has better solutions. http://stackoverflow.com/a/2042029/146325 — APC, Mar 09 '13 at 21:12
Also `parallel 9` is Teh Suck! Parallel degrees should always be powers of two. — APC, Mar 09 '13 at 21:13

score 0 · Answer 1 · answered Mar 10 '13 at 12:05

Possibly you're looking for this:

  <<my_block>>
  Declare
    table_name varchar2(30);
    counter    number;
  Begin
    table_name := 'my_table';

    select count(*)
    into   counter
    from   user_tables
    where  table_name = Upper(Trim(my_block.table_name)) and
           dropped = 'NO';

    if counter = 0
    then
      execute immediate 'create table '||table_name||' as ... etc';
    else
      execute immediate 'truncate table '||table_name;
      execute immediate 'insert into '||table_name' select ... etc';
    end if;
  end my_block;

anonymous block in 11g

1 Answers1