How to use sys.anydataset sql oracle 19c

user_12696852 0
Viewed 400
create table book (
    book_id number (3) NOT NULL,
    book_name    SYS.ANYDATASET NOT NULL,
    student_student_id number(5) NOT NULL);             #primary key

insert into book (book_id, book_name, student_student_id) values ('768', SYS.ANYDATAconvertVarchar2('harry potter'), '58765');

I am unsure how to use sys.anydataset. I tried using "));" at the end of 58765 but it shows an error "ORA-00933: SQL command not properly ended, and then decided to do a single bracket and it shows 'ORA-00932: inconsistent datatypes: expected ANYDATA got ANYDATA. If anyone can tell me what that means and how to resolve this.

oracle sql
1 Answers

Below is an example of creating and inserting an ANYDATASET. Unlike ANYDATA, creating an ANYDATASET cannot be done entirely in one SQL statements and must use PL/SQL.

This example is slightly more complicated than necessary because it demonstrates adding two elements to the ANYDATASET. Using multiple elements may not make sense in this example, but in general there's no point in using an ANYDATASET if you're not going to use multiple elements.

--Add one row with multiple values.
declare
    v_anytype    anytype;
    v_anydataset anydataset;
begin
    anydataset.BeginCreate(dbms_types.typecode_varchar2, v_anytype, v_anydataset);

    v_anydataset.addInstance;
    v_anydataset.setVarchar2('Harry Potter and the Philosopher''s Stone');

    v_anydataset.addInstance;
    v_anydataset.setVarchar2('Harry Potter and the Sorcerer''s Stone');

    anydataset.EndCreate(v_anydataset);

    insert into book (book_id, book_name, student_student_id) values ('768', v_anydataset, '58765');
    commit;
end;
/

Below is an example of reading and displaying the book names. Reading the values is just as complicated as writing them.

--Display all of the book names.
declare
    v_anydataset anydataset;
    v_type pls_integer;
    v_string varchar2(4000);
begin
    --Loop through the rows.
    for anydatasets in
    (
        select book_name
        from book
        order by book_id
    ) loop
        --Get the anydataset.
        v_anydataset := anydatasets.book_name;
        v_anydataset.piecewise;

        --Loop through all the elements in the anydataset.
        for i in 1 .. v_anydataset.getCount loop
            v_type := v_anydataset.getInstance;
            v_type := v_anydataset.getVarchar2(v_string);
            dbms_output.put_line(v_string);
        end loop;
    end loop;
end;
/

Here is a db<>fiddle that runs the above code.

You might want to reconsider using ANYDATASET. Relational databases are much easier to work with when you use simple, atomic values in each column. ANYDATASET is only practical in a small number of use cases and will confuse anyone that uses your database. I literally could not find a single example of storing ANYDATASET in a table.

user_4091720
Related