Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01402
| ORA-01402 [message #39427] |
Tue, 16 July 2002 10:28  |
Do I need a cursor for th
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
I am getting too many rows being fetched. Please help
Thanks
CREATE OR REPLACE PROCEDURE kd144_customer_personal_info
(p_customer_acct_num IN customer_data.customer_acct_num%TYPE default NULL)
IS
v_customer_acct customer_data.customer_acct_num%TYPE;
v_tel_num customer_data.pc_tel_num%TYPE;
v_fname customer_data.pc_fname%TYPE;
v_lname customer_data.pc_lname%TYPE;
v_address1 customer_data.pc_address1%TYPE;
v_address2 customer_data.pc_address2%TYPE;
v_city zipcode.city%TYPE;
v_state zipcode.state%TYPE;
v_zip zipcode.zip%TYPE;
v_email customer_data.email_address%TYPE;
v_legal_con_num customer_data.legal_contract_num%TYPE;
BEGIN
SELECT customer_acct_num, pc_tel_num, pc_fname, pc_lname, pc_address1, pc_address2,
z.city, z.state, z.zip, email_address, legal_contract_num
INTO v_customer_acct, v_tel_num, v_fname, v_lname, v_address1, v_address2, v_city,
v_state, v_zip, v_email, v_legal_con_num
FROM customer_data c, zipcode z
WHERE c.zip = z.zip;
htp.htmlOpen;
htp.headOpen;
htp.title('Customer Personal Info');
htp.headClose;
htp.bodyOpen(cattributes => 'BGCOLOR="669966"');
htp.centerOpen;
htp.header(2, 'Personal Info for '||v_fname||' '||v_lname||'');
htp.line();
htp.header(3, 'This is the current information on record for this customer.');
htp.header(4, 'To make changes, edit the information and select ''Save''.');
htp.p(' FORM TAG REMOVED ACTION="kd144_customer.kd144_update_customer" NAME="Update Customer Form" METHOD=POST>');
htp.formHidden('p_customer_acct_num', v_customer_acct);
htp.tableOpen();
htp.tableRowOpen();
htp.tableData('Telephone Number:');
htp.tableData(htf.formText('p_tel_num', NULL, NULL, v_tel_num));
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('First Name:');
htp.tableData(htf.formText('p_fname', NULL, NULL, v_fname));
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('Last Name:');
htp.tableData(htf.formText('p_lname', NULL, NULL, v_lname));
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('Address1:');
htp.tableData(htf.formText('p_address1', NULL, NULL, v_address1));
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('Address2:');
htp.tableData(htf.formText('p_address2', NULL, NULL, v_address2));
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('City:');
htp.tableData(v_city);
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('State:');
htp.tableData(v_state);
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('Zipcode:');
htp.tableData(htf.formText('p_zip', NULL, NULL, v_zip));
--htp.p(' - <INPUT TYPE="button" NAME="change_zip" VALUE="Zipcode List"onClick="javascript:window.open(''kd144_customer.kd144_showzip?p_customer_acct_num='||v_customer_acct||''', ''customer_zip'',''toolbar=no, status=yes, menubar=no,scrollbars=auto, resizable=yes, width=640, height=480'');"> - ');
htp.tableRowClose;
--htp.tableRowOpen();
--htp.tableData();
--htp.p(' - ');
--htp.p('<INPUT TYPE="BUTTON" VALUE="Submit" onClick="javascript:validateAll(this.form);"><INPUT TYPE="reset" VALUE="Reset"> - ');
--htp.p(' - ');
--htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('Email:');
htp.tableData(htf.formText('p_email', NULL, NULL, v_email));
htp.tableRowClose;
htp.tableRowOpen();
htp.tableData('legcon:');
htp.tableData(htf.formText('p_legcon', NULL, NULL, v_legal_con_num));
htp.tableRowClose;
htp.tableClose;
htp.formClose;
Owa_util.Signature(owa_util.Get_Procedure);
htp.centerClose;
htp.bodyClose;
htp.htmlClose;
EXCEPTION
WHEN OTHERS THEN
DECLARE
--V_SQLERR_CODE variable used to hold the SQLCODE value returned
v_sqlerr_code NUMBER := SQLCODE;
--V_SQLERR_MSG variable used to hold the SQLERRM value - 1st 350 bytes
v_sqlerr_msg VARCHAR2(350) := SQLERRM;
BEGIN
--Display
htp.p('An error occurred in processing, please contact Information System Dept, give them the code below.');
htp.p('SQL Error Code: ' || v_sqlerr_code);
htp.p('SQL Error Message: ' || v_sqlerr_msg);
END;
END;
--
|
|
|
|
| Re: ORA-01402 [message #39428 is a reply to message #39427] |
Tue, 16 July 2002 11:33  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Your SELECT statement is not filtering based on the customer account number passed in to the procedure (via p_customer_acct_num). A SELECT/INTO statement may return at most one row. Without a filter on customer, it is returning every customer in your customer_data table.
The p_customer_acct_num parameter is defined as allowing a null value, but this does not seem to make sense given the rest of the procedure.
It appear that your WHERE clause should be:
WHERE c.customer_acct_num = p_customer_acct_num
AND z.zip = c.zip; |
|
|
|
Goto Forum:
Current Time: Fri Apr 26 00:36:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
