Execute Immediate Code Issues !!

[Sahib Singh]

Code: Select all

   begin
        sql_stmt := 'select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' $3 tablespace PART_
DATA_' || PARTITION_YR || ' parallel $2;' from dba_tab_partitions  where partition_name=part_rec.part_name and table_owner = '$SCH
' and table_name=part_rec.tab_name;';
         execute immediate sql_stmt;
         EXCEPTION  WHEN NO_DATA_FOUND
         THEN
         NULL;
       END;

Tim,


This peace of code is part of big script. It works if i remove the Execute Immediate part, means it works as follows

Code: Select all

select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' $3 tablespace PART_
DATA_' || PARTITION_YR || ' parallel $2;' into incr2 from dba_tab_partitions  where partition_name=part_rec.part_name and table_owner = '$SCH
' and table_name=part_rec.tab_name;';
         EXCEPTION  WHEN NO_DATA_FOUND
         THEN
         NULL;
     

But with the execute immediate it goves errors something like


sql_stmt := 'select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' compress tablespace PART_DATA_' || PARTITION_YR || ' parallel 4;' from dba_tab_partitions where partition_name=part_rec.part_name and table_owner = 'UBSDB_EOD' and table_name=part_rec.tab_name;';
*
ERROR at line 26:
ORA-06550: line 26, column 30:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_




Can you help ?


Thanks
Sahib Singh

[Tim...]

Hi.

1) This is a SELECT that produces one or more rows of a concatenated string. That means it returns some rows. You have not used an INTO or a BULK COLLECT INTO to allow it to return any rows.
2) The string includes a ";" at the end, which is not a valid character at the end of a select statement in dynamic SQL.
3) This is not something that actually needs dynamic SQL. You are making life hard for yourself by using it. I would probably do something like this:

Code: Select all

BEGIN       
  FOR cur_str IN (select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||
                         ' $3 tablespace PART_DATA_' || PARTITION_YR || ' parallel $2;'
                  from   dba_tab_partitions
                  where partition_name=part_rec.part_name
                  and table_owner = '$SCH'
                  and table_name=part_rec.tab_name)
  LOOP
    execute immediate cur_str;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;

Note. This assumes your query is actually valid. I don't have your system so I can't test it.

Cheers

Tim...

[Sahib Singh]

Thanks. It gives me

Code: Select all

  49   50   51   52   53   54   55  old  16:       SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||&start_fiscal ,'YYYYMMDD')
new  16:           SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||0102 ,'YYYYMMDD')
    execute immediate cur_str;
                      *
ERROR at line 33:
ORA-06550: line 33, column 23:
PLS-00382: expression is of wrong type
ORA-06550: line 33, column 5:
PL/SQL: Statement ignored



[Tim...]

Hi.

The joys of writing code you can't run because you don't have the environment. I completely missed out the column name of the query.

Code: Select all

BEGIN       
  FOR cur_str IN (select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||
                         ' $3 tablespace PART_DATA_' || PARTITION_YR || ' parallel $2;'  AS ddl_stmt
                  from   dba_tab_partitions
                  where partition_name=part_rec.part_name
                  and table_owner = '$SCH'
                  and table_name=part_rec.tab_name)
  LOOP
    DBMS_OUTPUT.put_line (cur_str.ddl_stmt);
    execute immediate cur_str.ddl_stmt;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;

I've included a DBMS_OUTPUT call in this to push out the ALTER TABLE statement that you are attempting to run. It will only display if you have "SET SERVEROUTPUT ON".Obviously, if this statement is not valid, the EXECUTE IMMEDIATE will not work.

Cheers

Tim...

[Sahib Singh]

Set serveroutput is ON Tim

Here is what i get now with new code

Code: Select all


SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57  old  16:     SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||&start_fiscal ,'YYYYMMDD')
new  16:           SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||0102 ,'YYYYMMDD')
alter table UBSDB_EOD.SCENARIO_TRADE_LINK_EOD move partition P_20090929 compress
tablespace PART_DATA_2009 parallel 4;
  declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 35





