কোন একটা প্রোগ্রাম/কাজ একটা নির্দিষ্ট সময় পর পর স্বয়ংক্রিয় ভাবে চালাতে DBMS_SCHEDULER প্যাকেজ ব্যাবহার করা হয়। Continue reading How to make automated task in Oracle
Category: Oracle Database
Extend varchar2 data type length 4K to 32K in 12c
Oracle 12c নিউ ফিচার গাইড অনুযায়ী-
“The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). This is especially useful for brief textual data types and the capabilities to build indexes on these types of columns.”
(Oracle® Database New Features Guide 12c Release 1 (12.1) E17906-16 Page 1-13) Continue reading Extend varchar2 data type length 4K to 32K in 12c
How to send mail from Oracle APEX
In this tutorial, I will show you how to send mail from Oracle APEX. Let’s start… Continue reading How to send mail from Oracle APEX
Tips and Tricks – 1
1. Create unique case-insensitive constraint on a column.
Solution: Add function level unique index, example –
|
1 |
CREATE UNIQUE INDEX index_name on table_name (UPPER(Column_Name)); |
2. Cannot create service/sid using ORADIM (DIM-00019: create service error O/S-Error: (OS 5) Access is denied.)
Solution: Make sure your oracle service is running. then create instance.
To create instance- Open CMD (command prompt) with admin privilege. the type the following then hit enter.
|
1 |
ORADIM -NEW -SID SID_NAME |
continue…
Enable network service (ACL) and Send mail from APEX
Enabling Network Service: By default, the ability to interact with network services is disabled in Oracle Database 11g. Therefore, you must use the BMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040200 database user:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE ACL_PATH VARCHAR2(4000); BEGIN -- Look for the ACL currently assigned to '*' and give APEX_040200 -- the "connect" privilege if APEX_040200 -- does not have the privilege yet. SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL; IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (ACL_PATH,'APEX_040200','connect') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040200', TRUE, 'connect'); END IF; EXCEPTION -- When no ACL has been assigned to '*'. WHEN NO_DATA_FOUND THEN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml', 'ACL that lets power users to connect to everywhere', 'APEX_040200', TRUE, 'connect'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*'); END; / COMMIT; |
Send Mail: Oracle application express send mail using APEX_MAIL package. example…
|
1 2 3 4 5 6 7 |
APEX_MAIL.SEND( p_to => 'email@gmail.com', p_from => 'email2@oracle.com', p_subj => 'APEX_MAIL with attachment', p_body => 'Please review the attachment.', p_body_html => '<b>Please</b> review the attachment'); / |
Send mail with attachments…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE l_id Number; x varchar2(100); y BLOB; z varchar2(100); BEGIN l_id := APEX_MAIL.SEND( p_to => 'email@gmail.com', p_from => 'email2@oracle.com', p_subj => 'APEX_MAIL with attachment', p_body => 'Please review the attachment.', p_body_html => '<b>Please</b> review the attachment'); SELECT FILENAME, IMAGE, MIMETYPE into x, y, z FROM CUSTOMER WHERE CUSTOMER_ID = 100; APEX_MAIL.ADD_ATTACHMENT( p_mail_id => l_id, p_attachment => y, -- BLOB column name p_filename => x, -- File Name p_mime_type => z); -- MIME column name END; / |