Passion For Learning Technology: July 2016

Wednesday, 27 July 2016

Trigger Example in Oracle

As Salamo alaikum wa rahmatullah

--Create Table Name T

Create Table T
(
ID number(10) primary key,
FName varchar2(25) not null,
Lname varchar2(25) not null
)

--Create Table Name T_Audit

Create Table T_Audit
(
ID number(10) primary key,
FName varchar2(25) not null,
Lname varchar2(25) not null,
Created_dt Date
)

-- Create Trigger

Create Or Replace Trigger Trg_Test
Before INSERT ON T
For Each Row
Begin
Insert into T_Audit (ID,Fname,Lname,Created_dt) values(:new.ID,:new.Fname,:new.Lname,sysdate);
End;

Insert into T Values (1,'Zaheer','Khan')
Insert into T Values (2,'Mohammad','Sami')
Insert into T Values (3,'Iqbal','Abdullah')

commit;

Select * from T

Select * from T_Audit

--
Ma Asalaam
Passion 4 Oracle

Package Example in Oracle

As salamo alaikum wa rahmatullah
------------------------------------------

-- Package Example
-- Package Specification

Create or Replace Package Pck_Test
as
procedure PrcPck_Test(eno IN Number);
end Pck_Test;

-- Package Body

Create or Replace Package body Pck_Test
as
Procedure PrcPck_Test(eno IN Number)
as
enm varchar(50);
begin
select ename into enm from emp where empno=eno;
dbms_output.put_line('Employee no ->'||eno|| ' Employee name ->'|| enm);
exception
when others then
dbms_output.put_line('No data found'||enm);
end PrcPck_Test;
end Pck_Test;

-- output

begin
PCK_TEST.PRCPCK_TEST(7788);
end;

--

--
Ma Asalaam
Passion 4 Oracle

Procedure Example in Oracle

As Salamo alaikum wa rahmatullah

Select deptno, ename, sal, sum(sal) over(partition by
deptno order by deptno, ename) CUM_SAL
from
scott.emp;

-- exmaple of anonimous body
Declare
vname varchar2(20);
begin
select ename into vname from emp where empno=7788;
dbms_output.put_line(vname);
end;

-- Example of Implicit cursor
Declare
emp_row emp%RowType;
Begin
Select * into emp_row from emp where empno=7790;
if sql%notfound then
dbms_output.put_line('Record not found');
else
dbms_output.put_line('Record found');
end if;
end;
-- Procedure without parameter

create or replace Procedure Proc_Test
as
v_sal number(18,3);
Begin
Select Sal into v_sal from emp where empno=7788;
dbms_output.put_line(v_sal);
end Proc_Test;

-- Procedure with one IN parameter
create or replace Procedure Proc_Test_Inparameter (eno IN number)
as
vname emp.ename%type;
Begin
select ename into vname from emp where empno=eno;
dbms_output.put_line(vname);
Exception
when others then
dbms_output.put_line('Wrong number');
End Proc_Test_Inparameter;

-- Procedure with one IN one Out parameter

Create or replace procedure Proc_Test_InOutParam(eno IN number, enm OUT varchar2)
As
Begin
Select ename into enm from emp where empno=eno;
Exception
when others then
enm:='no data found';
End Proc_Test_InOutParam;

-- Procdure for INOUT parameter
create or replace procedure Proc_Test_InOutParam1(en IN OUT varchar)
As
Begin
Select job into en from emp where ename=en;
End Proc_Test_InOutParam1;

Declare
nm varchar2(100);
Begin
--Proc_Test;
--Proc_Test_Inparameter(7371);
Proc_Test_InOutParam(7788,nm);
dbms_output.put_line(nm);
nm:='SMITH';
Proc_Test_InOutParam1(nm);
dbms_output.put_line(nm);
End;

--
Ma Asalaam
Passion 4 Oracle

Function Example

As Salamo alaikum wa rahmatullah

-- Function

Create or Replace function Fun_Test
return varchar2
is
nm varchar(20);
begin
select ename into nm from emp where empno=7788;
return nm;
end Fun_Test;

Select Fun_Test() from dual;

-- Function with In Parameter
Create or Replace Function Fun_Test_In_Param(eno IN Number)
return varchar2
is
enm varchar2(100);
begin
select ename into enm from emp where empno=eno;
return enm;
end Fun_Test_In_Param;

Select Fun_Test_In_Param(7788) from dual;

-- Function with IN OUT Parameter

Create or Replace Function Fun_Test_In_Out_Param(eno IN Number,nm OUT varchar)
return varchar2
is
jb varchar2(20);
begin
select ename into nm from emp where empno=eno;
select job into jb from emp where ename=nm;
return jb;
end Fun_Test_In_Out_Param;