The moment i take out the EXECUTE IMMEDIATE, the code works.

[Sahib Singh]

Tim,

If i comment out the ==> execute immediate cur_str.ddl_stmt;


The following code works but

SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 old 16: SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||&start_fiscal ,'YYYYMMDD')
new 16: SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||0102 ,'YYYYMMDD')
declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 34

Code: Select all


BEGIN
  FOR cur_str IN (select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||
                         ' $3 tablespace PART_DATA_' || PARTITION_YR || ' parallel $2;'  ddl_stmt
                  from   dba_tab_partitions
                  where partition_name=part_rec.part_name
                  and table_owner = '$SCH'
                  and table_name=part_rec.tab_name)
  LOOP
    execute immediate cur_str.ddl_stmt;
    DBMS_OUTPUT.put_line (cur_str.ddl_stmt);
  END LOOP;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
 END;



[Tim...]

Hi.

OK. The ";" at the end of the statement has crept back in. You need to remove the final ";" within the string. Change

Code: Select all

' parallel $2;'

to

Code: Select all

' parallel $2'

When you are running some DDL from SQL*Plus you must have the ";" to tell SQL*Plus the line is finished. When you run the same DDL through dynamic SQL the ";" is not necessary and is seen as an invalid character.

Cheers

Tim...

[Sahib Singh]

Thanks Tim, It seems to be working. Second part of code is bit more complex as there may be more than one indexes on a table which we are tackling as below. Can you pls advise how we can put this also to use EXECUTE IMMEDIATE insetad of current solution of building the SPOOL SQL and then running

Code: Select all

 begin
        For r in (select 'alter index '||owner||'.'||dba_ind_partitions.index_name||' rebuild partition '||partition_name ||' $4 tab
lespace PART_INDEX_' || PARTITION_YR || ' parallel $2;' INDEX_QUERY from dba_indexes,dba_ind_partitions
        where table_name=part_rec.tab_name
        and dba_ind_partitions.index_name=dba_indexes.index_name and partition_name=part_rec.part_name
        and dba_indexes.table_owner='$SCH' and table_name=part_rec.tab_name)
        LOOP
         DBMS_OUTPUT.PUT_LINE (r.INDEX_QUERY);
        END LOOP;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
          NULL;
       END;
   end loop;
   end;
/


[Tim...]

Hi.

Remember to remove the extra ";". So

You seems to have an extra end loop in there. It helps if you indent your code properly, then you can see things like this a lot better.

Code: Select all

BEGIN
  FOR r IN (SELECT 'alter index '||owner||'.'||dba_ind_partitions.index_name||' rebuild partition '||partition_name ||
                   ' $4 tablespace PART_INDEX_' || PARTITION_YR || ' parallel $2' INDEX_QUERY
            FROM   dba_indexes,dba_ind_partitions
            WHERE  table_name = part_rec.tab_name
            AND    dba_ind_partitions.index_name=dba_indexes.index_name
            AND    partition_name=part_rec.part_name
            AND    dba_indexes.table_owner='$SCH'
            AND    table_name=part_rec.tab_name)
  LOOP
    DBMS_OUTPUT.PUT_LINE (r.INDEX_QUERY);
    EXECUTE IMMEDIATE r.INDEX_QUERY;
  END LOOP;
END;

Remember, the NO_DATA_FOUND exception will never fire for a cursor FOR LOOP. If not rows are returned, the code inside the loop never fires, so you can remove the exception handler on this and the previous section of code now you are using the cursor FOR LOOP. This code is unncessary in both examples we have discussed.

Code: Select all

 EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;

Cheers

Tim...

[Sahib Singh]

Tim,

I will remove the Exceptions.


I still get the following errors though in this code

Code: Select all


/* Build the SQL to move the Index Partiotions to Archival Tablespaces */

