CREATE OR REPLACE PROCEDURE “INSTUPDT_EXTENDEDLICENCE” (
tmp_int_subidFrom IN NUMBER,
tmp_subKindid IN NUMBER,
frm_str_subSelected IN VARCHAR2,
frm_str_optionSelected IN VARCHAR2,
tmp_added_by IN CHAR,
tmp_addedby_id IN NUMBER
) IS
– define two dim array TYPE data_t IS TABLE OF NUMBER INDEX BY binary_integer; TYPE array_t IS TABLE OF data_t INDEX BY binary_integer; arrPriv array_t;
kindid VARCHAR2(50); extlicenceid NUMBER(11); tmp_str_subSelected VARCHAR2(5000); tmp_str_optionSelected VARCHAR2(5000);
tmpExtLicId NUMBER; tmpDefaultValue VARCHAR2(10);
iPrivId NUMBER(11); iPrivValue [...]
Example for implementation of limit in queries:SELECT temp.*,rownum FROM (
SELECT sp.company_name,
sp.contact_phone,
sp.office_fax,
sp.sub_id,
sb.branch_id,
sb.branch_url,
sp.Office_Address.Address1 || ‘, ‘ || sp.Office_Address.Address2 || ‘, ‘ || sp.Office_Address.Address3 || ‘, ‘
|| sp.Office_Address.City || ‘, ‘ || sp.Office_Address.County as address
FROM subscriber_primary sp,
subscriber_branch sb,
subscriber_info si
WHERE si.sub_id=sp.sub_id AND
sb.sub_id=sp.sub_id AND
si.sub_kind_id=4 AND
sp.primacy_no=1 AND
sb.branch_level = ‘0′
) temp
GROUP BY
temp.contact_phone,
temp.office_fax,
temp.sub_id,
temp.branch_id,
temp.branch_url,
temp.address,
rownum having rownum>=2 AND rownum<4
Example code for dynamically creating table using the tables existing:
CREATE TABLE HIP_TASK_INFO_satish as SELECT HIP_TASK_ID ,
HIP_TASK_ID as HIP_TASK_BUDGET_ID,
HIP_TASK_ID as HIP_DUTY_DETAIL_ID,
IS_ACTIVE,
IS_DELETE,
ADDDT
FROM HIP_TASK_INFO
This is an example of writing an stored procedure in oracle:
CREATE OR REPLACE PROCEDURE “INST_SUB_PRIMARY_DET_SAT” (tmp_int in number) IS
BEGIN
– cursor
DECLARE CURSOR PRIMARY_DET
IS
SELECT
t.PRIMARY_ID p,
t.SUB_ID s,
t.OFFICE_ADDRESS.OFFICE_NO o1,
t.OFFICE_ADDRESS.OFFICE_NAME o2,
t.OFFICE_ADDRESS.ADDRESS1 o3,
t.OFFICE_ADDRESS.ADDRESS2 o4,
t.OFFICE_ADDRESS.ADDRESS3 o5,
t.OFFICE_ADDRESS.CITY o6,
t.OFFICE_ADDRESS.COUNTY o7,
t.OFFICE_ADDRESS.POST_CODE o8,
t.PRIMACY_NO pri,
t.COMPANY_NAME com,
t.CONTACT_NAME cont,
t.CONTACT_MOBILE mob,
t.CONTACT_PHONE phone,
t.CONTACT_EMAIL email,
t.OFFICE_PHONE opho,
t.OFFICE_FAX ofax,
t.OFFICE_EMAIL oemail,
t.IS_ACTIVE act,
t.IS_DELETE del,
t.ADDDT adt,
t.ADDED_BY aby,
t.ADDED_BY_ID aid,
t.EDITDT edt,
t.UPDATE_BY uby,
t.UPDATE_BY_ID ubi
FROM SUBSCRIBER_PRIMARY t;
–cres PRIMARY_DET%ROWTYPE;
BEGIN
FOR cres IN PRIMARY_DET
LOOP
INSERT [...]
