Home » SQL & PL/SQL » SQL & PL/SQL » Beginner question on Objects (Oracle 10g R2, Windows Server 2003)
Beginner question on Objects [message #422498] Wed, 16 September 2009 20:47 Go to next message
antiemotion
Messages: 1
Registered: September 2009
Junior Member
Hello all, I've only recently started getting into Oracle and have seemed to have stumped myself. I have been trying to create a system for a fiscal application that would keep track of a series of invoices attached to a variable number of ledgers, each of which belongs to a fiscal year. I had planned to have the structure of a table of Fiscal Year Objects, that hold a year and some other attributes, one of which was a varray of Ledger Objects. Each Ledger Object had a ledger id and other attributes, in addition to a varray of Invoice Objects.

Having set all of this up, I realized that I would at some point want to add an Invoice Object to the varray of an existing Ledger. My quick googling landed me on the article "Working with VARRAYs in Oracle Part I By James Koopmann" in DatabaseJournal which was very helpful in showing me how to do just that in a procedure. Only problem is, I wish my code to do this one "level" down, on a varray inside a varray. Seemed like it shouldn't be too hard, however I run into problems when trying to perform the actual UPDATE of the attribute holding the Invoice Object varray. Below is the entire chunk of code that I have created so far, which I run though the iSQL*Plus web interface. When run, it gives me some strange Oracle error, then actually disconnects my iSQL*Plus session (without logging me out of the web tool). Strange.

Code:
-- CLEANUP: Clear out old stuff
DROP TYPE invoice_list_varray FORCE;
DROP TYPE invoice_typ FORCE;
DROP TYPE ledger_typ FORCE;
DROP TYPE ledgers_varray FORCE;
DROP TABLE fiscalyears;
DROP PROCEDURE insert_invoice;

-- Set up the Date format
ALTER SESSION SET nls_date_format='MM-DD-YYYY';


-- SETUP
-- Create the Invoice Type Object
CREATE OR REPLACE TYPE invoice_typ AS OBJECT (
  invoice_id    INTEGER,
  total_price     INTEGER );
/
SHOW ERRORS;

-- Create the VARRAY of Invoice types for each Req Object
CREATE OR REPLACE TYPE invoice_list_varray AS VARRAY(100) OF invoice_typ;
/
SHOW ERRORS;

-- Create the Ledger Type Object
CREATE OR REPLACE TYPE ledger_typ AS OBJECT (
  ledger_id    INTEGER,
  invoices    invoice_list_varray,
  MEMBER FUNCTION invoices_total RETURN INTEGER  );
/
SHOW ERRORS;

-- Create the functions for a Ledger Type Object
CREATE OR REPLACE TYPE BODY ledger_typ AS
  MEMBER FUNCTION invoices_total RETURN INTEGER IS
  total FLOAT;
  BEGIN
    total := 0;
    FOR inv IN invoices.first..invoices.last LOOP
      total := total + invoices(inv).total_price;
    END LOOP;
    RETURN total;
  END;
END;
/
SHOW ERRORS;

-- Create the VARRAY of Ledger types for each Req Object
CREATE OR REPLACE TYPE ledgers_varray AS VARRAY(100) OF ledger_typ;
/
SHOW ERRORS;

-- Create the Fiscal Year Type Object
CREATE OR REPLACE TYPE fiscalyear_typ AS OBJECT (
  fiscal_year    INTEGER,
  ledgers    ledgers_varray );
/
SHOW ERRORS;

-- Create a table to store Fiscal Year Type Objects
CREATE TABLE fiscalyears (
  year    NUMBER,
  object     fiscalyear_typ);

-- Create a Procedure to add a new Invoice to an existing Ledger
CREATE OR REPLACE PROCEDURE insert_invoice 
( fiscalyear     IN NUMBER,
  ledger_id     IN INTEGER,
  invoice_id     IN INTEGER,
  total_price    IN INTEGER) AS
pr_invoice_list_varray     invoice_list_varray := invoice_list_varray();
BEGIN 
EXECUTE IMMEDIATE 
'SELECT invoices FROM TABLE(SELECT fy.object.ledgers FROM fiscalyears fy WHERE year = :1) l WHERE l.ledger_id = :2 FOR UPDATE OF invoices' 
   INTO pr_invoice_list_varray USING fiscalyear, ledger_id;
pr_invoice_list_varray.EXTEND;
pr_invoice_list_varray(pr_invoice_list_varray.LAST) := invoice_typ(invoice_id,total_price);
EXECUTE IMMEDIATE 
'UPDATE TABLE(SELECT l.invoices FROM TABLE(SELECT fy.object.ledgers FROM fiscalyears fy WHERE year = :1) l WHERE l.ledger_id = :2) i SET VALUE(i) = :3' 
 USING fiscalyear, ledger_id, pr_invoice_list_varray;
END insert_invoice;
/
SHOW ERRORS;
COMMIT;


-- TESTING
-- Insert some test data for FY10
INSERT INTO fiscalyears VALUES (
  2010,
  fiscalyear_typ('2010',
                 ledgers_varray(
                                ledger_typ(
                                           '1',
                                           invoice_list_varray(
                                                               invoice_typ('1','1'),
                                                               invoice_typ('2','5'),
                                                               invoice_typ('3','10')
                                                              )
                                          ),
                                ledger_typ(
                                           '2',
                                           invoice_list_varray(
                                                               invoice_typ('1','1'),
                                                               invoice_typ('2','5'),
                                                               invoice_typ('3','10')
                                                              )
                                          )
                               )
                )
  );
execute insert_invoice ('2010','1','4','20');
SHOW ERRORS;
COMMIT;
SELECT l.invoices_total() FROM fiscalyears fy, TABLE(fy.object.ledgers) l WHERE fy.year = 2010 and l.ledger_id = 1;


Results:
Type dropped.

Type dropped.

Type dropped.

Type dropped.

Table dropped.

Procedure dropped.

Session altered.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type body created.

No errors.

Type created.

No errors.

Type created.

No errors.

Table created.

Procedure created.

No errors.

Commit complete.

1 row created.

BEGIN insert_invoice ('2010','1','4','20'); END;

            *

ERROR at line 1:
ORA-03113: end-of-file on communication channel
No errors.

ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE 


I discovered that when I try and manually run a similar query for only changing the contents of the invoices varray directly, I get this error.
Query:
UPDATE TABLE(SELECT l.invoices FROM TABLE(SELECT fy.object.ledgers FROM fiscalyears fy WHERE year = 2010) l WHERE l.ledger_id = 1) i SET VALUE(i) = NULL

Error:
ERROR at line 1:
ORA-25015: cannot perform DML on this nested table view column 


Any thoughts on what I might do to properly construct the query I need to do what I'm looking to do? I can't seem to find many examples of people using a structure quite like this in my internet searchings. Any help is much much appreciated.
Re: Beginner question on Objects [message #422507 is a reply to message #422498] Wed, 16 September 2009 22:50 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
My experience with

ORA-03113: end-of-file on communication channel


is that there is something in your code that is causing the error. This has manifested for me in two ways:

1) you are using a feature incorrectly
2) you are using a feature correctly but there is a bug


Either way the fix is the same, change your code, or file a TAR and wait for a patch.

As a commentary, you said:

Quote:
I can't seem to find many examples of people using a structure quite like this in my internet searchings

That should clue you to something, mainly that most people use objects for coding purposes but few people actually store data in an oracle table as objects.

Indeed, consult asktomhome and Tom Kyte (An oracle VP and Oracle expert) will tell you that he almost never uses objects as a storage mechanism. He prefers to store his data in relational tables and use objects as a coding feature in certain situations. This object approach of not using objects in tables but using them in code is a common sentiment among those of us who have used objects in the past.

I would ask you why you feel you need to use objects this way. Why not just use normal relational tables. Then if you have to have an object interface, create it with views and instead-of-triggers.

Good luck, Kevin
Previous Topic: Constraints
Next Topic: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason
Goto Forum:
  


Current Time: Fri Dec 09 08:10:56 CST 2016

Total time taken to generate the page: 0.11099 seconds