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 [...]
create or replace PROCEDURE send_mail (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := ‘192.168.1.21′;
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn, p_message);
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;
/
Now execute the procedure above:
begin send_mail( ’satishg@imageonline.co.in’,
‘rolvy@imageonline.co.in’,
‘Hello Tom’ );
end; /
All the system tables that stores the user defined tables/sequences/object types :
user_types
USER_TYPE_ATTRS
user_sequences
user_tables
USER_TAB_COLUMNS
Code for creating a table for already existing table:
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
Command to rename table :
RENAME HIP_TASK_INFO_satish TO HIP_TASK_INFOÂ ;
Code example for limits implementaion:
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 || ‘, [...]
