Category: Oracle Database

How to make automated task in Oracle

কোন একটা প্রোগ্রাম/কাজ একটা নির্দিষ্ট সময় পর পর স্বয়ংক্রিয় ভাবে চালাতে DBMS_SCHEDULER প্যাকেজ ব্যাবহার করা হয়। Continue reading How to make automated task in Oracle

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

Tips and Tricks – 1

1. Create unique case-insensitive constraint on a column.

Solution: Add function level unique index, example –

 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.

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:

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…

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…

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;
/