How to run PL/SQL
1 Install database
2 go to oracle home directory and create a file with .sql extension and write pl/sql code on it
3 cmd>sqlplus
4. enter sys as sysdba and enter sys password
3 cmd>alter session set container=apexukpdb;
grant connect to ukaccount;
grant all privileges to ukaccount;
sql>@directory\filename.sql enter (best) // or copy and pase code on it and enter(may be face problem)
pl/sql basic syntax
set serveroutput on size 30000; // declare once
DECLARE
message varchar2(20):='Hi Uzzal';
BEGIN
dbms_output.put_line(message);
END;
/
create table using pl/sql
DECLARE
ddl_qry VARCHAR2 (150);
BEGIN
ddl_qry := 'CREATE TABLE apex.uktable(
id NUMBER(3),
tut_name VARCHAR2(50),
primary key(id)
)';
EXECUTE IMMEDIATE ddl_qry;
END;
/
user defined data type in pl/sql
DECLARE
SUBTYPE name IS CHAR(20);
SUBTYPE message IS varchar2(100);
sultan name;
ukmessage message;
BEGIN
sultan:='Reader';
ukmessage:='Welcome to plsql';
dbms_output.put_line('Hello' ||' '|| sultan || ukmessage);
END;
/
variable initialize in pl/sql
DECLARE
a integer:=10;
b integer:=20;
c integer;
d real;
BEGIN
c:=(a+b);
d:=(c *.5);
dbms_output.put_line('The value of c: ' || c);
dbms_output.put_line('The value of d: ' || d);
/* must write with single quatation */
END;
/
nested if-then-else in pl/sql
DECLARE
a number(3) := 100;
b number(3) := 200;
BEGIN
-- check the boolean condition
IF( a = 100 ) THEN
-- if condition is true then check the following
IF( b = 200 ) THEN
-- if condition is true then print the following
dbms_output.put_line('Value of a is 100 and b is 200' );
END IF;
END IF;
dbms_output.put_line('Exact value of a is : ' || a );
dbms_output.put_line('Exact value of b is : ' || b );
END;
/
labeling in loop in pl/sql
DECLARE
i number(1);
j number(1);
BEGIN
<< outer_loop >>
FOR i IN 1..3 LOOP
<< inner_loop >>
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop inner_loop;
END loop outer_loop;
END;
/
array in pl/sql
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;
/
exception handing in pl/sql
DECLARE
c_id ukaccount.ukcourse.id%type := &cc_id;
c_name ukaccount.ukcourse.Name%type;
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name INTO c_name
FROM ukaccount.ukcourse
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
CREATE OR REPLACE function DALERP.get_care_language_translation (p_care_label_id number,P_language_code varchar2) return NVARCHAR2 is
cursor all_fabrication is
select a1.sl_no, a1.ENGLISH,a1.FABRIC_PERCNT
from
(
select a.sl_no,A.LANGUAGE ENGLISH,to_char(a.FABRIC_PERCNT) FABRIC_PERCNT
From CARE_LABEL_FABRICATION a
where A.MASTER_ID=p_care_label_id
and LANGUAGE is not null
union all
select 99 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(IS_PART_OF_SETS,460,460,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
union all
select 100 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(EXCLUSIVE_TRIM,418,418,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
)a1
order by sl_no asc;
cursor all_wash is
select sl_no,ENGLISH
from
(
select a.sl_no,(A.INSTRUCTION) ENGLISH
From CARE_LABEL_WASH_INSTRUCTION a
where A.MASTER_ID=p_care_label_id
and A.INSTRUCTION is not null
)
order by sl_no asc;
v_fabrication NVARCHAR2(1000);
v_translation1 NVARCHAR2(1000);
v_1st_time number;
v_wash NVARCHAR2(1000);
v_translation2 NVARCHAR2(1000);
v_final_translation NVARCHAR2(1000);
begin
v_translation1:='';
v_1st_time:=0;
for j in all_fabrication loop
BEGIN
select decode(j.FABRIC_PERCNT,null,upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999),j.FABRIC_PERCNT||'% ' || upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999))
into v_fabrication
from TESCO_CARE_LANGUAGES_V a where a.ENGLISH=j.english and a.language_code=P_language_code;
EXCEPTION
WHEN OTHERS THEN
v_fabrication :='.....';
END;
if v_1st_time=0 then
v_translation1:=v_fabrication;
v_1st_time:=1;
else
v_translation1:=concat(concat(v_translation1,' / '),v_fabrication);
end if;
end loop;
for i in all_wash loop
BEGIN
select nvl(TRANSLATIONS,'.....') into v_wash from TESCO_CARE_LANGUAGES_V a where trim(upper(a.english))=trim(upper(i.english)) and language_code=P_language_code;
EXCEPTION
WHEN OTHERS THEN
v_wash :='.....';
END;
if v_1st_time=0 then
v_translation2:=v_wash;
v_1st_time:=1;
else
v_translation2:=concat(concat(v_translation2,' / '),v_wash);
end if;
end loop;
v_final_translation:=v_translation1|| v_translation2;
return trim(v_final_translation);
end;
/
implicit cursor in pl/sql
DECLARE
total_rows number(2);
BEGIN
UPDATE ukaccount.ukcourse
SET name ='bangladesh' WHERE ID =1;
IF sql%notfound THEN
dbms_output.put_line('no ukcourse selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' ukcourse selected ');
END IF;
COMMIT;
END;
/
explicit cursor in pl/sql
DECLARE
CURSOR cur_ukcourse is
SELECT id, name FROM ukaccount.ukcourse;
c_id ukaccount.ukcourse.id%type;
c_name ukaccount.ukcourse.name%type;
BEGIN
OPEN cur_ukcourse;
LOOP
FETCH cur_ukcourse into c_id, c_name;
EXIT WHEN cur_ukcourse%notfound;
dbms_output.put_line(c_id || ' ' || c_name);
END LOOP;
CLOSE cur_ukcourse;
END;
/
table base record in pl/sql
DECLARE
course_table_recrord ukaccount.ukcourse%rowtype;
BEGIN
SELECT * into course_table_recrord
FROM ukaccount.ukcourse
WHERE id = 2;
dbms_output.put_line('Course ID: ' || course_table_recrord.id);
dbms_output.put_line('Course Name: ' || course_table_recrord.name);
END;
/
cursor base table record in pl/sql
DECLARE
CURSOR course_cur is
SELECT id, name
FROM ukaccount.ukcourse;
course_record course_cur%rowtype;
BEGIN
OPEN course_cur;
LOOP
FETCH course_cur into course_record;
EXIT WHEN course_cur%notfound;
DBMS_OUTPUT.put_line(course_record.id || ' ' || course_record.name);
END LOOP;
END;
/
user defined record in pl/sql
DECLARE
type ukcourse is record
(
id number(3),
name varchar(50)
);
ukcourse1 ukcourse;
ukcourse2 ukcourse;
BEGIN
-- ukcourse1 specification
ukcourse1.id := 3;
ukcourse1.name := 'India';
-- ukcourse2 specification
ukcourse2.id := 4;
ukcourse2.name := 'Pakistan';
-- Print ukcourse1 record
dbms_output.put_line('id : ' || ukcourse1.id);
dbms_output.put_line('name: '|| ukcourse1.name);
-- Print ukcourse2 2 record
dbms_output.put_line('id : ' || ukcourse2.id);
dbms_output.put_line('name: '|| ukcourse2.name);
END;
/
declare and call function in pl/sql
create or replace function UKACCOUNT.adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/
// call function UKACCOUNT.adder -- done
DECLARE
n3 number(2);
BEGIN
n3 := UKACCOUNT.adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/
declare and call function in one block in pl/sql
// declare and call in one block --problem
DECLARE
a number;
b number;
c number;
create or replace FUNCTION ukaccount.findMax2(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := ukaccount.findMax2(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
CREATE OR REPLACE function DALERP.get_care_language_translation (p_care_label_id number,P_language_code varchar2) return NVARCHAR2 is
cursor all_fabrication is
select a1.sl_no, a1.ENGLISH,a1.FABRIC_PERCNT
from
(
select a.sl_no,A.LANGUAGE ENGLISH,to_char(a.FABRIC_PERCNT) FABRIC_PERCNT
From CARE_LABEL_FABRICATION a
where A.MASTER_ID=p_care_label_id
and LANGUAGE is not null
union all
select 99 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(IS_PART_OF_SETS,460,460,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
union all
select 100 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(EXCLUSIVE_TRIM,418,418,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
)a1
order by sl_no asc;
cursor all_wash is
select sl_no,ENGLISH
from
(
select a.sl_no,(A.INSTRUCTION) ENGLISH
From CARE_LABEL_WASH_INSTRUCTION a
where A.MASTER_ID=p_care_label_id
and A.INSTRUCTION is not null
)
order by sl_no asc;
v_fabrication NVARCHAR2(1000);
v_translation1 NVARCHAR2(1000);
v_1st_time number;
v_wash NVARCHAR2(1000);
v_translation2 NVARCHAR2(1000);
v_final_translation NVARCHAR2(1000);
begin
v_translation1:='';
v_1st_time:=0;
for j in all_fabrication loop
BEGIN
select decode(j.FABRIC_PERCNT,null,upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999),j.FABRIC_PERCNT||'% ' || upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999))
into v_fabrication
from TESCO_CARE_LANGUAGES_V a where a.ENGLISH=j.english and a.language_code=P_language_code;
EXCEPTION
WHEN OTHERS THEN
v_fabrication :='.....';
END;
if v_1st_time=0 then
v_translation1:=v_fabrication;
v_1st_time:=1;
else
v_translation1:=concat(concat(v_translation1,' / '),v_fabrication);
end if;
end loop;
for i in all_wash loop
BEGIN
select nvl(TRANSLATIONS,'.....') into v_wash from TESCO_CARE_LANGUAGES_V a where trim(upper(a.english))=trim(upper(i.english)) and language_code=P_language_code;
EXCEPTION
WHEN OTHERS THEN
v_wash :='.....';
END;
if v_1st_time=0 then
v_translation2:=v_wash;
v_1st_time:=1;
else
v_translation2:=concat(concat(v_translation2,' / '),v_wash);
end if;
end loop;
v_final_translation:=v_translation1|| v_translation2;
return trim(v_final_translation);
end;
/
create procedure to create table in pl/sql
create or replace procedure UKACCOUNT.ddlUktable is
ddl_qry VARCHAR2 (150);
begin
ddl_qry := 'CREATE TABLE UKACCOUNT.uktable(
id NUMBER(3),
tut_name VARCHAR2(50),
primary key(id)
)';
EXECUTE IMMEDIATE ddl_qry;
end ddlUktable;
/
// call procedure apex.ddlUktable // done;
BEGIN
UKACCOUNT.ddlUktable;
END;
/
or
EXEC apex.ddlUktable;
create procedure to update another table data using cursor in pl/sql
set serveroutput on size 30000;
CREATE OR REPLACE PROCEDURE apex.updatetable IS
error_number number;
cursor ci IS
SELECT * FROM uktable;
all_data ci%ROWTYPE;
BEGIN
OPEN ci;
LOOP
fetch ci into all_data;
IF ci%NOTFOUND THEN
error_number :=999;
END IF;
/* or
EXIT WHEN ci%NOTFOUND;
*/
INSERT INTO UKCOURSE(NAME) VALUES(all_data.course_name);
END LOOP;
CLOSE ci;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
create procedure to update another table data using loop in pl/sql
set serveroutput on size 30000;
CREATE OR REPLACE PROCEDURE UKACCOUNT.updatetable IS
error_number number;
BEGIN
for r IN (SELECT * FROM UKACCOUNT.uktable)
LOOP
INSERT INTO UKACCOUNT.UKCOURSE(ID,NAME) VALUES(r.id,r.name);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
// call procedure UKACCOUNT.updatetable // done;
BEGIN
UKACCOUNT.updatetable;
END;
/
trigger in pl/sql
CREATE OR REPLACE TRIGGER display_course_id
BEFORE DELETE OR INSERT OR UPDATE ON ukaccount.ukcourse
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
id_diff number;
BEGIN
id_diff := :NEW.id - :OLD.id;
dbms_output.put_line('Old id: ' || :OLD.id);
dbms_output.put_line('New id: ' || :NEW.id);
dbms_output.put_line('ID difference: ' || id_diff);
END;
/
//note: where inseret id in the table then auto call trigger
package in pl/sql
// package specification -- done by grant connect to ukaccount
CREATE OR REPLACE PACKAGE ukaccount.uk_course AS
PROCEDURE addUkcourse(c_id ukaccount.ukcourse.id%type,c_name ukaccount.ukcourse.name%type);
PROCEDURE delUkcourse(c_id ukaccount.ukcourse.id%type);
PROCEDURE listUkcourse;
END uk_course;
/
// package body -- done by grant connect to ukaccount
CREATE OR REPLACE PACKAGE BODY ukaccount.uk_course AS
PROCEDURE addUkcourse(
c_id ukaccount.ukcourse.id%type,
c_name ukaccount.ukcourse.Name%type
)
IS
BEGIN
INSERT INTO ukaccount.ukcourse (id,name)
VALUES(c_id, c_name);
END addUkcourse;
PROCEDURE delUkcourse(c_id ukaccount.ukcourse.id%type) IS
BEGIN
DELETE FROM ukaccount.ukcourse
WHERE id = c_id;
END delUkcourse;
PROCEDURE listUkcourse IS
CURSOR course_cur is
SELECT id, name
FROM ukaccount.ukcourse;
course_record course_cur%rowtype;
BEGIN
OPEN course_cur;
LOOP
FETCH course_cur into course_record;
EXIT WHEN course_cur%notfound;
DBMS_OUTPUT.put_line(course_record.id || ' ' || course_record.name);
END LOOP;
END listUkcourse;
END uk_course;
/
// calling package
set serveroutput on size 30000;
BEGIN
ukaccount.uk_course.addUkcourse(5, 'Potitish');
ukaccount.uk_course.addUkcourse(6, 'krishna');
commit;
ukaccount.uk_course.listUkcourse;
END;
/
// note: if any code of any block occuur error then package will stop
view in pl/sql
CREATE OR REPLACE FORCE VIEW DALERP.VW_DELIVERY_ITEM
(
ITEM_PID,
ITEM_CODE,
ITEM_NAME,
ITEM_GROUP_PID,
GROUP_CODE,
GROUP_NAME,
MU_PID,
NET_WEIGHT,
MARKET_UNIT_PID,
SHORT_NAME,
UNIT_PID,
SKU,
CTN_UNIT_PID,
CTN,
CONVERSION_VALUE,
RATE,
SKU_RATE,
ISSUE_QTY,
ISSUE_QTY_CTN,
ISSUE_VALUE,
ISSUE_PID,
ISSUE_NO,
ISSUE_DATE,
ISSUE_FROM,
ISSUE_FROM_NAME,
ISSUE_TO,
ISSUE_TO_NAME,
STORE_TYPE_PID,
TYPE_NAME,
DELIVERED_QTY,
DELIVERY_QTY_CTN,
DELIVERED_VALUE,
DELIVERY_PID,
USER_DELIVERY_NO,
DELIVERY_DATE,
DELIVERY_FROM,
DELIVERY_FROM_NAME,
DELIVERY_TO,
DELIVERY_TO_NAME,
ADDRESS,
LOCATION,
CONTACT_NO,
APPROVE_BY,
APPROVE_DT,
DELIVERY_ENT_DATE,
RECOMMEND_BY,
RECOMMEND_DT,
TRANS_TYPE,
USER_ID,
DELIVERY_ENT_BY,
DELIVERY_ENT_BY_NAME,
CUM_DEL_QTY,
PENDING_QTY,
PENDING_QTY_CTN,
PENDING_VALUE,
STATUS,
STATUS_DESC
)
AS
SELECT iss.item_pid,
iss.item_code,
iss.item_name,
iss.item_group_pid,
ig.group_code,
ig.group_name,
iss.mu_pid,
iss.net_weight,
iss.market_unit_pid,
u.short_name,
iss.unit_pid,
u1.short_name sku,
iss.ctn_unit_pid,
u2.short_name ctn,
iss.conversion_value,
iss.rate,
iss.sku_rate,
iss.issue_qty,
iss.issue_qty_ctn,
iss.issue_value,
iss.issue_pid,
iss.issue_no,
iss.issue_date,
iss.issue_from,
su1.store_unit_name issue_from_name,
iss.issue_to,
su.store_unit_name issue_to_name,
iss.store_type_pid,
st.type_name,
dli.delivery_qty delivered_qty,
dli.delivery_qty_ctn,
dli.delivered_value,
dli.delivery_pid,
dli.user_delivery_no,
dli.delivery_date,
dli.delivery_from,
su3.store_unit_name delivery_from_name,
dli.delivery_to,
su2.store_unit_name delivery_to_name,
su2.address,
su2.location,
su2.contact_no,
dli.approve_by,
dli.approve_dt,
dli.ent_date delivery_ent_date,
dli.recommend_by,
dli.recommend_dt,
dli.trans_type,
dli.user_id,
dli.user_id delivery_ent_by,
dut.user_name delivery_ent_by_name,
dli.cum_del_qty,
iss.issue_qty - NVL (dli.cum_del_qty, 0) pending_qty,
iss.issue_qty_ctn
- DECODE (iss.market_unit_pid,
dli.market_unit_pid, NVL (dli.cum_del_qty, 0),
NVL (dli.cum_del_qty, 0) / iss.conversion_value)
pending_qty_ctn,
(iss.issue_qty - NVL (dli.cum_del_qty, 0)) * dli.rt pending_value,
iss.status,
sas.status_desc
FROM (SELECT si.item_pid,
i.item_group_pid,
i.item_code,
i.item_name,
si.mu_pid,
si.net_weight,
si.market_unit_pid,
si.conversion_value,
i.unit_pid,
i.market_unit_pid ctn_unit_pid,
si.rate,
si.sku_rate,
si.issue_qty,
DECODE (si.market_unit_pid,
i.market_unit_pid, si.issue_qty,
si.issue_qty / si.conversion_value)
issue_qty_ctn,
si.issue_qty
* DECODE (si.market_unit_pid,
i.market_unit_pid, si.rate,
si.sku_rate)
issue_value,
si.issue_pid,
sm.user_issue_no issue_no,
sm.issue_date,
sm.issue_from,
sm.issue_to,
sm.store_type_pid,
sm.status
FROM store_issuechd si, store_issuemst sm, item i
WHERE si.issue_pid = sm.pid AND si.item_pid = i.pid) iss,
(SELECT sdgg.pid,
sdgg.issue_pid,
sdgg.item_pid,
sdgg.rate,
sdgg.sku_rate,
sdgg.delivery_qty,
DECODE (sdgg.market_unit_pid,
i.market_unit_pid, sdgg.delivery_qty,
sdgg.delivery_qty / sdgg.conversion_value)
delivery_qty_ctn,
sdgg.delivery_qty
* DECODE (sdgg.market_unit_pid,
i.market_unit_pid, sdgg.rate,
sdgg.sku_rate)
delivered_value,
sdgg.delivery_pid,
sd.user_delivery_no,
sd.delivery_date,
sd.delivery_from,
sd.delivery_to,
sd.approve_by,
sd.approve_dt,
sd.ent_date,
sd.recommend_by,
sd.recommend_dt,
sd.trans_type,
sd.user_id,
(SUM (
sdgg.delivery_qty)
OVER (
PARTITION BY sdgg.issue_pid, sdgg.item_pid, sdgg.rate
ORDER BY sd.delivery_date, sd.user_delivery_no))
cum_del_qty,
DECODE (sdgg.market_unit_pid,
i.market_unit_pid, sdgg.rate,
sdgg.sku_rate)
rt,
i.market_unit_pid
FROM store_deliverygrandgrandchd sdgg,
store_deliverymst sd,
item i
WHERE sdgg.delivery_pid = sd.pid AND sdgg.item_pid = i.pid) dli,
item_group ig,
unit u,
unit u1,
unit u2,
store_unit su,
store_unit su1,
store_unit su2,
store_unit su3,
store_type st,
user_tbl dut,
store_action_status sas
WHERE iss.issue_pid = dli.issue_pid(+)
AND iss.item_pid = dli.item_pid(+)
AND iss.rate = dli.rate(+)
AND iss.item_group_pid = ig.pid
AND iss.store_type_pid = st.pid
AND dli.user_id = dut.user_id(+)
AND iss.market_unit_pid = u.pid
AND iss.unit_pid = u1.pid
AND iss.ctn_unit_pid = u2.pid
AND iss.issue_to = su.pid
AND iss.issue_from = su1.pid
AND dli.delivery_to = su2.pid(+)
AND dli.delivery_from = su3.pid(+)
AND iss.status = sas.id(+);
CREATE OR REPLACE FORCE VIEW DALPDFPRINT.ALL_PO_PDF_V (PO_NO)
AS
SELECT DISTINCT PO_NO
from(
SELECT distinct a.PO_NO
FROM DALPDFPRINT.FULL_PO a
WHERE SUBSTR(TRIM(A.PO_NO),0,3) ='460'
OR SUBSTR(TRIM(A.PO_NO),0,3) ='461'
UNION ALL
SELECT distinct a.PO_NO
FROM DALPDFPRINT.FULL_PO a,DALPDFPRINT.REST_PO B,DALPDFPRINT.PACK P,DALPDFPRINT.FULL_PO_PACKS PP
WHERE A.ID = PP.FULLPO_ID
AND P.ID = PP.PACKS_ID
AND SUBSTR(TRIM(A.PO_NO),0,3) !='460'
AND SUBSTR(TRIM(A.PO_NO),0,3) !='461'
AND SUBSTR(TRIM(A.PO_NO),-3) = SUBSTR(TRIM(B.RETEK_ORDER_NO),-3)
UNION ALL
SELECT distinct a.PO_NO PO_NO
FROM DALPDFPRINT.PARTIAL_PO A,DALPDFPRINT.REST_PO B,DALPDFPRINT.PACK P,DALPDFPRINT.PARTIAL_PO_PACKS PP
WHERE A.ID = PP.PARTIALPO_ID
AND P.ID = PP.PACKS_ID
AND SUBSTR(TRIM(A.PO_NO),-3) = SUBSTR(TRIM(B.RETEK_ORDER_NO),-3)
)
order by PO_NO asc;
To call a view run the following code
select PO_NO from ALL_PO_PDF_V where a.PO_NO ='990-01674';
varray in pl/sql
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE table_type IS VARRAY(5) OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
-- Initialise the collection with two values.
v_tab := table_type(1, 2);
-- Extend the collection with extra values.
<< load_loop >>
FOR i IN 3 .. 5 LOOP
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP load_loop;
-- Can't delete from a VARRAY.
-- v_tab.DELETE(3);
-- Traverse collection
v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/
index by table or associative array Collection in pl/sql
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE table_type IS TABLE OF NUMBER(10)
INDEX BY BINARY_INTEGER;
v_tab table_type;
v_idx NUMBER;
BEGIN
-- Initialise the collection.
<< load_loop >>
FOR i IN 1 .. 5 LOOP
v_tab(i) := i;
END LOOP load_loop;
-- Delete the third item of the collection.
v_tab.DELETE(3);
-- Traverse sparse collection
v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/
nested table collection in pl/sql
// nested table collection -- done
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
-- Initialise the collection with two values.
v_tab := table_type(1, 2);
-- Extend the collection with extra values.
<< load_loop >>
FOR i IN 3 .. 5 LOOP
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP load_loop;
-- Delete the third item of the collection.
v_tab.DELETE(3);
-- Traverse sparse collection
v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/
assignment of same type nested table collection in pl/sql
// assignment of same type nested table collection -- done
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab_1 table_type;
v_tab_2 table_type;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type(1, 2);
-- Assignment works.
v_tab_2 := v_tab_1;
END;
/
// or
SET SERVEROUTPUT ON
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab_1 table_type;
v_tab_2 table_type;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type(1, 2);
v_tab_2 := v_tab_1;
IF v_tab_1 = v_tab_2 THEN
DBMS_OUTPUT.put_line('1: v_tab_1 = v_tab_2');
END IF;
v_tab_1 := table_type(1, 2, 3);
IF v_tab_1 != v_tab_2 THEN
DBMS_OUTPUT.put_line('2: v_tab_1 != v_tab_2');
END IF;
END;
/
assignment of two type nested table collection in pl/sql
// assignment of two type nested table collection -- done
DECLARE
TYPE table_type_1 IS TABLE OF NUMBER(10);
TYPE table_type_2 IS TABLE OF NUMBER(10);
v_tab_1 table_type_1;
v_tab_2 table_type_2;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type_1(1, 2);
-- Assignment causes compilation error.
v_tab_2 := v_tab_1;
END;
/
-- output: problem
// method on collection example -- done
DECLARE
TYPE emp_arr_typ IS TABLE OF VARCHAR2(10);
emp_arr emp_arr_typ;
i INTEGER := 0;
CURSOR emp_cur IS SELECT name FROM ukaccount.ukcourse
WHERE ROWNUM <= 10;
BEGIN
emp_arr := emp_arr_typ();
FOR r_emp IN emp_cur LOOP
emp_arr.EXTEND;
emp_arr(emp_arr.LAST) := r_emp.name;
END LOOP;
-- Traverse sparse collection
i := emp_arr.FIRST;
<< display_loop >>
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The Name is ' || emp_arr(i));
i := emp_arr.NEXT(i);
END LOOP display_loop;
END;
/