BEGIN
  FOR r IN (SELECT 'alter index '||owner||'.'||dba_ind_partitions.index_name||' rebuild partition '||partition_name ||
                   ' $3 tablespace PART_INDEX_' || PARTITION_YR || ' parallel $2' INDEX_QUERY
            FROM   dba_indexes,dba_ind_partitions
            WHERE  table_name = part_rec.tab_name
            AND    dba_ind_partitions.index_name=dba_indexes.index_name
            AND    partition_name=part_rec.part_name
            AND    dba_indexes.table_owner='$SCH'
            AND    table_name=part_rec.tab_name)
  LOOP
    DBMS_OUTPUT.PUT_LINE (r.INDEX_QUERY);
     EXECUTE IMMEDIATE r.INDEX_QUERY;
  END LOOP;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
 END;

/
 spool off;
 exit
 EOF
~


SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61  old  15:         SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||&start_fiscal ,'YYYYMMDD')
new  15:           SELECT (CASE WHEN part_rec.dat >= TO_DATE(TO_CHAR(part_rec.dat,'yyyy')||0102 ,'YYYYMMDD')
  declare
*
ERROR at line 1:
ORA-06550: line 60, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe




[Sahib Singh]

TIm,

Pls ignore my previous POST. Seems i know the issue.

Thanks

[Tim...]

OK.

[Sahib Singh]

Tim,


My code below now works as a sheel scriot. But the problem is that once SQL part is completed its not taking the next Shell scripts commands in the code file. Any idea pls ?

Code: Select all

# Initialize Constants
#---------------------
   PROG_NAME=`basename $0 `
   MY_NAME=`echo ${PROG_NAME} | cut -d "." -f1`
   PROC_ID=$$

   USER_ID=` id |cut -d "(" -f 2 | cut -d ")" -f 1`
   tty >/dev/null
/exception
             ELSE to_number(to_char(part_rec.dat,'yyyy'))
              END) FISCAL_YEAR into PARTITION_YR
          FROM temp_table where part_name=part_rec.part_name
          ORDER BY 1;


  /* Build the SQL to move the Table Partitions to Archival tablespaces  */

      begin
       for cur_str IN (select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||
                         ' $3 tablespace PART_DATA_' || PARTITION_YR || ' parallel $2'  ddl_stmt
                  from   dba_tab_partitions
                  where partition_name=part_rec.part_name
                  and table_owner = '$SCH'
                  and table_name=part_rec.tab_name)
       loop
        DBMS_OUTPUT.put_line (cur_str.ddl_stmt);
        execute immediate cur_str.ddl_stmt;
       end loop;
        exception
         when no_data_found then
         NULL;
         when others then
        dbms_output.put_line('ERROR: failed due to '||sqlerrm) ;
     end;

  /* Build the SQL to move the Index Partiotions to Archival Tablespaces */
      BEGIN
         FOR r IN (SELECT 'alter index '||owner||'.'||dba_ind_partitions.index_name||' rebuild partition '||partition_name ||
                   ' $4 tablespace PART_INDEX_' || PARTITION_YR || ' parallel $2' INDEX_QUERY
            FROM   dba_indexes,dba_ind_partitions
            WHERE  table_name = part_rec.tab_name
            AND    dba_ind_partitions.index_name=dba_indexes.index_name
            AND    partition_name=part_rec.part_name
            AND    dba_indexes.table_owner='$SCH'
            AND    table_name=part_rec.tab_name)
        LOOP
             DBMS_OUTPUT.PUT_LINE (r.INDEX_QUERY);
             execute immediate r.INDEX_QUERY;
        END LOOP;
     END;
    end loop;
        exception
         when no_data_found then
         NULL;
         when others then
         dbms_output.put_line('ERROR: failed due to '||sqlerrm) ;
   end;
   /
 spool off;
 exit
 EOF


 ====> The Script doesnt execute the following Portion
  touch test
  egrep "ERROR" part_move.log > /opt/oracle/admin/dba/bchahal/smurf.log
  if [ -s /opt/oracle/admin/dba/bchahal/smurf.log ]; then
  PRIORITY=1
  fi

[Tim...]

Sorry. No idea what you are talking about.

Cheers

Tim...