Home » SQL & PL/SQL » SQL & PL/SQL » ora-01745: Invalid host/bind variable name error
ora-01745: Invalid host/bind variable name error [message #44449] Mon, 08 December 2003 16:54 Go to next message
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 #44450 is a reply to message #44449] Mon, 08 December 2003 17:50 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
At first glance, this is a problem (should not have the leading colon):

:'XUSERNAMEX');


Is that just a typo when posting here or is that really in the trigger code?
Re: ora-01745: Invalid host/bind variable name error [message #44453 is a reply to message #44450] Tue, 09 December 2003 09:46 Go to previous messageGo to next message
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
Re: ora-01745: Invalid host/bind variable name error [message #44456 is a reply to message #44453] Tue, 09 December 2003 12:28 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
This one took me a few minutes:

:New.Hire_Date,  -- was missing the comma
Re: ora-01745: Invalid host/bind variable name error [message #44457 is a reply to message #44456] Tue, 09 December 2003 13:26 Go to previous message
Ray
Messages: 58
Registered: January 2000
Member
ughhh.

Man, I can't thank you enough Todd. I feel pretty small for not seeing that. I hope I am able to return the favor sometime.

-Ray
Previous Topic: Workflow
Next Topic: Concatenation with new line between two columns
Goto Forum:
  


Current Time: Mon Aug 26 00:22:15 CDT 2019