.someictstuff{}

SQL Server - Delete data from table contained huge records and control the log file size

OK, first of all. I do not need to explain the difference between truncate and delete. You most probably already knew it. I don't think truncate will help at this moment.

My situation here is my table has almost 90M records and this records was a monthly data snapshot for a year. I need to remove records from Jan to Jun and the rest will remain.

Let summarize this;

Objective : To remove past 6 months records which approximately 45M ++ records to be deleted

Constraint : My disk utilization almost 90% and insert/delete command both will generate log which will make log file increase drastically during the insert/delete operation.

What I do?

As I mentioned earlier, my data is a monthly snapshot data. I cannot simply execute delete command with filtering condition where date < '2020-07-01' right? If you do this, your log file with increase skyrocketly.

I prepared 6 delete command lines, each specifically will delete data for only 1 month, in sequence. And to handle the log? I will put extra DBCC command so that it will shrink the log file before another delete operation taking place.

DBCC SHRINKFILE (your_log_file_name, 1); * 1 means shrink the file to 1MB

Before that, I need to make sure that the database recovery log are set to Simple instead of Full.

delete from my_table where date < '2020-02-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-03-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-04-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-05-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-06-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-07-01';

DBCC SHRINKFILE (your_log_file_name, 1);

That approach works for me and I believe it should work for you too. I you have suggestion or comment, do comment me below.

Thanks!

How to send email from Oracle database using procedure/PLSQL

It has been a long time I did not post to this blog. Happy New Year 2017 anyway!

Recently, I am working on something related to my work. I have to monitor Oracle job scheduler, their job status etc. One of the options came across my mind is to email me the job status everyday from Oracle database.

Below script is working on my machine, which running Oracle XE. If error accrued during execute the script, it may come from insufficient privileges on certain package. Please grant where necessary.

Here is the script:

DECLARE
V_FROM VARCHAR2(80) := 'YOUR_EMAIL@GMAIL.COM';
V_RECIPIENT VARCHAR2(80) := 'RECIPIENT_EMAIL@GMAIL.COM';
V_SUBJECT VARCHAR2(80) := 'TEST EMAIL';
V_MAIL_HOST VARCHAR2(30) := 'YOUR_MAIL_HOST';
V_MAIL_CONN UTL_SMTP.CONNECTION;
CRLF VARCHAR2(2) := CHR(13)||CHR(10);
V_CONTENT VARCHAR2(300);

BEGIN

SELECT 'THIS IS EMAIL TESTING FROM ORACLE DATABASE.'
INTO V_CONTENT
FROM DUAL;

BEGIN
V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, V_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
'DATE: ' || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||
'FROM: ' || V_FROM || CRLF ||
'SUBJECT: '|| V_SUBJECT || CRLF ||
'TO: ' || V_RECIPIENT || CRLF ||
CRLF ||
V_CONTENT || CRLF || --MESSAGE BODY
CRLF

);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL', TRUE);
END;

END;

Hope this help.

How to convert VCF to CSV and vice versa

Nowadays, most contact information for individual store inside their mobile phone and gadgets. Android device for example, storing their contact information locally and when user do contact backup, the file produced will be in VCF formatted data.

Now, consider this scenario; I do have *.VCF in hands and I would like to manage my contact information manually. Manually meanings I wanted to sort, remove duplicate, change their first name or last name, change email address, change mobile number etc. For me, manage it all in Excel is the most convenience way. So , I need that file to be converted to Excel readable file.

The solution

The are many online solution available, no need to install seldom use software or whatever. Do it online because everyone now is connected to internet. One example is vcftocsv.com . The application provided by the website is to convert CSV to VCF and VCF/LDIF to CSV. Just read the instruction inside the website and you will be good.

Again, here is the link: vcftocsv.com

Hope this will help.

Cheers!

Floating ads using CSS

In the events that you wanted to have a floating ads on your blogspot website, the use of CSS most probably will help a lot. You may refer to the screenshot below, or the live demo of it at this site.

Pretty simple actually.

