CODEWITHSUNDEEP
sqloracle-database
Stephan
Stephan

Reputation: 527

Parent Child insert statement in an one Oracle package

I'm completely new to Oracle but have got experience with MS SQL. In this case, I have to use Oracle, however.

I have a webpage where I can upload (simplified) a document and enter a title for the document. This information is stored in two tables (Document_meta and Document_content). For this, I used two insert statements

Parent-insert:

INSERT INTO DOCUMENT (TITLE) VALUES (:title) RETURNING DOCUMENTID INTO :documentId

Child-insert:

INSERT INTO CONTENT (SEQ_VSL_DOCUMENT,DOCUMENT) VALUES (:documentId,:blob)

for this to work I had to use a trigger in de DB, to retrieve the new Id of the inserted data in the document-meta table.

Trigger code:

create or replace TRIGGER DOC_INS 
BEFORE INSERT ON DOCUMENT_META 
FOR EACH ROW

BEGIN
  SELECT DOC_ID.nextval
  INTO   :NEW.DOCUMENTID
  FROM   dual;
END;

This works. But now I have to move this into a package. So I try to create a package but without any luck.

The package header has looks like:

create or replace PACKAGE PKG_DOCUMENT AS 
    PROCEDURE insert_document(p_title VARCHAR2,p_content BLOB);
END PKG_DOCUMENT;

and the body like:

create or replace PACKAGE body PKG_DOCUMENT AS 
    PROCEDURE insert_document(p_title VARCHAR2,p_content BLOB) AS
    BEGIN
        INSERT INTO document_meta(TITEL) VALUES (p_title);
        RETURNING DOCUMENTID INTO docId;
        
        INSERT INTO document_content(content,document_id) VALUES (p_content,docId);        
    END insert_document;    
END PKG_DOCUMENT;

But this won't compile, I get the error:

Error(5,19): PLS-00103: Encountered the symbol "DOCUMENTID" when expecting one of the following:     := . ( @ % ;

And I don't know how to solve this? Is it even possible to insert data like this, or do I have to use a function that inserts the data in the Parenttable and returns the new Id and after this inserting into the child-table?

Any help is appreciated. I use Oracle 11c Express.

Upvotes: 2

Views: 554

Answers (1)

Popeye
Popeye

Reputation: 35920

There are few issue in your package body which is listed inline in following code:

create or replace PACKAGE body PKG_DOCUMENT AS 
    PROCEDURE insert_document(p_title VARCHAR2,p_content BLOB) AS
    docid document_meta.DOCUMENTID%type; -- this must be declared
    BEGIN
        INSERT INTO document_meta(TITLE) VALUES (p_title) --; -- removed semicolon and spelling of TITLE was incorrect
        RETURNING DOCUMENTID INTO docId;

        INSERT INTO document_content(content,document_id) VALUES (p_content,docId);        
    END insert_document;    
END PKG_DOCUMENT;

Cheers!!

Upvotes: 1

Related Questions