Home » Developer & Programmer » Forms » ORA-01445 on execute_query (Forms 6i)
| ORA-01445 on execute_query [message #396338] |
Mon, 06 April 2009 03:35  |
irlrobins
Messages: 5
Registered: June 2007
|
Junior Member |
|
|
Morning all,
Having an issue with a form (Forms 6i) I'm trying to debug at the moment.
When an execute_query is performed to populate a datablock on the form, I get an ORA-01445: cannot select rowid from a join view without a key-preserved table error.
Now I know a solution is to not select rowid, but it's being selected by the execute_query and not explicitly selected.
The actual SQL statement is
SELECT ROWID,PAY_ADDR_1,PAY_ADDR_2,AMT,PAY_ADDR_3,PAY_ADDR_4,TAX_DEDUCTED_AMT,PTAX_DEDUCTED_AMT,PAYEE,PAYMENT_STATUS_CODE,PAYEE_TYPE,ASSOC_P AYMENT_NO,PAYMENT_NO,PAYMENT_STATUS_DESC,SECTION FROM V_PAYMENT_LIST1 WHERE PAYEE = 'XXXXXXXXX' AND PAYEE_TYPE = 'EMPR' AND PAYMENT_STATUS_CODE = 'PD' AND (SECTION = 'I' OR SECTION IS NULL) order by empe_name asc, empr_name asc, erep_name asc
V_PAYMENT_LIST1 is a view. It's create script is:
DROP VIEW MYSCHEMA.V_PAYMENT_LIST1;
CREATE OR REPLACE FORCE VIEW MYSCHEMA.V_PAYMENT_LIST1
(
PAYMENT_NO,
PAYMENT_STATUS_CODE,
PAYEE,
AMT,
PAYEE_TYPE,
PTAX_DEDUCTED_AMT,
TAX_DEDUCTED_AMT,
PAY_ADDR_1,
PAY_ADDR_2,
PAY_ADDR_3,
PAY_ADDR_4,
ASSOC_PAYMENT_NO,
SECTION,
PAYMENT_STATUS_DESC,
EMPE_NAME,
EMPR_NAME,
EREP_NAME
)
AS
SELECT PAYMENT.PAYMENT_NO,
PAYMENT.PAYMENT_STATUS_CODE,
PAYMENT.PAYEE,
PAYMENT.AMT,
PAYMENT.PAYEE_TYPE,
PAYMENT.PTAX_DEDUCTED_AMT,
PAYMENT.TAX_DEDUCTED_AMT,
PAYMENT.PAY_ADDR_1,
PAYMENT.PAY_ADDR_2,
PAYMENT.PAY_ADDR_3,
PAYMENT.PAY_ADDR_4,
PAYMENT.ASSOC_PAYMENT_NO,
PAYMENT.SECTION,
PAYMENT_STATUS.PAYMENT_STATUS_DESC,
EMPLOYEE_DTL.SURNAME || ', ' || EMPLOYEE_DTL.FIRSTNAME EMPE_NAME,
EMPLOYER_DTL.EMPLOYER_NAME EMPR_NAME,
EMPLOYERREP.EREP_NAME EREP_NAME
FROM PAYMENT,
PAYMENT_STATUS,
MYSCHEMA_employee_lookup EMPLOYEE_DTL,
MYSCHEMA_employer_lookup EMPLOYER_DTL,
MYSCHEMA_EREP_LOOKUP EMPLOYERREP
WHERE PAYMENT.PAYMENT_STATUS_CODE = PAYMENT_STATUS.PAYMENT_STATUS_CODE
AND EMPLOYEE_DTL.SSNUM(+) = PAYMENT.PAYEE
AND EMPLOYER_DTL.REG(+) = PAYMENT.PAYEE
AND EMPLOYERREP.REG_NO(+) = PAYMENT.PAYEE
WITH CHECK OPTION;
DROP PUBLIC SYNONYM V_PAYMENT_LIST1;
CREATE PUBLIC SYNONYM V_PAYMENT_LIST1 FOR MYSCHEMA.V_PAYMENT_LIST1;
GRANT SELECT ON MYSCHEMA.V_PAYMENT_LIST1 TO MYROLE1;
DESC for payment:
Name Null? Type
------------------------------- -------- ----
PAYMENT_NO NOT NULL NUMBER(6)
PAYMENT_STATUS_CODE NOT NULL VARCHAR2(6)
USERID NOT NULL VARCHAR2(10)
PAYEE NOT NULL VARCHAR2(9)
AMT NOT NULL NUMBER(11,2)
PAYEE_TYPE NOT NULL VARCHAR2(4)
PTAX_DEDUCTED_AMT NUMBER(11,2)
TAX_DEDUCTED_AMT NUMBER(11,2)
PAY_ADDR_1 NOT NULL VARCHAR2(35)
PAY_ADDR_2 VARCHAR2(35)
PAY_ADDR_3 VARCHAR2(35)
PAY_ADDR_4 VARCHAR2(35)
ASSOC_PAYMENT_NO NUMBER(6)
SECTION VARCHAR2(1)
LEVY_DEDUCTED_AMT NUMBER(11,2)
DESC for PAYMENT_STATUS:
Name Null? Type
------------------------------- -------- ----
PAYMENT_STATUS_CODE NOT NULL VARCHAR2(6)
PAYMENT_STATUS_DESC NOT NULL VARCHAR2(15)
DESC for MYSCHEMA_employee_lookup
Name Null? Type
------------------------------- -------- ----
EMPLOYEE_ID NUMBER
SSNUM VARCHAR2(12)
FIRSTNAME VARCHAR2(100)
SURNAME VARCHAR2(100)
USER_ID VARCHAR2(30)
DESC for MYSCHEMA_employer_lookup:
Name Null? Type
------------------------------- -------- ----
EMPLOYER_ID NUMBER
REG VARCHAR2(12)
EMPLOYER_NAME VARCHAR2(100)
USER_ID VARCHAR2(30)
DESC for MYSCHEMA_EREP_LOOKUP:
Name Null? Type
------------------------------- -------- ----
USER_ID VARCHAR2(30)
REG_NO VARCHAR2(20)
EREP_NAME VARCHAR2(80)
Is there anyway of getting the execute_query not to select rowid? Or any other way of preventing this error occuring?
Thanks
R
|
|
|
|
|
|
| Re: ORA-01445 on execute_query [message #399231 is a reply to message #396338] |
Tue, 21 April 2009 03:43  |
irlrobins
Messages: 5
Registered: June 2007
|
Junior Member |
|
|
The formatting is not that bad. 
Sorry for delay in replying, only getting back to this now.
I resolved this issue by setting the DML Data Target Type on the datablock from 'table' to 'none'. This eliminated the problem and all seems fine now.
Thank you for your reply.
Robin
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 11:55:08 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
