How to deal with sequence in insert from XMLTable?

Question

I have write a PL/SQL function that takes input in XML format for the following table:

TABLE: TBL_MEDICAL_CENTER_BILLS

Name          Null     Type          
------------- -------- ------------- 
MED_RECORDNO  NOT NULL NUMBER       
MED_EMPID              NVARCHAR2(10) 
MED_BILL_HEAD          NVARCHAR2(20) 
MED_DATE               DATE          
MED_AMOUNT             FLOAT(126)   

Here is the function code:

FUNCTION save_medical_center_bills(medical_bill_data NVARCHAR2 ) RETURN clob IS ret clob;
   xmlData XMLType;

   v_code  NUMBER;
   v_errm  VARCHAR2(100);

   BEGIN 
   xmlData:=XMLType(medical_bill_data);
   INSERT INTO TBL_MEDICAL_CENTER_BILLS SELECT x.* FROM XMLTABLE('/medical_center_bill'
                                                PASSING xmlData

                                                COLUMNS  MED_RECORDNO NUMBER  PATH 'MED_RECORDNO' default null,
                                                         MED_EMPID    NVARCHAR2(11)     PATH   'employee_id',
                                                         MED_BILL_HEAD  NVARCHAR2(20)     PATH   'bill_head' ,
                                                         MED_DATE DATE  PATH  'effective_date',
                                                         MED_AMOUNT    FLOAT       PATH    'bill_amount'
                                                        ) x;


     ret:=to_char(sql%rowcount);
COMMIT;

RETURN '<result><status affectedRow='||ret||'>success</status></result>';
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm);
-- '<result><status>Error</status> <error_message>'|| 'Error Code:' || v_code || ' ' || 'Error Message:' || v_errm ||'</error_message> </result>';
RETURN '<result><status>Error</status> <error_message>'|| 'Error Message:' || v_errm ||'</error_message> </result>';

END save_medical_center_bills;

However, I want to keep table's first column MED_RECORDNO as incrementing sequence (at the moment I am keeping it null since I don't know how to put the sequence in the XMLTable clause) and the rest of the inputs [MED_EMPID, MED_BILL_HEAD , MED_DATE , MED_AMOUNT] will be taken from the XML passed to the function.

I created a sequence and a trigger to keep this sequence incremented for that table column MED_RECORDNO:

CREATE SEQUENCE MED_RECORDNO_SEQ;

create or replace TRIGGER MED_RECORDNO_TRIGGER 
BEFORE INSERT ON TBL_MEDICAL_CENTER_BILLS  FOR EACH ROW
WHEN (new.MED_RECORDNO is null)
DECLARE
  v_id TBL_MEDICAL_CENTER_BILLS.MED_RECORDNO%TYPE;
BEGIN
SELECT  MED_RECORDNO_seq.nextval INTO v_id FROM DUAL;
 :new.MED_RECORDNO  := v_id;

END;

As you can see, my XMLTable is inserting 4 column values in a 5 column table, because columns MED_RECORDNO will take its value from sequence MED_RECORDNO_SEQ using TRIGGER MED_RECORDNO_TRIGGER.

I don't know any thing about doing this. If you have ever experience such things, then please share your idea.

Answer 1

I sort of hinted at this in an earlier answer. You should specify the names of of the columns in the table you are inserting into; this is good practice even if you are populating all of them, as it will avoid surprises if the table structure changes (or differs between environments), and makes it much easier to spot like having columns or values in the wrong order.

   INSERT INTO TBL_MEDICAL_CENTER_BILLS (MED_EMPID, MED_BILL_HEAD, MED_DATE, MED_AMOUNT)
   SELECT x.MED_EMPID, x.MED_BILL_HEAD, x.MED_DATE, x.MED_AMOUNT
   FROM XMLTABLE('/medical_center_bill'
      PASSING xmlData
      COLUMNS  MED_EMPID    NVARCHAR2(11)     PATH   'employee_id',
               MED_BILL_HEAD  NVARCHAR2(20)     PATH   'bill_head' ,
               MED_DATE DATE  PATH  'effective_date',
               MED_AMOUNT    FLOAT       PATH    'bill_amount'
              ) x;

The insert you have should actually work (if the column order in the table matches); the trigger will still replace the null value you get from the XMLTable with the sequence value. At least, until you make the MED_RECORDNO column not-null, and you probably want to if it's the primary key.

---

Incidentally, if you're on 11g or higher your trigger can assign the sequence straight to the NEW pseudorecord:

create or replace TRIGGER MED_RECORDNO_TRIGGER 
BEFORE INSERT ON TBL_MEDICAL_CENTER_BILLS
FOR EACH ROW
BEGIN
   :new.MED_RECORDNO  := MED_RECORDNO_seq.nextval;
END;

The when null check implies you sometimes want to allow a value to be specified; that is a bad idea as manually inserted values can clash with sequence values, either giving you duplicates or a unique/primary key exception.