User:Satish gaude
From Wikipedia, the free encyclopedia
-- This is procedure for altering a object type when it has been used in a table with data. -- this is demo procedure for subscriber_primary where the office_address columns type is changed from -- office_address_type to office_address_type2
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 INTO SUBSCRIBER_PRIMARY_test2
VALUES(
cres.p,
cres.s,
cres.pri,
cres.com,
cres.cont,
cres.mob,
cres.phone,
cres.email,
office_address_type2(cres.o1, cres.o2, cres.o3, cres.o4, cres.o5, cres.o6, cres.o7, cres.o8, null),
cres.opho,
cres.ofax,
cres.oemail,
cres.act,
cres.del,
cres.adt,
cres.aby,
cres.aid,
cres.edt,
cres.uby,
cres.ubi
);
END LOOP;
END;
END;
execute begin INST_SUB_PRIMARY_DET_SAT('1');end;
CREATE TABLE "SUBSCRIBER_PRIMARY_TEST2"
("PRIMARY_ID" NUMBER(11) NOT NULL,
"SUB_ID" NUMBER(11) NOT NULL,
"PRIMACY_NO" NUMBER(11),
"COMPANY_NAME" VARCHAR2(100 byte),
"CONTACT_NAME" VARCHAR2(100 byte),
"CONTACT_MOBILE" CHAR(20 byte),
"CONTACT_PHONE" CHAR(20 byte),
"CONTACT_EMAIL" CHAR(50 byte) NOT NULL,
"OFFICE_ADDRESS" "TEENA2"."OFFICE_ADDRESS_TYPE2",
"OFFICE_PHONE" CHAR(20 byte),
"OFFICE_FAX" CHAR(20 byte),
"OFFICE_EMAIL" CHAR(50 byte),
"IS_ACTIVE" CHAR(1 byte) DEFAULT 'y' NOT NULL,
"IS_DELETE" VARCHAR2(15 byte) DEFAULT 'n' NOT NULL,
"ADDDT" DATE DEFAULT sysdate NOT NULL,
"ADDED_BY" CHAR(1 byte),
"ADDED_BY_ID" NUMBER(11),
"EDITDT" DATE,
"UPDATE_BY" CHAR(1 byte),
"UPDATE_BY_ID" NUMBER(11),
CONSTRAINT "CHK_PRIMARY_ADDEDBY2" CHECK(ADDED_BY IN ('a','s','u')),
CONSTRAINT "CHK_PRIMARY_UPDTBYID2" CHECK(UPDATE_BY_ID > '0'),
CONSTRAINT "CHK_SUBPRIME_UPDTBY2" CHECK(UPDATE_BY IN ('a','s','u')),
CONSTRAINT "PK_SUBSCRIBER_EMAIL2" PRIMARY KEY("CONTACT_EMAIL")
);
SELECT
t.OFFICE_ADDRESS.COUNTRY
FROM
SUBSCRIBER_PRIMARY_TEST2 t;
--TWO DIMensional array implementation in ORACLE PLSQL--
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 NUMBER(11); sData VARCHAR2(5000); iPos NUMBER(11); iPos1 NUMBER(11); iCnt NUMBER(3); sData1 VARCHAR2(5000); iPos2 NUMBER(11); iPos3 NUMBER(11);
BEGIN
-- create a two dim array of selected subscribers tmp_str_subSelected := frm_str_subSelected; LOOP -- split the string of selected subscribers - frm_str_subSelected iPos := INSTR(tmp_str_subSelected, ',');
IF (NVL(iPos,0) = 0) THEN
sData:= LTRIM(RTRIM(tmp_str_subSelected));
ELSE
sData:= LTRIM(RTRIM(SUBSTR(tmp_str_subSelected,1,iPos-1)));
END IF;
iPos1 := INSTR(sData, '#');
iPrivId := LTRIM(RTRIM(SUBSTR(sData,1,iPos1-1))); iPrivValue := LTRIM(RTRIM(SUBSTR(sData,iPos1+1)));
-- split
iCnt :=0;
LOOP
iCnt:= iCnt +1;
iPos2 := INSTR(iPrivValue, '|');
IF (NVL(iPos2,0) = 0) THEN
sData1:= LTRIM(RTRIM(iPrivValue));
ELSE
sData1:= LTRIM(RTRIM(SUBSTR(iPrivValue,1,iPos2-1)));
END IF;
arrPriv(iPrivId)(iCnt):= sData1;
EXIT WHEN (iPos2 = 0);
iPrivValue := SUBSTR(iPrivValue, iPos2+1);
END LOOP;
EXIT WHEN (iPos = 0); tmp_str_optionSelected := SUBSTR(tmp_str_subSelected, iPos+1); END LOOP; -- end of array creation iPrivId:=0; iPos:=0; sData:=;
-- get the all the priv and the corr option selected tmp_str_optionSelected := frm_str_optionSelected;
LOOP iPos := INSTR(tmp_str_optionSelected, ',');
IF (NVL(iPos,0) = 0) THEN
sData:= LTRIM(RTRIM(tmp_str_optionSelected));
ELSE
sData:= LTRIM(RTRIM(SUBSTR(tmp_str_optionSelected,1,iPos-1)));
END IF;
iPos1 := INSTR(sData, '#');
iPrivId := LTRIM(RTRIM(SUBSTR(sData,1,iPos1-1))); iPrivValue := LTRIM(RTRIM(SUBSTR(sData,iPos1+1)));
-- check whether there exists a rule or main record in the extended_licence table for the tmp_int_subidFrom and the tmp_subKindid
SELECT extended_licence_id, default_value INTO tmpExtLicId, tmpDefaultValue
FROM EXTENDED_LICENCE
WHERE sub_id_from=tmp_int_subidFrom AND
ext_lic_sub_kind=tmp_subKindid AND
is_active='y' AND
privileges_id=iPrivId AND
is_delete='n';
IF (tmpExtLicId >0) THEN
IF (tmpDefaultValue != iPrivValue) THEN
-- update the default value
UPDATE EXTENDED_LICENCE
SET default_value=iPrivValue
WHERE extended_licence_id=tmpExtLicId;
END IF;
-- delete all the previous records from dependency table extended_licence_sub for this extended licence reocrd
DELETE
FROM extended_licence_sub
WHERE extended_licence_id=tmpExtLicId;
--
ELSE
-- insert a default record
INSERT INTO EXTENDED_LICENCE
(
EXTENDED_LICENCE_ID,
SUB_ID_FROM,
EXT_LIC_SUB_KIND,
PRIVILEGES_ID,
DEFAULT_VALUE,
IS_ACTIVE,
IS_DELETE,
ADDDT,
ADDED_BY,
ADDED_BY_ID
)
VALUES
(
extended_licence_seq.NEXTVAL ,
tmp_int_subidFrom,
tmp_subKindid,
iPrivId,
iPrivValue,
'y',
'n',
SYSDATE,
tmp_added_by,
tmp_addedby_id
);
SELECT extended_licence_seq.CURRVAL INTO tmpExtLicId FROM dual;
END IF;
-- insert the dependency records into the extended_licence_sub table
FOR i IN arrPriv(iPrivId).FIRST..arrPriv(iPrivId).LAST
LOOP
-- insert into extended_licence_sub
INSERT INTO EXTENDED_LICENCE_SUB
(
EXTENDED_LICENCE_ID,
SUB_ID_TO ,
PRIVILEGES_VALUE
)
VALUES
(
tmpExtLicId,
arrPriv(iPrivId)(i),
iPrivValue
);
END LOOP;
EXIT WHEN (iPos = 0);
tmp_str_optionSelected := SUBSTR(tmp_str_optionSelected, iPos+1);
END LOOP;
END;
Code for file Downloading - Force Downloading
header("Content-disposition: attachment; filename=$tmp_str_fileDownload");
header("Content-Type: application/force-download");
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".strlen($csvHead.$csv));
header("Pragma: no-cache");
header("Expires: 0");
print $csvHead.$csv;
