Replies Back to Article
Calling an Oracle Stored Procedure
I am not very clear about this method. Actually I have a procedure named proc_exp_comp_dt in which there are 3 IN parameter and 2 OUT parameter. Both OUT parameter returns one- one value. I have to use that value in my ASP page for further processing. Should I need to connect it through command object (as described in Step 3 , method 2) and then use step 4?
Please clearly mention what is to be written in step 4 with some example.I would be very grateful to you...
Let us say, my Input parameters are v_reqno,v_class,v_deptno and Output parameters are v_reqdt and v_time..
Thanks
Hi Surya,
Very nice to see the program unit. Thanks very much. I have further questions to get clarified. How do I use Oracle RecordTypes and TableTypes(having mulitple fields) in my ASP/ColdFusion. I am posting a sample package, which I need to access from my ASP/CFM pages. I haven't posted the method to encrypt the password, as it is not required for this particular instance. If you need any information I can pass it. Thanks.
create table User_Master(
usr_cd VARCHAR2(8),
eml_addrss VARCHAR2(30),
IP_addrss VARCHAR2(30),
frst_nm VARCHAR2(30),
lst_nm VARCHAR2(30),
scl_scrty_cd VARCHAR2(9),
dt_of_brth DATE,
effctv_dt DATE,
trm_dt DATE);
---------------
CREATE TABLE User_Password(
usr_cd VARCHAR2(8),
encrptd_psswrd VARCHAR2(30),
effctv_dt DATE,
trm_dt DATE);
---------------
CREATE OR REPLACE PACKAGE pk_UserMaster IS
TYPE RecordType IS RECORD(
usr_cd VARCHAR2(8),
eml_addrss VARCHAR2(30),
IP_addrss VARCHAR2(30),
frst_nm VARCHAR2(30),
lst_nm VARCHAR2(30),
scl_scrty_cd VARCHAR2(9),
dt_of_brth DATE,
effctv_dt DATE,
trm_dt DATE);
TYPE TableType IS TABLE OF RecordType INDEX BY BINARY_INTEGER;
FUNCTION createUser(i_UserRecord IN RecordType,
i_Password IN VARCHAR2) RETURN BOOLEAN;
PROCEDURE getUserDetails(i_UserCode IN VARCHAR2,
o_UserRecord OUT RecordType);
PROCEDURE getAllUserDetails(o_UserDetailTable OUT TableType);
END pk_UserMaster;
/
CREATE OR REPLACE PACKAGE BODY pk_UserMaster IS
FUNCTION createUser(i_UserRecord IN RecordType,
i_Password IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
INSERT INTO User_Master
(usr_cd
,eml_addrss
,IP_addrss
,frst_nm
,lst_nm
,scl_scrty_cd
,dt_of_brth
,effctv_dt
,trm_dt) VALUES
(i_UserRecord.usr_cd
,i_UserRecord.eml_addrss
,i_UserRecord.IP_addrss
,i_UserRecord.frst_nm
,i_UserRecord.lst_nm
,i_UserRecord.scl_scrty_cd
,i_UserRecord.dt_of_brth
,i_UserRecord.effctv_dt
,NULL);
INSERT INTO User_Password
(usr_cd
,encrptd_psswrd
,effctv_dt
,trm_dt) VALUES
(i_UserRecord.usr_cd
,i_Password
,i_UserRecord.effctv_dt
,NULL);
RETURN TRUE;
END;
----------------
PROCEDURE getUserDetails(i_UserCode IN VARCHAR2,
o_UserRecord OUT RecordType) IS
Cursor c_User(p_UserCode VARCHAR2) IS SELECT
usr_cd
,eml_addrss
,IP_addrss
,frst_nm
,lst_nm
,scl_scrty_cd
,dt_of_brth
,effctv_dt
,trm_dt
FROM User_Master
WHERE usr_cd = p_UserCode;-- Returns 1 or 0 records.
l_Index BINARY_INTEGER := 0;
BEGIN
FOR l_UserRecord IN c_User(i_UserCode)
LOOP
l_Index := l_Index + 1;
o_UserRecord.usr_cd := l_UserRecord.usr_cd;
o_UserRecord.eml_addrss := l_UserRecord.eml_addrss;
o_UserRecord.IP_addrss := l_UserRecord.IP_addrss;
o_UserRecord.frst_nm := l_UserRecord.frst_nm;
o_UserRecord.lst_nm := l_UserRecord.lst_nm;
o_UserRecord.scl_scrty_cd := l_UserRecord.scl_scrty_cd;
o_UserRecord.dt_of_brth := l_UserRecord.dt_of_brth;
o_UserRecord.effctv_dt := l_UserRecord.effctv_dt;
o_UserRecord.trm_dt := l_UserRecord.trm_dt;
END LOOP;
END;
----------------
PROCEDURE getAllUserDetails(o_UserDetailTable OUT TableType) IS
Cursor c_Users IS SELECT
usr_cd
,eml_addrss
,IP_addrss
,frst_nm
,lst_nm
,scl_scrty_cd
,dt_of_brth
,effctv_dt
,trm_dt
FROM User_Master;
l_Index BINARY_INTEGER := 0;
BEGIN
FOR l_UserRecord IN c_Users
LOOP
l_Index := l_Index + 1;
o_UserDetailTable(l_Index).usr_cd := l_UserRecord.usr_cd;
o_UserDetailTable(l_Index).eml_addrss := l_UserRecord.eml_addrss;
o_UserDetailTable(l_Index).IP_addrss := l_UserRecord.IP_addrss;
o_UserDetailTable(l_Index).frst_nm := l_UserRecord.frst_nm;
o_UserDetailTable(l_Index).lst_nm := l_UserRecord.lst_nm;
o_UserDetailTable(l_Index).scl_scrty_cd := l_UserRecord.scl_scrty_cd;
o_UserDetailTable(l_Index).dt_of_brth := l_UserRecord.dt_of_brth;
o_UserDetailTable(l_Index).effctv_dt := l_UserRecord.effctv_dt;
o_UserDetailTable(l_Index).trm_dt := l_UserRecord.trm_dt;
END LOOP;
END;
END pk_UserMaster;
/