Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts

Tuesday, June 9, 2015

Oracle Apps : Apply AP Invoice Hold using API

The hold codes can be extracted from query below.

SELECT *
  FROM ap_hold_codes
 WHERE hold_type = 'INVOICE HOLD REASON';

Below is the API to apply hold using Standard Oracle API :


PROCEDURE inv_hold (p_invoice_id        IN     NUMBER,
                    p_reject_code       IN     VARCHAR2,
                    p_hold_val_status   IN     VARCHAR2,
                    x_status               OUT VARCHAR2)
IS
   l_hold_invoice_id   NUMBER := NULL;
   l_hold_err_msg      VARCHAR2 (2000) := NULL;
BEGIN
   l_hold_invoice_id := p_invoice_id;

   IF     l_hold_invoice_id IS NOT NULL
      AND p_reject_code IS NOT NULL
      AND p_hold_val_status = 'Y'
   THEN
      BEGIN
         ap_holds_pkg.insert_single_hold (
            X_invoice_id         => l_hold_invoice_id,
            X_hold_lookup_code   => p_reject_code);

         x_status := 'S';
      EXCEPTION
         WHEN OTHERS
         THEN
            l_hold_err_msg :=
                  'Unknow Exception Occured in Hold API call.. Exception Code : '
               || SQLCODE
               || ' Exception Details : '
               || SQLERRM;
            DBMS_OUTPUT.put_line (l_hold_err_msg);
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END inv_hold;



Monday, April 13, 2015

How to use plsql collections in select statment

Step 1 : Declare Collection type Globally

For Example:


 Test_Coll can be declared globally as "TYPE  Test_Coll IS TABLE OF INTEGER"

Step 2 :

DECLARE

 test_coll_tab  Test_Coll;
 MyName         VARCHAR2(100);

BEGIN

test_coll_tab =  Test_Coll();
test_coll_tab.extend;
test_coll_tab(1):=1;
test_coll_tab.extend;
test_coll_tab(2):=2;
test_coll_tab.extend;
test_coll_tab(3):=3; 
test_coll_tab.extend;
test_coll_tab(4):=4;
test_coll_tab.extend;
test_coll_tab(5):=5; 

 SELECT Name
      INTO MyName
    FROM Item
 WHERE ItemId NOT IN (select * from table(test_coll_tab));

END;



Friday, August 22, 2014

Ref Cursor in Oracle PL/SQL

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.

Let us start with a small sub-program as follows:



%ROWTYPE with REF CURSOR

In the previous section, I retrieved only one column (ename) of information using REF CURSOR.  Now I would like to retrieve more than one column (or entire row) of information using the same.  Let us consider the following example:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
----------------------------------------------------------------------------------------------------

As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time.  Before associating a new SELECT statement, we need to close the CURSOR.  Let us have an example as follows:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  open c_emp for select ename,sal from emp where deptno = 10;
  dbms_output.put_line('Department: 10');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
  open c_emp for select ename,sal from emp where deptno = 20;
  dbms_output.put_line('Department: 20');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
end;

-----------------------------------------------------------------------------------------------------------

Wednesday, August 20, 2014

Nested Table in PL/SQL

Nested Table:
  Nested table is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table may contain multiple rows. In one sense, it's a way of storing a one-to-many relationship within one table, Consider a table that contains information about departments, each of which may have many projects in progress at any one time. In a strictly relational model, you would create two separate tables - DEPT and PROJECT.
  nested table allow you to store the information about projects within the DEPT table. The PROJECT table records can be accessed directly via the DEPT table, without the need to perform a join. The ability to select the data without traversing joins may make data easier to access for users. Even if you do not define methods for accessing the nested data, you have clearly associated the department and project data. In a strictly relational model, the association between the DEPT and PROJECT tables would be accomplished via foreign key relationships.



SQL> create or replace type emp_ty as object
  2  (desg varchar2(10),
  3  dname varchar2(10),
  4  doj date);
  5  /

Type created.

SQL> create type emp_nt as table of emp_ty;
  2  /

Type created.

SQL> create table empdata
  2  (ename varchar2(10),
  3  details emp_nt)
  4  nested table details store as emp_nt_tab;

