re: creating a spatial index using dbms_job

[hreddy007]

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

[slimdave]

Does the server log file have any further information or error codes in it?

[learning_bee]

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.

[hreddy007]

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

[hreddy007]

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

[slimdave]

Best post the actual procedure text, I think.

[hreddy007]

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;
/

[slimdave]

Have you tried running it as ...

BEGIN
execute immediate
   'CREATE INDEX RD_LINK_GEO_IDX
              ON ITN_NEW.RD_LINK_FINAL(GEOLOC)
                 INDEXTYPE IS MDSYS.SPATIAL_INDEX';
END;
/

[hreddy007]

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

[learning_bee]

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