Declare
rt varchar2(100);
nm1 varchar(100);
begin
rt:=Fun_Test_In_Out_Param(7788,nm1);
dbms_output.put_line('return value'||rt);
dbms_output.put_line('output parameter'||nm1);
end;

--
Ma Aslaam
Passion 4 Oracle

Wednesday, 20 July 2016

Oracle Packages

As salamo alaikum wa rahmatullah

About the DBMS_PIPE Package

Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a UNIX program.

At the other end of the pipe, you can use the procedures RECEIVE_MESSAGE and UNPACK_MESSAGE to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write a C program to collect data, then send it through pipes to stored procedures in an Oracle database.

About the HTF and HTP Packages

Packages HTF and HTP allow your PL/SQL programs to generate HTML tags.

About the UTL_FILE Package

Package UTL_FILE lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.

When you want to read or write a text file, you call the function FOPEN, which returns a file handle for use in subsequent procedure calls. For example, the procedure PUT_LINE writes a text string and line terminator to an open file, and the procedure GET_LINE reads a line of text from an open file into an output buffer.

About the UTL_HTTP Package

Package UTL_HTTP allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. It can retrieve data from the Internet or call Oracle Web Server cartridges. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in hypertext markup language (HTML) format.

About the UTL_SMTP Package

Package UTL_SMTP allows your PL/SQL programs to send electronic mails (emails) over Simple Mail Transfer Protocol (SMTP). The package provides interfaces to the SMTP commands for an email client to dispatch emails to a SMTP server.

Guidelines for Writing Packages

When writing packages, keep them general so they can be reused in future applications. Become familiar with the Oracle-supplied packages, and avoid writing packages that duplicate features already provided by Oracle.

Design and define package specs before the package bodies. Place in a spec only those things that must be visible to calling programs. That way, other developers cannot build unsafe dependencies on your implementation details.

To reduce the need for recompiling when code is changed, place as few items as possible in a package spec. Changes to a package body do not require recompiling calling procedures. Changes to a package spec require Oracle to recompile every stored subprogram that references the package.

Ma Asalaam

Passion 4 Oracle

Tuesday, 19 July 2016

Invoice Reports

As salamo alaikum wa rahmatullah

Invoice Reports (Oracle Payables Implementation)
------------------------------------------------

Invoice Aging Report

>Use this report to view your unpaid invoices
>The report provides information about invoice payments due within four time periods you specify in the Aging Periods windows.

Invoice Audit Listing

>Use the Invoice Audit Listing to audit invoices for duplicates. You should audit invoices periodically to ensure control of invoice payments. You can sort this listing in six differenct ways.

$$ For example, you may want to only audit over $1000. you can specify a minimum invoice amount and sort invoices by amount the supplier name and date.

>You can also use this report to obtain a listing of your invoices type. For example, you can submit the report to obtain a listing of just your expenses report invoices of your payments.

Invoice types - PO matched invoice, Quick Invoice, Manaul invoice or imported invoice from external sources.
>It helps

Invoice History Report

>The Invoice History Report provides information to support the balance due on an invoice.
>It helps you quickly indentify and review a detailed list of all payment activities pertaining to a specific invoice sucan gains , losses and discounts.

>All amounts on the report are shown in the payment currency.

Invoice on Hold Report

> Use the Invoice on Hold Report to identify invoices on hold.
>The report provides you the total number and amount (in your ledger currency) of invoices on hold which can be helpful for your accounts payable metrics.
>Run the Invoice Validation process before submitting this report to obtain the most up-to-date hold information.

Invoice Register

>Use the Invoice Register to review detailed information about invoices.
>Payable ordrs the report by invoice currency and, if you use batch control, by the invoice batch name. Within the currency and batch name, the report orders by the supplier name and invoice number.

Payments Status Report

>Use the Payments Status Report to review the unapplied prepayment and unpaid or partially paid invoices for a supplier. you can compare the invoices and prepayments for a supplier to determine if there are outstanding repayments wich you can apply against unpaid invoices.
>You can submit the report for either Temporary Payment prepayments, or a combination of a specific prepayment type with credit/debit memos and nvoices. You can view the distribution detail for validated and paid prepayment invoices. Payables displays the prepayment item distributions that have an amount remaining greater than zero.

Recurring Invoices Report

>Use this report to review recurring invoice templates you define during a specific time period.You can review this report to determine the amount you have authorized for a recurring invoice template, how much you have released, and the next amount you have scheduled.
>The report also lists the number of periods remaining for a recurring invoice and teh next period you have scheduled.
>The report list recurring invoice templates by supplier and site.
--

Ma Asalaam
Passion 4 Oracle

Invoice Reports