Tuesday, February 25, 2014

Script to find Oracle API's for any module

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name 
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AP_%API%' –- Checking for AP Related APIs
order by
a.owner, a.name

Thursday, January 16, 2014

Oracle Apps API's

API'S
=====
Oracle AP Invoice API's
Oracle AP Supplier API's
Oracle External Bank Payment API's
Oracle FND User API's
Oracle HRMS API's

https://blogs.oracle.com/prajkumar/entry/oracle_hrms_apis

Thanks to Punith RajKumar... :-)

Tuesday, March 12, 2013

Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well

SELECT hou.NAME operating_unit_name,
hou.short_code,
hou.organization_id operating_unit_id,
hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code,
ood.organization_id Inv_organization_id,
ood.chart_of_accounts_id
FROM hr_operating_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC

Saturday, January 26, 2013

Send a mail using Oracle's Utility UTL_SMTP

Below is the sample code to send a mail using Oracle's Utility UTL_SMTP.....
  create or replace procedure xxsammail as
    L_recipient VARCHAR2 (80) := 'vvydhyal@cisco.com';
    L_from VARCHAR2 (80) := 'vvydhyal@cisco.com';
    L_mail_host VARCHAR2 (30) := 'mailman.cisco.com';
    L_subject VARCHAR2 (80) := 'This is a Test Mail';
    L_mail_conn UTL_SMTP.connection;
begin
     L_mail_conn := UTL_SMTP.open_connection (L_mail_host, 25);
     UTL_SMTP.helo (L_mail_conn, L_mail_host);
     UTL_SMTP.mail (L_mail_conn, L_from);
     UTL_SMTP.rcpt (L_mail_conn, L_recipient);
     --UTL_SMTP.DATA (L_mail_conn,'Hellllooooo');
     utl_smtp.data(L_mail_conn,'From: Oracle Database'||utl_tcp.crlf ||
                                          'To: '||L_recipient||utl_tcp.crlf||
                                          'Subject: '||L_subject||utl_tcp.crlf||L_message);
      UTL_SMTP.quit (L_mail_conn);
exception
    when others then
         dbms_output.put_line('Error : ' ||sqlerrm);
end;

--------------------================================
begin
    xxsammail;
exception
    when others then
        dbms_output.put_line('Error : ' ||sqlerrm);
end;

Friday, January 4, 2013

Multicolumn Subqueries

We can compare elements having multiple values with the set of elements. This is called a multicolumn subquery.

CREATE TABLE EMPDEP(EMPNO NUMBER, DEPTNO NUMBER,SALARY NUMBER);

CREATE TABLE EMPCITY(EMPNO NUMBER, DEPTNO NUMBER,CITY VARCHAR2(30));

INSERT INTO EMPDEP VALUES (1,10,1000);

INSERT INTO EMPDEP VALUES (2,20,2000);

INSERT INTO EMPDEP VALUES (3,30,3000);

INSERT INTO EMPCITY VALUES (1,10,'NAGPUR');

INSERT INTO EMPCITY VALUES (2,20,'DELHI');

INSERT INTO EMPCITY VALUES (3,30,'NAGPUR');

SELECT * FROM EMPDEP

EMPNO DEPTNO SALARY
1 10 1000
2 20 2000
3 30 3000

SELECT * FROM EMPCITY

EMPNO DEPTNO CITY
1 10 NAGPUR
2 20 DELHI
3 30 NAGPUR

SELECT E1.EMPNO, E1.DEPTNO,E1.SALARY
FROM EMPDEP E1 WHERE (E1.EMPNO,E1.DEPTNO)
IN (SELECT E2.EMPNO,E2.DEPTNO FROM EMPCITY E2 WHERE CITY = 'NAGPUR')

EMPNO DEPTNO SALARY
1 10 1000
3 30 3000

Sunday, May 27, 2012

UTL File Concept

When we are going to work with UTL Files, we have to follow four steps,
  1. Declare a File Variable
  2. Open a File using File Variable
  3. Write the data into the file using File Variable
  4. Close the file using File Variable.
Note: To create a file in the server the file directory path should be defined or registered.  To check the defined file directory path below query can be used,
        select value from v$parameter where name like 'utl_file_dir'
If the above select statement doesn't return any records, that means there is no defined file directory path.  This will be taken care by DBA's.  Else if you want to create a directroy path follow the below steps
  • Connect as SYSDBA
  • CREATE OR REPLACE DIRECTORY AS '/PATH'
  • GRANT READ, WRITE  ON DIRECTORY TO SCOTT;
  • ALTER SYSTEM SET UTL_FILE_DIR = '/PATH' SCOPE=SPFILE 
UTL File Steps:
  • Declare a File Variable:
         Syn:   file_var_name UTL_FILE.FILE_TYPE;
           Ex:  fv UTL_FILE.FILE_TYPE;
  • Open a File using File Variable
            Syn: file_var_name  := UTL_FILE.FOPEN(,,);
             Ex:  fv := UTL_FILE.FOPEN('C:/TEMP','SAM.txt','W');
  • Write the data into the File
             Syn: UTL_FILE.PUT_LINE(file_var_name,'Message' / variable);
               Ex: UTL_FILE.PUT_LINE(fv,'Hello');
  • Close the File
              Syn: UTL_FILE.FCLOSE('file_var_name);
                Ex: UTL_FILE.FCLOSE(fv);

Sample piece of UTL File Code,

create or replace procedure utl_sample
is
  l_file_var utl_file.file_type;
begin
  l_file_var := utl_file.fopen('C;/temp','SAM.txt','W');
  utl_file.put_line(l_file_var,'This is a sample prog');
  utl_file.fclose(l_file_var);
end;