Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I insert into a table after an insert event?

Hello,
This is the trigger I am using that listens for an INSERT or UPDATE event from table CRPDTA.F03012 where it should insert the data in table CRPDTA.F_ASR_USERS the fields F_ASR_USERS.USER_ID(F03012.AIAN8)F_ASR_USERS.USER_IDENTITY(F0101.ABTAX)F_ASR_USERS.USER_EMAIL(F01151.EAEMAL) and F_ASR_USERS.USER_PASSWORD(F0101.ABTAX), this is my trigger:

create or replace TRIGGER CRPDTA.INSERTUSER 
AFTER insert or update  
ON CRPDTA.F03012 
FOR EACH ROW
DECLARE
 USER_ID NUMBER := :NEW.AIAN8;
 USER_IDENTITY VARCHAR2(100);
 USER_EMAIL VARCHAR2(100);
BEGIN
SELECT ABTAX INTO USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :USER_ID;
SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE ABAN8 = :USER_ID;
IF USER_IDENTITY IS NULL
  IF USER_EMAIL IS NULL
   UPDATE CRPDTA.F_ASR_USERS F_ASR_USERS SET F_ASR_USERS.USER_EMAIL = :USER_EMAIL 
   WHERE F_ASR_USERS.USER_IDENTITY = :USER_IDENTITY
  ELSE
   INSERT INTO CRPDTA.F_ASR_USERS (USER_ID, USER_IDENTITY,USER_EMAIL, USER_PASSWORD) 
   VALUES (:USER_ID, :USER_IDENTITY,:USER_EMAIL, CUSTOM_HASH(:USER_IDENTITY,256))
  END IF;
  COMMIT;
ELSE
 RAISE_APPLICATION_ERROR(-20000,'AN8 NOT EXIST ADDRESS BOOK MASTER')
END IF;
END;

The problem is that I have a bug, and I'm not sure if it works the way I put it, and I can't find in forums more or less the case I have.
And this is the error I have:
image.png
Oracle version: 19.0.0.0.0 - Production

I hope you can help me, thank you.

This post has been answered by Frank Kulash on Nov 25 2021
Jump to Answer

Comments

Mannamal-Oracle
Hi Wolfgang,

Are you looking to load data into 10g or 11g?

If you are looking at 11g, sample files are located at $ORACLE_HOME/md/demo/network/rdf_demos/ (as specified in the documentation). This location contains a ctl file and a sample sql file that uses that ctl file. You could modify the sample sql file (bulkload.sql) to load multiple files (please refer to the SQL*Loader documentation for more details).

Detailed instructions on the load process are in section 1.7 of the 11g Oracle Database Semantic Technologies documentation. The Oracle Technology Network includes links to the 11g documentation.

Melli
609276
Hi Melli,

Thanks for your answer. I am loading data into 11g.
I found the samples. I was also looking for an easy way for uploading all files within a directory, which I found out is not possible directly (i.e. it is not possible to specify a directory in the ctl file). So there are two possible solutions to this:
1.) Create a list of all files in the directory (on Windows with "dir/b *.nt > files.dat"), prefix every line with "INFILE", surround the filenames with a paragraph and include all that in the ctl file.
2.) It should also be possible to let a SQL-script create the list ("host dir/b *.nt > files.dat"), load this list into a temp-table and iteratively invoke the sqlldr.

For me solution 1 worked perfectly.

However when trying some sample queries (the LUBM test queries) I observed some very strange behaviour of SEM_MATCH (see thread "Missing triples in SEM_MATCH query")

Wolfgang
1 - 2

Post Details

Added on Nov 25 2021
3 comments
1,963 views