Table created.

SQL> desc empdata
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 DETAILS                                            EMP_NT

SQL> set describe depth 2
SQL> desc empdata
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 DETAILS                                            EMP_NT
   DESG                                             VARCHAR2(10)
   DNAME                                            VARCHAR2(10)
   DOJ                                              DATE


SQL> ed
Wrote file afiedt.buf

  1  insert into empdata values (
  2  'Raju',
  3  emp_nt(
  4  emp_ty('Clerk','Sales','12-Sep-05'),
  5  emp_ty('Asst','Mrkt','15-Oct-04'),
  6* emp_ty('Mngr','Sales','13-Aug-05')))
SQL> /

1 row created.

SQL> select * from empdata;

ENAME
----------
DETAILS(DESG, DNAME, DOJ)
--------------------------------------------------------------------------------
Raju
EMP_NT(EMP_TY('Clerk', 'Sales', '12-SEP-05'), EMP_TY('Asst', 'Mrkt', '15-OCT-04'
), EMP_TY('Mngr', 'Sales', '13-AUG-05'))


SQL>  select ename,n.desg,n.doj from empdata,table(empdata.details) n;

ENAME      DESG       DOJ
---------- ---------- ---------
Raju       Clerk      12-SEP-05
Raju       Asst       15-OCT-04
Raju       Mngr       13-AUG-05

Merge, Exists and Non-Exists in PL/SQL

Exists:
-------------

The EXISTS condition is considered "to be met" if the subquery returns at least one row.

The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );

Ex:
---

SELECT *
FROM dept
WHERE EXISTS
  (select *
    from emp
    where dept.deptno = emp.deptno);


--------------------------------------

Not Exists:


SELECT *
FROM dept
WHERE NOT EXISTS
  (select *
    from emp
    where dept.deptno = emp.deptno);


-------------------------------------

Merge:


create table student10 ( sno number(3),
             sname varchar2(20),
             marks number(3));

insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);



create table student20 ( sno number(3),
             sname varchar2(20),
             marks number(3));


insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);



merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);

Tuesday, August 19, 2014

Where Current of : PL/SQL

WHERE CURRENT OF clause is used in some UPDATE statements.

The WHERE CURRENT OF clause is an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated.

We must declare the cursor with FOR UPDATE clause to use this feature.

When the session opens the cursor with the FOR UPDATE clause, all rows in the return set will hold row-level locks. Other sessions can only query for the rows, but cannot update or delete.


declare
cursor c1
is
select empno,ename,sal from emp
where comm is null
for update of comm;

var_comm number(4);
begin
for cur_rec in c1 loop
if cur_rec.sal < 2000 then
var_comm:=200;
elsif cur_rec.sal <4000 then
var_comm:=400;
else
var_comm:=100;
end if;

update emp set comm=var_comm
where current of c1;

end loop;
end;
/

Rank Function in PL/SQL


Rank :

In Oracle/PLSQL, the rank function returns the rank of a value in a group of values.

The rank function can be used two ways - as an Aggregate function or as an Analytic function.

---
Rank used as aggregate function:

select rank (800) within  group (order by sal) rank from emp;

select rank (850) within  group (order by sal) rank from emp;

select rank (5000) within  group (order by sal) rank from emp;


Rank used as analytical function:

As an Analytic function, the rank returns the rank of each row of a query

select ename, sal,
rank() OVER (ORDER BY sal) rank
from emp;

Thursday, July 10, 2014

Variants of NVL and Branching techniques in SQL

NVL

NVL gives you the flexibility to choose an alternate path if your current value is null.

For Example
Select NVL(dept,'Bench') from employees;

The above query will returns Bench if the dept is null for an employee

NVL2

NVL2 give you extended flexibility to choose your path if the value is null or not null

For Example
Select NVL2(dept,'Existing','New') from employees;

The above query returns Existing if dept is not null and New if the dept is null.

DECODE

Decode can be thought as a mapping/lookup. If the value matches to a code, a corresponding meaning is return.

