Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Array of unlimited values as input to stored procedure in oracle

    We have an array from java which contains numeric values.
    This is the input to the stored proc and these values may be of any number
    and we have to save these values in the table.

    Please suggest as we tried many methods but none worked fine. Please help. Thanks. It would be grateful if someone can give the example code...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please suggest as we tried many methods but none worked fine.

    Let me restate the problem in slightly different terms.

    My car won't go.
    I tried many fixes but it still won't go.
    Tell me how to make my car go.

    You pass an "array" from Java into PL/SQL.
    Which PL/SQL datatype is compatible with the Java array?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm Guessing you mean something like

    '1,2,3,4,5,6,7,8,9,10,11,12'

    Something like that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >'1,2,3,4,5,6,7,8,9,10,11,12'
    In Oracle characters between single quote marks are STRINGS
    'This is a string; 1,2,3,4,5,6,7,8,9,10,11,12 not a number or array'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2009
    Posts
    4
    It is an array of numbers.

    I am using it like...

    declare
    cate_id category_varray;

    --temp variables
    BEGIN
    cate_id := category_varray();
    cate_id.EXTEND(150);

    for i in cate_id.first..cate_id.last loop
    cate_id (i) := ('501','502');
    end loop;
    dbms_output.put_line('cate_id(1) is '||cate_id(1));
    dbms_output.put_line('cate_id(2) is '||cate_id(2));
    dbms_output.put_line('cate_id(3) is '||cate_id(3));


    But how to pass the array which has unlimited values lik
    cate_id(i) :=('501','502','503','504',.....)
    How to give this as input..?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2  cate_id category_varray;
      3  --temp variables
      4  BEGIN
      5  cate_id := category_varray();
      6  cate_id.EXTEND(150);
      7  for i in cate_id.first..cate_id.last loop
      8  cate_id (i) := ('501','502');
      9  end loop;
     10  dbms_output.put_line('cate_id(1) is '||cate_id(1));
     11  dbms_output.put_line('cate_id(2) is '||cate_id(2));
     12  dbms_output.put_line('cate_id(3) is '||cate_id(3));
     13* end;
     14  /
    cate_id category_varray;
            *
    ERROR at line 2:
    ORA-06550: line 2, column 9:
    PLS-00201: identifier 'CATEGORY_VARRAY' must be declared
    ORA-06550: line 2, column 9:
    PL/SQL: Item ignored
    ORA-06550: line 5, column 1:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 5, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 6, column 1:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 6, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 7, column 10:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 7, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 10, column 40:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 10, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 11, column 40:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 11, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 12, column 40:
    PLS-
    Please provide URL which documents "category_varray" as valid PL/SQL datatype.
    Please ONLY post code which is syntax correct!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2009
    Posts
    4
    Hi Sorry for the error..


    This was the VARRAY I used..

    CREATE OR REPLACE TYPE category_varray AS varray(150) OF VARCHAR2(50);

    And the exact code with syntax is:


    declare
    cate_id category_varray;

    --temp variables
    BEGIN
    cate_id := category_varray();
    cate_id.EXTEND(150);


    cate_id (1) := ('501');
    cate_id (2) := ('502');
    dbms_output.put_line('cate_id(1) is '||cate_id(1));
    dbms_output.put_line('cate_id(2) is '||cate_id(2));


    END TEST_ARRAY_CATG;



    I am not getting how to do for the unlimited values of array..

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am not getting how to do for the unlimited values of array..
    No array ever has unlimited values.
    An array in any language (Java, PL/SQL, etc) is a memory structure.
    The array sized is predefined & fixed at compile time.
    Therefore the size is never "unlimited".

    In order to effectively pass an array from 1 procedure to another procedure,
    the maximum size of the array being passed also needs to be passed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by anacedent View Post
    Please provide URL which documents "category_varray" as valid PL/SQL datatype.
    Please ONLY post code which is syntax correct!

    LOL

    you MUST be Kidding
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by sravanpvsr View Post
    It is an array of numbers.

    I am using it like...
    Tell him what he's won Johnny!!!!!!!!!


    Hmmmm...so Oracle has Array's in PL/SQL?

    Very nice

    What about User defined functions?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >We have an array from java which contains numeric values.
    Using JDBC you can directly interact with Oracle DB from Java procedure.
    There is no need for any PL/SQL procedure.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [Lion King]
    Look Closer
    [/Lion King]

    That's not what they meant
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the array is in Java, why don't you simply loop in Java, and insert each row into the target table? Or, if you really need to cut down on the number of calls, send a set maximum number, and (again) looping in Java, send a set amount each time, until you run out of Java array?

Tags for this Thread

Posting Permissions

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