| ora-01745: Invalid host/bind variable name error [message #44449] |
Mon, 08 December 2003 16:54  |
Ray
Messages: 58
Registered: January 2000
|
Member |
|
|
Hi,
I'm receiving the following compilation error that I don't see a reason for:
somd FINANCE > CREATE OR REPLACE TRIGGER vw_New_Employee_secure_INSERT
2 INSTEAD OF INSERT ON finance.vw_new_employee_secure
3 REFERENCING NEW AS New
4
5 FOR EACH ROW
6 BEGIN
7
8 INSERT INTO finance.employee VALUES (EmpID_Seq.NEXTVAL,
9 '04',
10 :New.ssn,
11 :New.orgcode,
12 :new.LName,
13 :new.FName,
14 :new.Object_Code,
15 :new.FTE,
16 :new.PRFTE,
17 :new.ExSalary,
18 :new.unit,
19 :New.rating,
20 :New.Hire_Date
21 :new.bargaining_unit,
22 :new.PERCENT_INCREASE,
23 'X',
24 'XXXX',
25 :New.Base_Salary,
26 :New.PrSalary,
27 :New.Reserve_Amt,
28 :new.REMARKS,
29 :new.Job_Code,
30 :new.New_Emp_Flag,
31 :'XUSERNAMEX');
32 END;
33 /
Warning: Trigger created with compilation errors.
somd FINANCE > show errors
Errors for TRIGGER VW_NEW_EMPLOYEE_SECURE_INSERT:
LINE/COL ERROR
-------- -------------------------------------------------------------
3/4 PL/SQL: SQL Statement ignored
26/7 PL/SQL: ORA-01745: invalid host/bind variable name
This is to allow an insert into a table named employee as follows:
somd FINANCE > DESC EMPLOYEE;
Name Null? Type
------------------------------- -------- ----
EMPID NOT NULL NUMBER
FY VARCHAR2(2)
SSN VARCHAR2(9)
ORGCODE VARCHAR2(5)
LNAME VARCHAR2(35)
FNAME VARCHAR2(25)
OBJECT_CODE VARCHAR2(4)
FTE FLOAT(126)
PRFTE FLOAT(126)
EXSALARY FLOAT(126)
UNIT VARCHAR2(2)
RATING VARCHAR2(1)
HIRE_DATE DATE
BARGAINING_UNIT VARCHAR2(1)
PERCENT_INCREASE FLOAT(126)
JOB_GROUP VARCHAR2(1)
JOB_CLASS VARCHAR2(4)
BASE_SALARY FLOAT(126)
PRSALARY FLOAT(126)
RESERVE_AMT FLOAT(126)
REMARKS VARCHAR2(255)
JOB_CODE VARCHAR2(4)
NEW_EMP_FLAG VARCHAR2(1)
USERNAME VARCHAR2(15)
The view that the instead of insert trigger fires on is as follows:
somd FINANCE > DESC VW_NEW_EMPLOYEE_SECURE;
Name Null? Type
------------------------------- -------- ----
EMPID NOT NULL NUMBER
FY VARCHAR2(2)
SSN VARCHAR2(9)
ORGCODE VARCHAR2(5)
LNAME VARCHAR2(35)
FNAME VARCHAR2(25)
OBJECT_CODE VARCHAR2(4)
FTE FLOAT(126)
PRFTE FLOAT(126)
EXSALARY FLOAT(126)
UNIT VARCHAR2(2)
RATING VARCHAR2(1)
HIRE_DATE DATE
BARGAINING_UNIT VARCHAR2(1)
PERCENT_INCREASE FLOAT(126)
JOB_GROUP VARCHAR2(1)
JOB_CLASS VARCHAR2(4)
BASE_SALARY FLOAT(126)
PRSALARY FLOAT(126)
RESERVE_AMT FLOAT(126)
REMARKS VARCHAR2(255)
JOB_CODE VARCHAR2(4)
NEW_EMP_FLAG VARCHAR2(1)
USERNAME VARCHAR2(15)
The only reference I have found for this error states for me to rename the variable in question and retry. I have renamed, and even gone as far as placing a literal value instead of a variable name in the INTO statement and it still chokes.
Any ideas?
Your help is much appreciated!
Regards,
-Ray
|
|
|
|
|
|
| Re: ora-01745: Invalid host/bind variable name error [message #44453 is a reply to message #44450] |
Tue, 09 December 2003 09:46  |
Ray
Messages: 58
Registered: January 2000
|
Member |
|
|
Really was in the trigger code. Great catch!
Leads to another error:
LINE/COL ERROR
-------- --------------------------------------3/4 PL/SQL: SQL Statement ignored
3/24 PL/SQL: ORA-00947: not enough values
I seem to have all destination fields accounted for in the insert statement.
I don't want to impose on everyone to debug my mistakes, though if anyone happens to see anything obvious, any input is appreciated, otherwise I'll keep working on it.
Thanks Todd,
-Ray
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