For Example
Select DECODE(dept,
     'SALES','Department of Sales',
     'MKT','Department of Marketing',
     'MFG','Manufacturing',
     'Bench') from employees;

In the above query if dept is equal to SALES, Department of Sales if returned. If none of them matches, Bench is returned.

COALESCE

COALESCE function returns the first non null operand.

For Example
select COALESCE(NULL,last_name,first_name) from employee;

The above function will check the fisrt operand, Since it is null it will move to second operand.
If last_name is null,  first_name is returned
If last_name is not null, first_name will be returned.

NULLIF

Nullif returns null if two operands are equivalent,If they are not equivalent you can return a custom messge.

For example
select NULLIF(first_name,last_name) from employee;

If first_name is equal to last_name null is returned, else fist_name is returned.
It can be thought of as
select decode(first_name,last_name,NULL,first_name) from employee;

Wednesday, July 2, 2014

RECORDS in PL/SQL

Introduction


Record is a data-structure which can hold differnt type of datatypes. Record can be imagined as a database table with multiple columns

We have the following types of record in PL/SQL
1) Cursor Based Records
2) Table Based Records
3) User Defined Records

Example of Cursor Based Record
%ROWTYPE is used for Cursor and Table Based Records


DECLARE
   CURSOR cur_employee is
      SELECT name, eno, dept
      FROM employees
   employee_rec cur_employee%rowtype;
BEGIN
   OPEN customer_cur;
   LOOP
      FETCH cur_employee into employee_rec
      EXIT WHEN cur_employee %notfound;
      DBMS_OUTPUT.put_line(employee_rec.name || ' ' || employee_rec.dept);
   END LOOP;
END;
/



Example of Table Based Record
%ROWTYPE is used for Cursor and Table Based Records


DECLARE
   employee_rec employees%rowtype;
BEGIN
   SELECT * into employee_rec
   FROM employees
   WHERE eno = 293215;

   dbms_output.put_line('Employee Name : ' || employee_rec.name);
   dbms_output.put_line('Employee Number : ' || employee_rec.eno);
   dbms_output.put_line('Department : ' || employee_rec.dept );
END;
/


Example of User Defined Record


DECLARE
   type employee is record
      (name varchar(100),
       eno varchar(10),
       dept varchar(100)
    );
   employee_rec employee;

BEGIN
   -- Employee assignment
      employee_rec.name := 'John';
      employee_rec.eno  := '21232';   
      employee_rec.dept    := 'Sales';

   -- Print Employee record
   dbms_output.put_line('Employee Name : '|| employee_rec.name );
   dbms_output.put_line('Employee Number : '|| employee_rec.eno );
   dbms_output.put_line('Employee Department : '|| employee_rec.dept );

END;
/

Friday, June 27, 2014

Create Custom Queue in Oracle and use as pre-queue to other queue

In our scenario we setup a JMS queue which will be used a pre-queue to ECX_INBOUND queue.
Below are the steps to achieve the same.

Queue Creation


1) Create a queue table
BEGIN
dbms_aqadm.create_queue_table
     ( queue_table        => 'XX_INQUEUE_TBL'
     , queue_payload_type => 'sys.aq$_jms_text_message'
     );
END;

2) Create a Queue based on queue table
    BEGIN
dbms_aqadm.create_queue
    ( queue_name  => 'XX_INQUEUE'
    , queue_table => 'XX_INQUEUE_TBL'
    );
END;

3) Start Queue. If you do no start the queue you will get error message
   as "ORA-25207: enqueue failed, queue APPS.XX_INQUEUE is disabled from enqueueing"

BEGIN
dbms_aqadm.start_queue
( queue_name => 'XX_INQUEUE'
);
END;

4) Query confirm queue and queue table. 

SELECT *
 FROM XX_INQUEUE_TBL
WHERE q_name = 'XX_INQUEUE';

SELECT *
 FROM DBA_QUEUES
WHERE name = 'XX_INQUEUE';


 Populating queue with XML message

 1) Run the block below to populate the queue
