DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: EXECUTE Problems

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Posts
    58
    That did not work. It did not like that statement. Here is what I have below:

    Declare
    V_sql_1 varchar2(100);
    V_sql_2 varchar2(100);
    v_sql_3 varchar2(100);
    Cursor Ind_Cur is Select index_name from all_Indexes where TABLESPACE_NAME = 'PRO_DATA' and owner in ('PRO','OHT') Order by INDEX_NAME;

    Begin

    For Ind_rec in Ind_Cur Loop

    v_sql_1 := 'Alter index PRO.';

    v_sql_2 := ind_rec.index_name;

    v_sql_3 := ' rebuild tablespace pro_index';

    v_sql_1 := v_sql_1||v_sql_2||v_sql_3;

    execute immediate v_sql_1;

    dbms_output.put_line(v_sql_1||' has been moved to PRO_INDEX');

    End loop;

    End;

    ERROR: execute immediate v_sql_1;
    *
    ORA-06550: line 19, column 9:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

    := . ( @ % ;



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    add dbms_output.put_line(v_sql_1) before your execute immediate and see if the syntaxis is good

  3. #3
    Join Date
    Jan 2002
    Posts
    58

    HELP

    Same problem. I just want to write a script that rebuilds all the indexes for a specific User into a different tablespace. how can I do this.

    jeremy


  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Check following problem :
    1.
    v_sql_1 := 'Alter index PRO.'; -- but u read from all_Indexes users -> in ('PRO','OHT') .

    2. (but only may be???)

    (You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or for a partition (ALTER INDEX ... MODIFY PARTITION). You can specify PCTFREE in all other forms of the ALTER INDEX statement.)

    ur first(old) tablespace has different default PCTFREE parameter, then second(new) tablespace.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    so is your syntax good? you didnt think by adding dbms_output your proc will work huh?!?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it works for me I just tried it

  7. #7
    Join Date
    Jan 2002
    Posts
    58

    WHAT version of Oracle

    i am running 8.0.4.0 and I am getting the error around Execute and the way I use it. Man this sucks.

    THIS IS THE ERROR:
    46>
    execute immediate v_sql_1;
    *
    ORA-06550: line 13, column 21:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

    := . ( @ % ;

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U cannot use EXECUTE IMMEDIATE in 8.0.4
    u should use dbms_sql package for executing synamic sql statments.

  9. #9
    Join Date
    Jan 2002
    Posts
    58
    How about this syntax..




    Declare
    V_sql_1 varchar2(100);
    V_sql_2 varchar2(100);
    v_sql_3 varchar2(100);
    Cursor Ind_Cur is Select index_name from all_Indexes where TABLESPACE_NAME = 'PRO_DATA' and owner = 'PRO' Order by INDEX_NAME;

    Begin
    For Ind_rec in Ind_Cur Loop
    v_sql_1 := 'Alter index PRO.';
    v_sql_2 := ind_rec.index_name;
    v_sql_3 := ' rebuild tablespace pro_index';
    v_sql_1 := v_sql_1||v_sql_2||v_sql_3;
    dbms_output.put_line(v_sql_1||' has been moved to PRO_INDEX');
    dbms_sql(alter index pro.ind_rec.index_name rebuild tablespace pro_index);
    End loop;

    End;

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    This is little bit more difficult, then use EXECUTE IMMEDIATE.
    U should:
    -- open (create) cursor
    -- parse statment
    ...

    This is little example for u:
    -- ----------------------------------------------
    create or replace
    procedure Do_Sql(p_TXT_SQL varchar2) is
    temp_sql integer;
    r number ;
    res number ;
    begin
    temp_sql := dbms_sql.open_cursor;
    dbms_sql.parse(temp_sql,temp_sql,DBMS_SQL.NATIVE);
    r := dbms_sql.execute(temp_sql);
    dbms_sql.close_cursor(temp_sql);
    return;
    exception
    when INVALID_CURSOR then
    raise_application_failure(-20199, 'Error in do_sql() - invalid statment');
    when OTHERS then
    raise_application_failure(-20199, 'Error in do_sql() - undefined error');
    end;
    /
    -- --------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width