Click [Add a gadget] from your blogger layout page. In does not matter where it is located.
Choose [HTML/Javascript] from the list of gadget available.
Paste the code given below.

<style type="text/css">

.advt {

color:black;

font-size:10px;

position:fixed;

left:25px;

margin-bottom:25px;

bottom:0;

}

</style>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.3.min.js"></script>

<script type="text/javascript">

$(document).ready(function() {

$("#advt").click(function() {

});

$("#closeadvt").click(function(event) {

event.stopPropagation();

$("#advt").fadeOut();

})

});

</script>

<div align="center" id="advt" class="advt"><span id="closeadvt" style="cursor:pointer;">Close X</span>

<p></p>

<a href="http://etoro.tw/1D3PSrW">

<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5zOwoElynlXWHRu7XEusOxdQQ8mBrZN6CIyEl-XwSOUBypfLwFT1CFT2bBI3lri-VhXvg9LuseFNOO115UX4dDbk-ASrrbeWpKb9LzeiMkAkyCF51fqg0foBWD7w-ETQrJfEcTW7okQ/s1600/etoro_copy_trader1.png" />

</a>

</div>

The reason why it is does not matter is because the floating ads will be sitting at the bottom left of your blogspot website. (It also can be use other then blogspot as well if your know how to code it)

Please change these parameter accordingly:

1. To change your preferred link --> please find code <a href="http://etoro.tw/1D3PSrW">

2. To change the image --> please find code <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5zOwoElynlXWHRu7XEusOxdQQ8mBrZN6CIyEl-XwSOUBypfLwFT1CFT2bBI3lri-VhXvg9LuseFNOO115UX4dDbk-ASrrbeWpKb9LzeiMkAkyCF51fqg0foBWD7w-ETQrJfEcTW7okQ/s1600/etoro_copy_trader1.png" />

3. To change the wording --> please find "Close X"

4. To move the floating ads to top-left --> please find code [bottom:0;], change to [top:0;]

Please change the CSS if you know what you doing. Nothing harm to playing around with the CSS. Have fun!

Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

For some of us whom frequently using MS Access in daily use, encountered an error might be very troublesome.

The problem is like this : I am using MS Access for data analysis and checking and have a connection over the network to Oracle database. My connection was using Microsoft ODBC Driver for Oracle. For some security reason, I need to change the password now. The password changed with no problem but encountered error ORA-12154 when trying to relink the tables.

Error as below:

ODBC--call failed
Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

Generally, ORA-12154 indicated that the specifying TNS alias is not resolving. Few questions needed to clarify;

Are you using a DSN for your connection to Oracle database?
If so, what is the TNS alias that you specifying?
Does that alias exist in your tnsnames.ora file?
Are there multiple tnsnames.ora files on your machine?

Your should have your tnsnames.ora file when you installing the Oracle client software/driver. If you wondering what is the version of the Oracle client you have installed, the simplest way is to get DOS command prompt and type 'tnsping [service_name]' where [service_name] is TNS alias specified in your DNS.

The ouput of it may look like this:

C:\Users\jcave>tnsping fuddy_duddy
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-OCT-2
010 08:10:51
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
TNS-03505: Failed to resolve name

The "Used parameter files" line shows you the directory where the TNS related files are stored. A tnsnames.ora and a sqlnet.ora file should be in that directory and it alwasys be there by default. Perhaps you should check the TNS alias name and confirm with your DNS setting.

How to load image files into BLOB column using PL/SQL

Assuming that you have a list of physical images needed to be loaded into Oracle database. And also you have a free text list of the files, perhaps in form of CSV file format.

From above, you may already get the bigger picture of the scenario which you need to execute.

Lets consider this task needed to be execute in Oracle using Oracle procedure.

The PL/SQL

I already prepared the PL/SQL example for this purpose. Check the code below;