set serveroutput on;
DECLARE
l_destination_queue    VARCHAR2 (100) := 'XX_INQUEUE';
l_enqueue_options      SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
l_enqueue_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
l_enqueue_payload      SYS.aq$_jms_text_message;
x_enqueue_msgid        RAW (16);
l_xml_message CLOB := '<HEADER><NAME>Hello World</NAME></HEADER>';

BEGIN
 l_enqueue_payload := SYS.AQ$_JMS_TEXT_MESSAGE.construct();
 l_enqueue_payload.set_text (l_xml_message);
 sys.DBMS_AQ.enqueue (
queue_name           => l_destination_queue,
enqueue_options      => l_enqueue_options,
message_properties   => l_enqueue_properties,
payload              => l_enqueue_payload,
msgid                => x_enqueue_msgid
);
 dbms_output.put_line(x_enqueue_msgid);  
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

 2) Query the queue table to verify data is populated the queue
SELECT *
  FROM XX_INQUEUE_TBL
 WHERE q_name = 'XX_INQUEUE';

The user is displayed as [SYS.AQ$_JMS_TEXT_MESSAGE]
If you want to view the payload use the query below
SELECT A.*,A.user_data.text_vc
  FROM XX_INQUEUE_TBL A
 WHERE q_name = 'XX_INQUEUE';


Once we have the data in XX_INQUEUE. We will now dequeue the data
and enqueue it into the ECX_INBOUND queue for further processing
Below a are the api's you need to enqueue the ECX_INBOUND queue.
      SYSTEM.ecxmsg     : This is required to create a payload
sys.DBMS_AQ.enqueue : This is required to enqueue the message
  to standard APPLSYS.ECX_INBOUND queue.

I will cover the above two api's in my next post.



Saturday, May 17, 2014

Few Examples on Dynamic SQL

Dynamic SQL


------------------------------------------------------------------
DECLARE
v_table_name varchar2(100) := 'test_dept';
v_stmt varchar2(1000);
BEGIN
v_stmt := 'insert into '||v_table_name||' values(50,''HR'')';
execute immediate v_stmt;
dbms_output.put_line(v_stmt);
END;

-------------------------------------------------------------------
DECLARE
type sob_record is record
(
name gl_sets_of_books.NAME%type,
currency_code gl_sets_of_books.CURRENCY_CODE%type,
set_of_books gl_sets_of_books.SET_OF_BOOKS_ID%type
);
v_stmt varchar2(1000);
curr sys_refcursor;
row_rec sob_record;
BEGIN
v_stmt := 'select name,currency_code,set_of_books_id from gl_sets_of_books where set_of_books_id=:input';
open curr for v_stmt using 2023;
loop
fetch curr into row_rec;
exit when curr%notfound;
dbms_output.put_line(row_rec.currency_code || '    '||row_rec.name);
end loop;
END;


-------------------------------------------------------------------------------

DECLARE
type status_record is record
(
cid xx_account_status_swap.customer_number%type,
acc_status xx_account_status_swap.new_account_status%type
);
type status_table is table of status_record;

v_stmt varchar2(1000);
curr sys_refcursor;
status_coll status_table;
BEGIN
v_stmt := 'select customer_number,new_account_status from xx_account_status_swap where success_status=:input';

open curr for v_stmt using 'P';
fetch curr bulk collect into status_coll;

for i in 1..status_coll.count loop
dbms_output.put_line(status_coll(i).cid||'   '||status_coll(i).acc_status);
end loop;

dbms_output.put_line('----------------------------------------------');
dbms_output.put_line(status_coll.count);
dbms_output.put_line('----------------------------------------------');
END;

--------------------------------------------------------------------------------------

Convert CSV to Rows in Oracle

Query


SELECT substr(csv,
              instr(csv,
                    ',',
                    1,
                    lev) + 1,
              instr(csv,
                    ',',
                   1,
                    lev + 1) - instr(csv,
                                    ',',
                                    1,
                                    lev) - 1)
  FROM (SELECT ',' || 'a,b,c,d' || ',' csv
          FROM dual),
       (SELECT LEVEL lev
          FROM dual
        CONNECT BY LEVEL <= 100)
 WHERE lev <= length(csv) - length(REPLACE(csv,
                                           ',')) - 1;