Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: How to insert LINEs into Oracle sptial database

    Hi, ALL

    I've got milions of line objects to be inserted into a Oracle sptial table.

    I use the following code to do the work. I am using Oracle 10g

    Code:
    declare geom  SDO_GEOMETRY;
    begin geom:=SDO_GEOMETRY(2002, 8307, MDSYS_ELEM_INFO_ARRAY(1,2,1), MDSYS_SDO_ORDINATE_ARRAY(%s);
    EXECUTE IMMIDIATE 'insert in %s values(%s, null, null,%d, :1, null)'USING geom;
    END;
    It works fine for hours. Then, the program got exception error. I noticed from the Windows Task Management that the amount of memory consumed by the oracle process increased as the program running.

    I do not know why. I suspect that 'declare geom SDO_GEOMETRY' which actually is in a for loop cause too many varaible geom are declared consuming too many memory. But, I am not sure.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    Quote Originally Posted by cy163 View Post
    Then, the program got exception error.
    My car is broken. How do I fix it?
    Please provide the error information too...

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Code:
    begin
    insert into %s values(%s, null, null,%d, SDO_GEOMETRY(2002, 8307, MDSYS_ELEM_INFO_ARRAY(1,2,1), MDSYS_SDO_ORDINATE_ARRAY(%s)), null);
    END;
    But still, I would think you are running this code block dynamically and injecting an ordinate array into %s which is exceeding the SQL statement length limits. The real solution is to use bind variable correctly from the calling code.

  4. #4
    Join Date
    Apr 2007
    Posts
    130
    dayneo, Thank you your reply.

    Actually, there was no error message. The program just looks like it is hung, did not response.


    Initially, I use the following code to do the work

    Code:
    INSERT INTO sales_regions
    
    VALUES
    
     (1000,
    
       sdo_geometry(2004,
    
                    8307,
    
                    NULL,
    
                    sdo_elem_info_array(1, 1, 1100),--over 1100 points
    
                    sdo_ordinate_array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.. . 
                                       1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
    
                                       )));
    It cause error "too many arguments for function"

    In this case, I turn to way of using 'declare geom sdo_geometry....'

    I guess there are too many variable 'geom' is declared, causing excessively memory consuming. I wonder if there is a command 'undeclare' to release the variables.

Posting Permissions

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