Jump to content

oracbeg

Members
  • Content count

    7
  • Joined

  • Last visited

Community Reputation

0 Neutral

About oracbeg

  • Rank
    Newbie
  • Birthday 03/28/1992

Profile Information

  • Gender
    Male
  • Location
    india
  1. Private Messaging Members Search Help Control Panel Logout [ oracbeg ] Home Home » SQL & PL/SQL » SQL & PL/SQL » Recording Update through trigger based on csv file upload Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator E-mail to friend | Unsubscribe from topic | Bookmark topic Recording Update through trigger based on csv file upload [message #667532] Thu, 04 January 2018 06:26 oracbeg Messages: 15 Registered: July 2017 Junior Member Hello Everyone. Request you to please suggest options for me some way on this. I have a csv file which i am uploading to a table using UTL_FILE. This activity runs on daily basis where file name remains same but data in there changes (insertion or update). I need to record that particular row with required columns which was changed in the csv file. Created a procedure and used below as condition which works for upload part. Code: [Select all] [Show/ hide] merge into test using dual on (Id = v_Id) when not matched then insert (Id, name, address) values (v_Id, v_name, v_address) when matched then update set name = v_name, address = v_address; For recording that update i used a trigger on this table (test) Code: [Select all] [Show/ hide] create or replace TRIGGER test_trg before update on test for EACH ROW begin If Updating Then insert into test_log (name, address ) values (:new.name, :new.address ); The trouble i am facing is that instead of recording that single record which is changed in table test using utl_file upload it records whole table values of required columns. Trigger works fine if i manually edit or update the test table but in case of UTL_FILE upload procedure trigger records whole table data of required columns but i need only the row which i actually changed in csv file and in database table. [Updated on: Thu, 04 January 2018 06:30] Report message to a moderator Re: Recording Update through trigger based on csv file upload [message #667533 is a reply to message #667532] Thu, 04 January 2018 06:40 Littlefoot Messages: 21310 Registered: June 2005 Location: Croatia, Europe Senior Member Account Moderator add to buddy list ignore all messages by this user Would something like this help? Code: [Select all] [Show/ hide] if updating and :new.name <> :old_name then insert into test_log (name, address) values (:new.name, :new.address); end if; Report message to a moderator Re: Recording Update through trigger based on csv file upload [message #667535 is a reply to message #667533] Thu, 04 January 2018 09:03 Solomon Yakobson Messages: 2697 Registered: January 2010 Location: Connecticut, USA Senior Member add to buddy list ignore all messages by this user The above will work only if column is NOT NULL, otherwise: if updating and not (:new.name = :old.name or (:new.name is null and :old.name is null)) SY. [Updated on: Thu, 04 January 2018 09:10] Report message to a moderator Re: Recording Update through trigger based on csv file upload [message #667594 is a reply to message #667535] Sun, 07 January 2018 22:33 oracbeg Messages: 15 Registered: July 2017 Junior Member Thank you so much for your reply . It worked and figured it out. As i mentioned that i am using utl_file to upload a csv file. A unusual issue i am facing is that it is uploading csv data in double quotes. I think it is is due to a column having data with line breaks like RAFFINERIE HEIDE 6.375% 17-01/12/2022. I am using something like this v_Securityname := REGEXP_SUBSTR(v_NewLine, '("[^"]*"|[^,]+)', 2, 3); Any way i could get over these line breaks and including that i need to skip headers while uploading csv file.
  2. Hello Everyone. Request you to please suggest some way on this. I have a csv file which i am uploading to a table using UTL_FILE. This activity runs on daily basis where file name remains same but data in there changes (insertion or update). I need to record that particular row with required columns which was changed in the csv file. I created a procedure and used below as condition which works for upload part. ## merge into test using dual on (Id = v_Id) when not matched then insert (Id, name, address) values (v_Id, v_name, v_address) when matched then update set name = v_name, address = v_address; For recording that update i used a trigger on this table (test) ## create or replace TRIGGER test_trg after update on test for EACH ROW begin If Updating Then insert into test_log (name, address ) values (:new.name, :new.address ); The trouble i am facing is that instead of recording that single record which is changed in table test using utl_file upload it records whole table values of required columns. Trigger works fine if i manually edit or update the test table but in case of UTL_FILE upload procedure trigger records whole table data of required columns.
  3. Hi Donald, Thanks for your response. As you stated...if i use a cursor to get count of the dml operation say delete for 10 rows. How will it be used in trigger to send a single mail using utl_mail.send....Now as i used trigger which will update the audit table and execute utl_mail to send a notification to xyz@abc.com. What current situation is that it is sending 10 mails for 10 rows deleted. I want that it should send 1 mail for 10 rows deleted at a time. Again if i delete 2 rows together it should single mail for that and so on.....Can we do that? Thanks a ton for your time.
  4. Need some help/suggestion here. I have created a trigger which will update the table (audit table) when a record is updated (insert, update and delete) in parent table and will also send a email to recipient before that. It is working fine. Issue is that it is sending email for each record updated and is flooding the inbox as the updating activity is frequent. I want that it should send single email instead of multiple emails every time when multiple records are updated in an instant with the information picked from audit table columns as it was doing for single row update. I have used utl_mail pacakage to execute. Please help. Waiting for the response
  5. I really need some advice on this. Please. I have a oracle table having column names with spaces. I made a code having sql select query inside, which extracts data accordingly. It works for column name without spaces like "select column from table". i need to make work this in vba "select column name from table". What delimiters should i use. Have tried {},[],``. Please help
  6. I am posting this after searching whole internet and trying all the solutions getting from there but this error is not letting me go through. I am trying to connect excel 2016 32 bit with oracle 11gr2 64 bit on windows 10 64 bit. When i connect i get this error Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" Things i have done before it are... 1) Installed oracle client (odbc drivers) 32 bit version 2)set up the tnsnames.ora as ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) 3) Set the environment variable as tns_admin till the directory client_1 as well as setting its path to client_1 4) made the odbc 32 bit connection with tns service name (orcl) and the connection gets successful. still getting the same error. Please respond if i am missing something or can try anything else. Thanks
  7. Hi everyone. I need help with excel upload to oracle table. Actually i want to upload an excel in oracle table but it is in transposed form. the excel rows in the left are columns in the table i created and corresponding are the values of it in rows in excel. Is there any way to upload that excel in oracle table without transposing or doing formatting it in that excel Please help
×