Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Question Unanswered: create type of PL/SQL table

    I want to create a type of Oracle pl/sql table so I can use is as a column type. I don't think this is actually possible but just wanted to ask incase I'm worng. DB is 9i

    I can declare it as a variable and use that variable:
    type vc_arr is table of varchar2(32767) index by binary_integer;

    and I can created an array type:
    CREATE OR REPLACE TYPE V_ARRAY_TYPE AS VARRAY(500) OF VARCHAR2(32000)

    but I want to create a type of table of varchar2(32767) index by binary_integer

    any ideas
    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do this:

    Code:
    create or replace type vc_arr is table of varchar2(32767);
    /
    However, you will not be allowed to create a column based on that type unless you reduce the size of the varchar2 to 4000 - the limit for varchar2 columns:
    Code:
    SQL> create table tt (c vc_arr) nested table c store as cc;
    create table tt (c vc_arr) nested table c store as cc
    *
    ERROR at line 1:
    ORA-02320: failure in creating storage table for nested table column C
    ORA-00910: specified length too long for its datatype
    
    
    SQL> create or replace type vc_arr is table of varchar2(4000);
      2  /
    
    Type created.
    
    SQL> create table tt (c vc_arr) nested table c store as cc;
    
    Table created.

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    great cheers. That worked for what I want but I now need an array of an object type for something else!!

    Code:
    CREATE OR REPLACE TYPE LLEXTRA AS OBJECT (
    PLLEXTRAID VARCHAR2(20),
    PLLEXTRATITLE VARCHAR2(25),
    PLLEXTRAINITIALS VARCHAR2(25),
    PLLEXTRAFNAME VARCHAR2(25),
    PLLEXTRALNAME VARCHAR2(25),
    PLLEXTRASALUTATION VARCHAR2(100),
    PLLEXTRAAGREEMENTNAME VARCHAR2(100),
    PLLEXTRAAGREEMENTSAL VARCHAR2(100),
    PLLEXTRACOMPANY VARCHAR2(50),
    PLLEXTRAADDR1 VARCHAR2(50),
    PLLEXTRAADDR2 VARCHAR2(50),
    PLLEXTRAADDR3 VARCHAR2(50),
    PLLEXTRATOWN VARCHAR2(50),
    PLLEXTRACOUNTY VARCHAR2(30),
    PLLEXTRAPOSTCODE VARCHAR2(8)
     )
    /
    
    
    CREATE OR REPLACE TYPE llextra_array AS TABLE OF LLEXTRA
    /
    
    
    CREATE TABLE test1 (
    llext llextra_array
    )
    nested table llext store as llextra_nest
    /
    
    
    DECLARE
    	v1 llextra_array := llextra_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    BEGIN
    
    	v1.EXTEND(5);
    
    	FOR i IN 1 .. 5 LOOP
    		v1(i) := llextra(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    	END LOOP;
    
    	FOR i IN 1 .. 5 LOOP
    		v1(i).PLLEXTRAID		     := 'a1';
    		v1(i).PLLEXTRATITLE          := 'a2';
    		v1(i).PLLEXTRAINITIALS       := 'a3';
    		v1(i).PLLEXTRAFNAME          := 'a4';
    		v1(i).PLLEXTRALNAME          := 'a5';
    		v1(i).PLLEXTRASALUTATION     := 'a6';
    		v1(i).PLLEXTRAAGREEMENTNAME  := 'a7';
    		v1(i).PLLEXTRAAGREEMENTSAL   := 'a8';
    		v1(i).PLLEXTRACOMPANY        := 'a9';
    		v1(i).PLLEXTRAADDR1          := 'a0';
    		v1(i).PLLEXTRAADDR2          := 'a-';
    		v1(i).PLLEXTRAADDR3          := 'a=';
    		v1(i).PLLEXTRATOWN           := 'a,';
    		v1(i).PLLEXTRACOUNTY         := 'a.';
    		v1(i).PLLEXTRAPOSTCODE       := 'a/';
    	END LOOP;
    
    dbms_output.put_line(v1(5).PLLEXTRAADDR3);
    
    	INSERT INTO robert1 (llext) VALUES (v1);
    	COMMIT;  
    
    END;
    /
    This gives oracle error ORA-22805 cannot insert NULL object into object tables or nested tables

    Now my object is not null as proved by the dbms_output so I'm not sure what the problem is

    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Complicated buggers, these objects, aren't they? I hate them!

    Your problem occurs because the nested table v1 has 6 elements, of which the last is null. This is why there are 6 elements, not 5 as you think:
    1) The v1 declaration initializes the collection with one element:
    Code:
    v1 llextra_array := llextra_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,N  ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    2) You then extend it by 5 elements, making a total of 6.

    However, you only populate 5.

    You could change the extend argument to 4, or perhaps better change the declaration to:
    Code:
    v1 llextra_array := llextra_array();
    ... which creates an empty collection.

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    ahh, obvious really! Thanks for that )
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    now I have another wierd problem!!

    I have a type
    CREATE TYPE varchar2_4000_array AS TABLE OF VARCHAR2(4000)

    I have a variable
    vNoteTextArr varchar2_4000_array := varchar2_4000_array();

    and I have a table with a column of that type called PNOTETEXT

    I select the collection into my variable
    SELECT PNOTETEXT INTO vNoteTextArr FROM test

    I then set some of the elements in the collection
    vNoteTextArr(14) := '12345';

    I then set the field in the table to the collection variable
    UPDATE test SET PNOTETEXT = vNoteTextArr

    when I do this all my elements in the collection move down one, ie element no 1 is now in element no 2.

    if I print out all the elements in my collection variable they are correct but when I update the table and select the column they are wrong!

    however, if I set the column to null before setting it to my variable it works fine!!
    UPDATE test SET PNOTETEXT = null
    UPDATE test SET PNOTETEXT = vNoteTextArr

    any ideas why

    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    btw, this doesn't happen every time I update the array so it *may* be caused by some data I am putting in the array if that is possible!
    There are 10 types of people in the world, those that know Binary and those that don't.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sounds like it could be a bug!

    Can you post a simple working example so I and others can try it?
    Last edited by andrewst; 01-14-05 at 09:21.

  9. #9
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    simple example!!

    ok, here is the code to create a type, a table, a sequence for the id and then some pl/sql to populate it the table and print the variables. It is looking like a bug and I think I'll create a TAR with Oracle.

    One point to note is that this work fine for the first record you put in the table, there is only an issue with the second and subsequent records!!!

    Code:
    CREATE TYPE varchar2_4000_array AS TABLE OF VARCHAR2(4000)
    /
    
    DROP TABLE test_tab
    /
    
    CREATE TABLE test_tab (
    id NUMBER,
    PNOTETEXT VARCHAR2_4000_ARRAY
    )
    nested table PNOTETEXT store as PNOTETEXT_NEST
    /
    
    CREATE INDEX i_test_tab_pk ON test_tab (id)
    /
    
    CREATE SEQUENCE TEST_TAB_SEQ MINVALUE 1 MAXVALUE 9999999 START WITH 1 INCREMENT BY 1 NOCACHE
    /
    
    
    
    
    DECLARE
    	vCollection	varchar2_4000_array := varchar2_4000_array();
          vID		NUMBER;
    BEGIN
    -- get a new id
    	SELECT TEST_TAB_SEQ.NEXTVAL INTO vID FROM dual;
    
    -- insert a blank record
    	INSERT INTO test_tab (id) VALUES (vID);
    
    -- initialize the collection variable with 3 elements
    	vCollection.EXTEND(3);
    
    -- set the first and second element of collection variable
    	vCollection(1) := '1';
    	vCollection(2) := '2';
    
    -- update the collection field of the record we created
    	UPDATE test_tab SET PNOTETEXT = vCollection WHERE id = vID;
    	COMMIT;
    
    -- delete all the elements from the collection variable
    	vCollection.delete;
    
    -- select the collection field into the collection variable
    	SELECT pnotetext INTO vCollection FROM test_tab WHERE id = vID;
    
    -- set the third element of collection variable
    	vCollection(3) := '3';
    
    -- loop round all the collection variable elements and print them out
          FOR q IN 1 .. vCollection.count LOOP
                dbms_output.put_line(q||' - var : '||vCollection(q));
          END LOOP;
    
    -- if you set the collection field to null in the table
    -- for the record you are looking at it all works fine!!!!
    --	UPDATE test_tab SET PNOTETEXT = NULL WHERE id = vID;
    
    -- update the collection field of the record we created
    	UPDATE test_tab SET PNOTETEXT = vCollection WHERE id = vID;
    	COMMIT;
    
    -- delete all the elements from the collection variable
    	vCollection.delete;
    
    -- select the collection field into the collection variable
    	SELECT pnotetext INTO vCollection FROM test_tab WHERE id = vID;
    
    -- loop round all the collection variable elements and print them out
          FOR q IN 1 .. vCollection.count LOOP
                dbms_output.put_line(q||' - tab : '||vCollection(q));
          END LOOP;
    
    END;
    There are 10 types of people in the world, those that know Binary and those that don't.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    When I run your script on 9.2.0.4.0 I get this output...
    Code:
    1 - var : 1
    2 - var : 2
    3 - var : 3
    1 - tab : 1
    2 - tab : 2
    3 - tab : 3
    
    PL/SQL procedure successfully completed.
    ... which I guess is what you would expect, but is different to what you are getting? In which case, it must be a bug in your version of Oracle!

  11. #11
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    we are on 9.2.0.1.0 so perhaps there was a bug!

    the first time I run it I get
    1 - var : 1
    2 - var : 2
    3 - var : 3
    1 - tab : 1
    2 - tab : 2
    3 - tab : 3

    the second time I run it I get
    1 - var : 1
    2 - var : 2
    3 - var : 3
    1 - tab : 2
    2 - tab : 3
    3 - tab : 1
    There are 10 types of people in the world, those that know Binary and those that don't.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh, so do I - I missed the part about running it twice!
    Code:
    1 - var : 1
    2 - var : 2
    3 - var : 3
    1 - tab : 1
    2 - tab : 2
    3 - tab : 3
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    1 - var : 1
    2 - var : 2
    3 - var : 3
    1 - tab : 2
    2 - tab : 3
    3 - tab : 1
    
    PL/SQL procedure successfully completed.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Mind you, this is a nested table, not a VARRAY. Oracle isn't obliged to return table data in a specific order without use of an ORDER BY clause! You can always get them back in the right order like this:
    Code:
    SQL> select column_value
      2  from test_tab, table(test_tab.pnotetext)
      3  where id=2
      4  order by column_value;
    
    COLUMN_VAL
    ----------
    1
    2
    3
    So maybe it isn't a bug - but you may want to see what Oracle have to say about that!

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In fact, I'm certain it isn't a bug - just expected behaviour! You can fix your script by changing the SELECTs to this form:
    Code:
    SELECT CAST(MULTISET(SELECT column_value 
                         FROM TABLE(pnotetext)
                         ORDER BY column_value)
                AS varchar2_4000_array)
    INTO   vCollection
    FROM   test_tab
    WHERE  id = vID;
    (IMHO, nested tables stored in the database are an abomination, and should never be used! You've seen the pain - now where is the gain?)

  15. #15
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    cheers for that. The problem is though the data won't be entered in alphabetical order.

    I'll try and explain briefly! I am importing data and if various fields are populated in the data supplied then I store them in notes so there could be any number of notes and each note has a subject so I have two columns in a temporary table which has hundreds of other fields.

    I could just have a child table for the notes but that would make it harder to view the data and harder to import as I have a procedure that I just loop round all the data in the temp table and fire the cursor at the procedure and the parameter for the notes is the same type as the column. If I was to have a child table I'd have to put the data into an array before I can fire it at the procedure, not a major issue I know but easier if this would work!
    There are 10 types of people in the world, those that know Binary and those that don't.

Posting Permissions

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