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

Thread: re: creating a spatial index using dbms_job

  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Angry re: creating a spatial index using dbms_job

    hi! Guys ,

    I am running Oracle 9i on windows 2000 professional.

    I am creating a spatial index on a table column.

    When i try to create it from sqlplus command prompt ..

    it errors out END OF FILE ON COMMUNICATION CHANNEL ..

    It was suggested to be run as a job using DBMS_JOB.

    I created a proc in which I use dbms_sql for creating the spatial
    index.

    I submit the proc as a job using DBMS_JOB.SUBMIT

    When I try to run the job using DBMS_JOB.RUN(32); where 32 is the
    job number it is bombing out.

    *******************************************
    submitted as job number 32

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.00
    SQL> execute dbms_job.run(32);
    -29855ORA-29855: error occurred in the execution of ODCIINDEXCREATE
    routine
    ORA-13249: internal error in S

    PL/SQL procedure successfully completed.
    ********************************************

    Could any body help me resolve the issue and succesfully create the
    spatial index ????

    I have only oracle to work on this and not any applications like Arcse.

    thanks,
    harish
    thanks,
    harish

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Does the server log file have any further information or error codes in it?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2003
    Posts
    312
    what version of 9i are you running on???? be sure you are on 9.2.0.4 or else the lower version had a lot of bug. Also for your problem, make sure your spatial data are valid, you can use the validate funtion package to check your data. before you use the dbms_submit to submit your job, be sure you can create the spatial index first.

  4. #4
    Join Date
    Jan 2004
    Posts
    58

    Unhappy re: from the alert.log file

    Thu May 13 15:01:00 2004
    Errors in file D:\oracle\admin\DFT\udump\ORA01628.TRC:
    ORA-12012: error on auto execute of job 30
    ORA-20000: ORU-10028: line length overflow, limit of 255 bytes/chars per line
    ORA-06512: at "SYS.DBMS_OUTPUT", line 34
    ORA-06512: at "SYS.DBMS_OUTPUT", line 110
    ORA-06512: at "SYS.DBMS_OUTPUT", line 81
    ORA-06512: at "ITN_NEW.P_1", line 15
    ORA-06512: at line 1
    thanks,
    harish

  5. #5
    Join Date
    Jan 2004
    Posts
    58

    Angry re: oracle 9i version i am running on

    Oracle9i Release 9.0.1.1.1 - Production
    PL/SQL Release 9.0.1.1.1 - Production
    CORE 9.0.1.1.1 Production
    TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
    NLSRTL Version 9.0.1.1.1 - Production
    thanks,
    harish

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Best post the actual procedure text, I think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jan 2004
    Posts
    58

    Unhappy heres the code

    create or replace procedure p_1 as
    v_cursor INTEGER;
    v_sqlstatement VARCHAR2(500);
    v_returncode INTEGER;
    BEGIN
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    v_sqlstatement := 'CREATE INDEX RD_LINK_GEO_IDX ON ITN_NEW.RD_LINK_FINAL(GEOLOC)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX';
    DBMS_SQL.PARSE(v_cursor,v_sqlstatement,DBMS_SQL.V7);
    v_returncode := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    commit;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(to_char(sqlcode)||substr(sqlerrm,1,100));
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END;
    /

    variable v_job1 number
    set serveroutput on
    begin
    dbms_job.submit(job => :v_job1, what => 'p_1;', next_date => sysdate);
    dbms_job.run(:v_job1);
    dbms_output.put_line('submitted as job number '|| to_char(:v_job1));
    end;
    /
    thanks,
    harish

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you tried running it as ...
    Code:
    BEGIN
    execute immediate
       'CREATE INDEX RD_LINK_GEO_IDX
                  ON ITN_NEW.RD_LINK_FINAL(GEOLOC)
                     INDEXTYPE IS MDSYS.SPATIAL_INDEX';
    END;
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jan 2004
    Posts
    58

    Unhappy re: when i try execute immediate

    i get the following error

    *
    ERROR at line 1:
    ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
    ORA-13249: internal error in Spatial index: [mdidxrbd]
    ORA-13249: Error in Spatial index: index build failed
    ORA-13249: Error in R-tree: [mdrcritbl]
    ORA-13231: failed to create index table [] during R-tree creation
    ORA-29400: data cartridge error
    ORA-01031: insufficient privileges
    ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 7
    ORA-06512: at line 1
    ORA-06512: at "ITN_NEW.P_2", line 3
    ORA-06512: at line 1
    thanks,
    harish

  10. #10
    Join Date
    Oct 2003
    Posts
    312
    that's what I have been telling you. you need to create Oracle spatial index first before using the package to submit the job.

    make sure you have the metadata for the object you create and validate the spatial index.

    any way, do you have any idea about Oracle Spatial ??? If not, I suggest that you need to do some research on SPATIAL.

    good luck

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