CREATE OR REPLACE PROCEDURE PROC_LOAD_IMAGES (FILE_DIR in VARCHAR2 , MYFILE_NAME in VARCHAR2) AS
-- FILE_DIR is the directory of the flat file and images
-- MYFILE_NAME is the flat file name
F UTL_FILE.FILE_TYPE;
V_LINE VARCHAR2 (1000);
V_CACC_OLD_NUM VARCHAR2(24 BYTE);
V_IDENT_TYPE NUMBER(1);
V_ID_NUMBER VARCHAR2(20 BYTE);
V_FILE_NAME VARCHAR2(80 BYTE);
V_FILE_BLOB BLOB;
V_CLASS VARCHAR2(1 BYTE);
lFile BFILE;

BEGIN
F := UTL_FILE.FOPEN (upper(FILE_DIR), MYFILE_NAME, 'R');
IF UTL_FILE.IS_OPEN(F) THEN
LOOP
BEGIN
UTL_FILE.GET_LINE(F, V_LINE, 1000);
IF V_LINE IS NULL THEN
EXIT;
END IF;
--If using piping, change to '[^|]+'
V_CACC_OLD_NUM := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 1);
V_IDENT_TYPE := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 2);
V_ID_NUMBER := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 3);
V_FILE_NAME := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 4);
V_CLASS := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 5);

INSERT INTO BS_SIGNATURE_IMAGES (CACC_OLD_NUM, IDENT_TYPE, ID_NUMBER, FILE_NAME, CLASS) VALUES( V_CACC_OLD_NUM, V_IDENT_TYPE, V_ID_NUMBER, empty_blob(), V_CLASS) RETURNING FILE_NAME INTO V_FILE_BLOB;
lFile := BFILENAME(upper(FILE_DIR), V_FILE_NAME);

IF dbms_lob.fileexists(lFile) = 1 THEN
DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(V_FILE_BLOB, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(DEST_LOB => V_FILE_BLOB,
SRC_LOB => lFile,
AMOUNT => DBMS_LOB.GETLENGTH(lFile));
DBMS_LOB.CLOSE(lFile);
DBMS_LOB.CLOSE(V_FILE_BLOB);
END IF;

COMMIT;

EXIT;
END;
END LOOP;
END IF;
UTL_FILE.FCLOSE(F);
END;
/

The important portion UTL_FILE function. This is the function to read the file and responsible to load the files into BLOB format into the database.

APEX : Send email with attachment (Part 2)

Please refer to my previous post related to this, here.

Moving forward, by now you should already completed doing these 2 things:

User can insert any attachment into the form
The attachment will be save into my own table within the Oracle database.

So now, the next step is to configure APEX to sent-out email together with the attachment.

The email process must be create at point [Processing > Processes] on [Page Processing] , right after the 'CopyFile' process (refer to my previous post)

Right click on [Processes] > Create
Select [PL/SQL] and Next
Give a name to identify the procesess and click Next
On the [Enter PL/SQL Page Process], paste below code;

DECLARE
l_id number;
BEGIN
l_id := APEX_MAIL.SEND( p_to => 'fred@flintstone.com',
p_from => 'barney@rubble.com',
p_subj => 'APEX_MAIL with attachment',
p_body => 'Please review the attachment.',
p_body_html => '<b>Please</b> review the attachment' );
FOR c1 IN (SELECT filename, blob_content, mime_type
FROM attachment_files
WHERE ID = :P1_YOUR_ID ) loop
APEX_MAIL.ADD_ATTACHMENT( p_mail_id => l_id,
p_attachment => c1.blob_content,
p_filename => c1.filename,
p_mime_type => c1.mime_type);
END LOOP;
END;
BEGIN
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'localhost',
P_SMTP_PORTNO => '25'
);
END;

Please see the code and change whenever suit with your table name and page item. For example code below:

FOR c1 IN (SELECT filename, blob_content, mime_type
FROM attachment_files
WHERE ID = :P1_YOUR_ID ) loop

The [attachment_files] comes from my table which I have created previously in so called Part 1.

You can have multiple 'File Browse...'. You may also need to change the PL/SQL code if have more [File Browse]. Do comment below if you still need help.

I will try my best to help. :)

Subscribe to: Posts (Atom)