Wednesday, September 19, 2018

PL/SQL Send mail & Show tablespace used percentage

--Call SEND_MAIL_X or SEND_MAIL

DECLARE

tablespace_name VARCHAR2(50);
total_space VARCHAR2(50);
used_space VARCHAR2(50);
pct_free VARCHAR2(50);

P_TXT VARCHAR2(20000) := '';
P_SUB VARCHAR2(50) := 'subject';
P_SENDOR VARCHAR2(50) := 'finch.chen@linde-lienhwa.com.tw';
P_RECEIVER VARCHAR2(50) := 'finch.chen@linde-lienhwa.com.tw';
P_SERVER VARCHAR2(50) := '172.30.160.3';
P_PORT NUMBER := 25;
P_NEED_SMTP INT := 0;
P_USER VARCHAR2(50) DEFAULT NULL;
P_PASS VARCHAR2(50) DEFAULT NULL;
P_FILENAME VARCHAR2(50) DEFAULT NULL;
P_ENCODE VARCHAR2(50) DEFAULT 'bit 7';

CURSOR table_status IS
    select a.tablespace_name, a.total || 'm' total_space, (a.total - b.free) || 'm' used_space,
            to_char((a.total - b.free) / a.total * 100, '99.99') || '%' pct_free
    from
    (select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_data_files group by tablespace_name) a,
    (select tablespace_name, sum(bytes) / 1024 / 1024 free from dba_free_space group by tablespace_name) b
    where a.tablespace_name = b.tablespace_name
    order by pct_free desc;

BEGIN

P_TXT := '
    <html>
        <head>     
     
        </head>
        <body>
            <Table border="1">
                <tr bgcolor="#d3d3d3"><td>Tablespace Name</td><td> Total Space</td><td>Used Space</td><td>Used%</td></tr>
            ';
FOR a IN table_status LOOP
  P_TXT := P_TXT||'<tr><td>'||a.tablespace_name||'</td><td>'|| a.total_space||'</td><td>'|| a.used_space||'</td><td>'|| a.pct_free||'</td></tr> ';
END LOOP;
P_TXT := P_TXT||'</Table></body></html> ';

SEND_MAIL_X(
P_TXT ,
P_SUB ,
P_SENDOR ,
P_RECEIVER ,
P_SERVER ,
P_PORT ,
P_NEED_SMTP ,
P_USER ,
P_PASS ,
P_FILENAME ,
P_ENCODE );

END;

Tuesday, September 18, 2018

REP-1219: has no size -- length or width is zero. [Reports Builder]

REP-1219: has no size -- length or width is zero.

The best way to solve this issue is to convert the file. 

File>Administration>Convert...(navigation for 6i) 
Tools>File Conversions.... (navigation for 9i) 

1. Select the Source and Destination(preferable take a new location) 
2. Change the Destination Type to .RDF 
3. Switch to Option tab 
4. Change Destination Unit to "Character".

Monday, September 17, 2018

Execute AD_ZD Script after modify or create table in Oracle R12.2.X

 AD_ZD_TABLE
                                                                                                   

1) For new table creations - after creating the table in custom schema execute below script to generate editoning view and synonym for it in APPS schema.

Begin AD_ZD_TABLE.UPGRADE('XXCUST','XX_CUST_TBL1'); End;

2) For table alteratuons - after running the DDL run below script to regenerate the editioning view for syncing any table changes.

Begin AD_ZD_TABLE.PATCH('XXCUST','XX_CUST_TBL1'); End;

3) Table grants must be done through API to avoid invalids.

Begin AD_ZD.GRANT_PRIVS('SELECT','XX_CUST_TBL1','XX_ROLE0'); End;

4) To view objects in all editions..add _ae to the data dictionary views

select * from user_objects_ae;

5) To update seed data tables in Patch edition, execute prepare command.

example:
 Begin ad_zd_seed.prepare('WF_MESSAGES'); End;

The prepare will create a edition based storage for the run edition data for which the updates will be made. During cutover, the run time data will be synced using forward/reverse cross edition triggers.