Example of SQL DATA LOADER

As Salamo alaikum wa rahmatullah

I have created one control file by the name stk.ctl which has following parameter
---------------------------

LOAD DATA
INFILE "D:/upload/stk.csv"
BADFILE "D:/upload/stk.bad"
DISCARDFILE "D:/upload/stk.dsc"
Insert into table tbl_mst_stock 
Fields terminated by "," 

(stock_id,stk_code,stk_desc,created_dt date 'mm/dd/yyyy',updated_dt date 'mm/dd/yyyy')

After that open the command prompt and put the following command
c:/>sqlldr userid=scott@orcl/tiger  control=stk.ctl log=stk.log <etner>

--
MA Asalaam
Passion for Oracle

Create sequence in oracle

As salamo alaikum wa rahmatullah

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

--

Ma Asalaam

Passion for Oracle

Package Example in Oracle

As salamo alaikum wa rahmatullah

create or replace package pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2);
end pck_test;

create or replace package body pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) 
is
begin
insert into tbl_mst_stock(stk_code,stk_desc,created_dt,updated_dt)
values(stc,sdesc,sysdate,sysdate);
commit;
errcode:='0';
errbuf:='row inserted successfully';
exception
when dup_val_on_index then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_inst;

procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) is
begin
update tbl_mst_stock
set stk_code=stc, stk_desc=sdesc, updated_dt=sysdate where stock_id=id;
errcode:='0';
errbuf:='record updated successfully';
exception
when others then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_upd;

procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2) is
begin
delete from tbl_mst_stock 
where stock_id=id;
commit;
errcode:='0';
errbuf:='record deleted successfully';
exception
when others then
errcode:='1';
errbuf:='no row exist';
end prc_del;


end pck_test;

--

Ma Asalaam

Passion for Oracle

Variable example in Oracle SQL

As Salamo alaikum wa rahmatullah

sql> var rc varchar2(100);
sql>begin :rc:='Passion for Oracle'; end;
Sql>/
Sql> pl/sql Procedure successfully completed
Sql> print rc
Sql> rc
--------------
Passion for Oracle 


---
Ma Asalaam
Passion for Oracle

Package With Function

As salamo alaikum wa rahmatullah

Example of package which has function

create or replace package pck_fuc
as
function today_date  return date ;
end pck_fuc;

create or replace package body pck_fuc
as

function today_date return date
is 
dt date;
begin
select sysdate into dt from dual;
return(dt);
end today_date;
end pck_fuc;
show errors

declare 
d date;
begin
d:=pck_fuc.today_date();
dbms_output.put_line(d);

end;

--

Ma Asalaam
Passion for Oracle

Package with Procedure In Out Parameter Example

As salamo alaikum wa rahmatullah,



create or replace package test_proc2
AS
Procedure show_name(ucod in varchar2,fnm out varchar2,lnm out varchar2);
Procedure show_phone(ucod in varchar2,ph out varchar2);
Procedure show_email(ucod in varchar2,eml out varchar2);
end test_proc2;

create or replace package body test_proc2
as
Procedure show_name(ucod in varchar2,fnm out varchar2,lnm out varchar2)
as
Begin
Select Fname,Lname into fnm,lnm from tbl_mst_user where ucode=ucod; 
End show_name;
Procedure show_phone(ucod in varchar2,ph out varchar2)
as 
Begin
Select phone into ph from tbl_mst_user where ucode=ucod;
end show_phone;
Procedure show_email (ucod in varchar2,eml out varchar2)
is
begin
select email into eml from tbl_mst_user where ucode=ucod;
end show_email;
end test_proc2;

set serveroutput on;

declare
f varchar2(100);
l varchar2(100);
begin

TEST_PROC2.SHOW_NAME('u001',f,l);
dbms_output.put_line(f||'  '||l);
end;

declare
ph varchar2(100);
begin

TEST_PROC2.show_phone('u001',ph);
dbms_output.put_line(ph);
end;


declare
em varchar2(100);
begin

TEST_PROC2.Show_email('u001',em);
dbms_output.put_line(em);

end;


--
Ma Asalaam
Passion for Oracle

Simple Cursor example for Chart of Account

As salamo alaikum wa rahmatullah

Set Serveroutput on;
declare
cursor my_cursor is select * from GLFV_charts_of_accounts;
begin
for item in my_cursor
loop
 DBMS_OUTPUT.PUT_LINE(item.CHART_OF_ACCOUNTS_NAME);
end loop;
end;

--
Ma Asalaam
Passion for Oracle