asked on

Oracle function to insert records?

I need a skeleton Oracle function that does the following:

1. Enter record into table1
2. Get ID of entered record
3. Enter 5 records into table2 using the ID from the first table and 5 passed parameters.

I can't figure out how to do this. Table1 uses a sequence to get the next val and uses an insert trigger to set the ID.

Update...
Now, looking at our process, it's going to be a little more involved than that. The system is an MVC application and I want to make this process within a transaction in a single Oracle function.

It is currently done like this:
1. Find record in Table2 using 3 passed parameters. The find SQL uses an intersect to find the ID.
2. Delete all records in Table2 and Table1 with that ID
3. Insert record into Table1 using one of the passed parameters. This returns the ID
4. Insert 5 records into Table2 using the ID and 4 passed parameters, one extra record is inserted with the SYSDATE.

Is it possible to do this in a single Oracle function?

Here is the code to the Oracle function that is used to insert the record into Table1:

CREATE OR REPLACE FUNCTION CIC3.F_INSFILEDIRECTORY(pfd_filename in varchar2,vfd_lastmodified in date)     
  RETURN  number IS
   vfd_filename       varchar2(1000);
   pfd_rid            number(9);
   veve_rid           number(9);
   veve_event_code    varchar2(100);
BEGIN
   vfd_filename := upper(pfd_filename);
   veve_event_code := substr(vfd_filename,instr(vfd_filename,'\',-1),100);
   veve_event_code := substr(veve_event_code,2,instr(veve_event_code,'.',-1)-2);
   select max(eve_rid) into veve_rid from event where eve_event_code=veve_event_code;
   select /*+index(filedirectory filedirattrib_filemane)*/  max(fd_rid) into pfd_rid from filedirectory where fd_filename = vfd_filename;
   if pfd_rid is not null then
      update filedirectory set fd_lastmodified = nvl(vfd_lastmodified,sysdate),
      fd_eve_rid = veve_rid, fd_old = 'N' where fd_rid = pfd_rid;
   else
      select seq_fd_rid.nextval into pfd_rid from dual;
      insert into filedirectory (fd_rid, fd_filename, fd_lastmodified, fd_eve_rid, fd_old)
         values (pfd_rid, vfd_filename, nvl(vfd_lastmodified,sysdate), veve_rid, 'N');
   end if;
   return pfd_rid;
END F_INSFILEDIRECTORY;

Open in new window

This would be code used in my application (using PetaPoco ORM)

private Int32 SqlInsertArchive(string filename)
{
    var pfd_rid = new OracleParameter
    {
        ParameterName = "pfd_rid",
        OracleDbType = OracleDbType.Int32,
        Direction = System.Data.ParameterDirection.InputOutput
    };
    var sql = string.Concat(@"DECLARE pfd_rid NUMBER; BEGIN @0 := CIC3.F_INSFILEDIRECTORY('", filename, "', sysdate); END;");
    var res = _db.db.Execute(sql, pfd_rid);
    return Convert.ToInt32(pfd_rid.Value.ToString());
}

Open in new window

It returns the last inserted ID, which is then used to insert the other records using this function in my application:

private void SqlInsertArchiveKeyValue(int Id, string keyword, string value)
{
    var sql = String.Concat("INSERT INTO CIC3.FILEKEYPAIR (FKP_FD_RID, FKP_KEYWORD, FKP_VALUE) VALUES (", Id, ",'", keyword, "','", value, "')");
    var res = _db.db.Execute(sql);
    if(res == 0) {
        Exception e = new Exception();
        throw e;
    }
}

Open in new window

The Id parameter is the ID returned from the SqlInsertArchive function, the keyword and value parameters vary and we send up to 6 so this function would be called 6 times.

Peter Chan

Hi,
Insert/Delete is disallowed to have, within an Function. You should use one Stored proc to do the same.

Alex [***Alex140181***]

@HuaMinChen: your comment doesn't make any sense! Please clarify!

slightwv (䄆 Netminder)

What HuaMinChen was trying to say is that by default DML is not allowed inside a function in a query.

If you try you will get:
ORA-14551: cannot perform a DML operation inside a query

There is a way around this but you need to understand what you are doing before using it so I'll not post it here since it really isn't necessary.

The way it is being called is inside a PL/SQL block so it should be fine.

@EddieShipman,

What is the problem you are seeing?

Alex [***Alex140181***]

DML is not allowed inside a function in a query

Am I blind?! Where exactly in this case?

slightwv (䄆 Netminder)

>>Am I blind?! Where exactly in this case?

I was clarifying what HuaMinChen was likely referring to.

It isn't the case here and I clarified that in my post:
The way it is being called is inside a PL/SQL block so it should be fine.

Geert G

wow, that doesn't look like Delphi anymore :)

use a sequence.next_val as default value as of oracle 12
or use a trigger to set the id to the value of the sequence

below insert statement returns the inserted id

insert into yourtable (col1, col2) values (:val1, :val2) returning id into :id

Open in new window

why would insert/update/delete be disallowed in a function ?

and yes it's all possible, giving you provide all the necessary parameters at once too

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I did say "in a query" and inside the pl/sql block it was OK?

johnsone

I saw that, but there were a couple of references after what you said that were questioning that. So, I was just showing by example. I know that the people thread are knowledgeable enough to prove it themselves, so sorry if I offended anyone. Just been running into more and more people that you need to show examples to prove things. If you don't prove it, they just keep asking the same question over and over.

slightwv (䄆 Netminder)

No problem ;)

Eddie Shipman

ASKER

Below is final working function. Do you guys see anything that may cause issues?

Eddie Shipman

ASKER

Note: CIC3.PP_RMV_ARCHIVE_ID_PRC function deletes the records from both tables if they exist.

CREATE OR REPLACE FUNCTION CIC3.F_LMSINSARCHIVE(p_doc_name varchar2, p_event_code varchar2, p_cli_rid number, pfd_filename in varchar2)
    RETURN  number IS
    vfd_filename       varchar2(1000);
    pfd_rid            number(9);
    veve_rid           number(9);
    veve_event_code    varchar2(100);
BEGIN
    -- Find the old entry
    vfd_filename := upper(pfd_filename);
    veve_event_code := substr(vfd_filename,instr(vfd_filename,'\',-1),100);
    veve_event_code := substr(veve_event_code,2,instr(veve_event_code,'.',-1)-2);
    select max(eve_rid) into veve_rid from event where eve_event_code=veve_event_code;
    select /*+index(filedirectory filedirattrib_filemane)*/  max(fd_rid) into pfd_rid from filedirectory where fd_filename = vfd_filename;
    -- And delete it from filedirectory and filekeypair
    if pfd_rid is not null then
        CIC3.PP_RMV_ARCHIVE_ID_PRC(pfd_rid);
    end if;
    -- Now insert into filedirectory
    select seq_fd_rid.nextval into pfd_rid from dual;
    insert into filedirectory (fd_rid, fd_filename, fd_lastmodified, fd_eve_rid, fd_old)
        values (pfd_rid, vfd_filename, sysdate, veve_rid, 'N');
    -- Now insert into filekeypair
        -- DOC_NAME
    insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) 
        values (pfd_rid, 'DOC_NAME', p_doc_name);
        -- EVENT_CODE
    insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) 
        values (pfd_rid, 'EVENT_CODE', p_event_code);
        -- CLI_RID
    insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) 
        values (pfd_rid, 'CLI_RID', p_cli_rid);
        -- FILENAME
    insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) 
        values (pfd_rid, 'FILENAME', 'Q:' || pfd_filename);
        -- PRINT_DATE
    insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) 
        values (pfd_rid, 'PRINT_DATE', to_char(to_date(sysdate), 'YYYYMMDD'));
    commit;
    return pfd_rid;
END F_LMSINSARCHIVE;

Open in new window

Geert G

Dont put the commit inside the function.
Commit after the function

slightwv (䄆 Netminder)

>> Do you guys see anything that may cause issues?

Possibly. You select the MAX value into pfd_rid and delete it if it exists then reassign the value to seq_fd_rid.nextval.

Eddie Shipman

ASKER

@Geert, yep, missed that one..

@Netminder, That is correct, getting the max assures me that I remove the latest entry with that filename then the nextval is used on the insert of the new version.