Home » SQL & PL/SQL » SQL & PL/SQL » NEED HELP - ASAP! invalid host/bind variable name
| NEED HELP - ASAP! invalid host/bind variable name [message #36761] |
Tue, 18 December 2001 11:20  |
C PARRISH
Messages: 1
Registered: December 2001
|
Junior Member |
|
|
I get the following error(s)
ORA-01745: invalid host/bind variable name
ORA-06512: at line 24
ORA-06512: at line 122
I don't see anything wrong with my code.
Here it is:
DECLARE
V_PRIME AO_DD1662_PROP_BKUP.PRIME%TYPE:=Null;
V_CONTRACT_ID AO_DD1662_PROP_BKUP.CONTRACT_ID%TYPE:=Null;
V_DELIVERY_ORDER AO_DD1662_PROP_BKUP.DELIVERY_ORDER%TYPE:=Null;
V_CLASS_CODE AO_DD1662_PROP_BKUP.CLASS_CODE%TYPE:=Null;
V_PROP_NBR AO_DD1662_PROP_BKUP.PROP_NBR%TYPE:=Null;
V_CONTRACT_PO AO_DD1662_PROP_BKUP.CONTRACT_PO%TYPE:=Null;
V_AMOUNT_ADD AO_DD1662_PROP_BKUP.AMOUNT_ADD%TYPE:=Null;
V_AMOUNT_DEL AO_DD1662_PROP_BKUP.AMOUNT_DEL%TYPE:=Null;
V_AMOUNT_ENDBAL AO_DD1662_PROP_BKUP.AMOUNT_ENDBAL%TYPE:=Null;
V_BOOK_DATE AO_DD1662_PROP_BKUP.BOOK_DATE%TYPE:=Null;
V_LOCATION AO_DD1662_PROP_BKUP.LOCATION%TYPE:=Null;
V_PROP_DESC AO_DD1662_PROP_BKUP.PROP_DESC%TYPE:=Null;
V_EQTYPE AO_DD1662_PROP_BKUP.EQTYPE%TYPE:=Null;
V_ENDING_DATE AO_DD1662_PROP_BKUP.ENDING_DATE%TYPE:=Null;
V_MAX_ENDING_DATE AO_DD1662_PROP_BKUP.ENDING_DATE%TYPE:=Null;
V_ENDING_DATE_KEY AO_DD1662_PROP_BKUP.ENDING_DATE%TYPE:=Null;
V_PROP_NBR_KEY AO_DD1662_PROP_BKUP.PROP_NBR%TYPE:=Null;
V_CONTRACT_ID_KEY AO_DD1662_PROP_BKUP.CONTRACT_ID%TYPE:=Null;
V_UPDATE_EXCEPTION EXCEPTION;
V_INSERT_EXCEPTION EXCEPTION;
CURSOR TEST_CURSOR IS
SELECT AB.PRIME_NBR
, DECODE(RTRIM(AB.CONTRACT_ID),'ADDENDUM',((RTRIM(AB.CONTRACT_ID))||(SUBSTR(RTRIM(AB.PRIME_NBR),LENGTH(RTRIM(AB.PRIME_NBR)),1))), RTRIM(AB.CONTRACT_ID)) AS CONTRACT_ID
, AB.DELIVERY_ORDER
, AB.CLASS_CODE
, DECODE(AB.PROP_NBR,'ADJUSTMENT',('ADJUSTMENT'||(AB.CLASS_CODE)), AB.PROP_NBR) AS PROP_NBR
, AB.CONTRACT_PO
, NVL(AB.ADDSDIFF,0) AS AMOUNT_ADD
, NVL(AB.DELSDIFF,0) AS AMOUNT_DEL
, NVL(AB.END_BAL,0) AS AMOUNT_ENDBAL
, AB.BOOK_DATE
, AB.LOCATION
, AB.PROP_DESC
, AB.EQTYPE
FROM
(Select 'ADJUSTMENT' AS PROP_NBR
, CalcDiff.CONTRACT_ID
, CalcDiff.PRIME AS PRIME_NBR
, 'ADJ' AS DELIVERY_ORDER
, CalcDiff.CLASS_CODE
, 'ADJUSTMENT' AS CONTRACT_PO
, CalcDiff.ENDING_DATE AS BOOK_DATE
, 'ADJUSTMENT' AS LOCATION
, 'ADJUSTMENT' AS PROP_DESC
, 'GOV' AS EQTYPE
, NVL(DECODE((CalcDiff.DIFF_6_5/(abs(CalcDiff.DIFF_6_5))),-1, ABS(CalcDiff.DIFF_6_5), 0),0) AS ADDSDIFF
, NVL(DECODE((CalcDiff.DIFF_6_5/(abs(CalcDiff.DIFF_6_5))),1, CalcDiff.DIFF_6_5, 0),0) AS DELSDIFF
, 0 AS END_BAL
, CalcDiff.ENDING_DATE
, NVL(CalcDiff.QTY,0) AS QTY
FROM
(
SELECT MAIN.prime
, MAIN.CONTRACT_ID
, MAIN.property_account
, MAIN.CLASS_CODE
, nvl(aodd.end_BALANCE,0) as beg_bal
, nvl(MAIN.tot_add,0) as adds
, nvl(MAIN.tot_del,0) as dels
, nvl(MAIN.tot_endbal,0) as end_bal
, ((nvl(aodd.end_BALANCE,0) + nvl(MAIN.tot_add,0) - nvl(MAIN.tot_del,0)) - nvl(MAIN.tot_endbal,0)) AS DIFF_6_5
, NVL(MAIN.qty,0) AS QTY
, MAIN.ending_date
FROM AO_DD1662 aodd,
(
SELECT SUBMain.prime
, SUBMain.contract_id
, SUBMain.property_account
, SUBMain.CLASS_CODE
, nvl(aodd.end_BALANCE,0) as beg_bal
, nvl(SUBMain.tot_add,0) as tot_add
, nvl(SUBMain.tot_del,0) as tot_del
, nvl(SUBMain.tot_endbal,0) as tot_endbal
, nvl(ctr,0) as qty
, SUBMain.ending_date
FROM ao_dd1662 aodd
,(
select bkup.prime
, bkup.contract_id
, aopa.property_account
, MAX(DISTINCT aopa.CLASS_CODE) AS CLASS_CODE
, sum(decode(bkup.location,'HIS',0,bkup.amount_add)) as tot_add
, sum(bkup.amount_del) as tot_del
, sum(bkup.amount_endbal) as tot_endbal
, bkup.ending_date
, sum(decode(bkup.amount_endbal,0,0, + 1)) as ctr
from ao_dd1662_prop_bkup bkup
, ao_property_account aopa
where ((to_char(bkup.ending_date, 'YYYYMMDD')) = '20020930')
AND
bkup.class_code = aopa.class_code
group by bkup.prime
, bkup.contract_id
, aopa.property_account
, bkup.ending_date
)SUBMain
WHERE SUBMAIN.contract_id = aodd.contract_id(+)
and
SUBMAIN.property_account = aodd.property_account (+)
and
((to_char(SUBMAIN.ending_date, 'YYYYMMDD')) = '20020930')
and
add_months(SUBMAIN.ending_date,-12) = aodd.ending_date(+)
)MAIN
WHERE MAIN.contract_id = aodd.contract_id(+)
and
MAIN.property_account = aodd.property_account (+)
and
((to_char(MAIN.ending_date, 'YYYYMMDD')) = '20020930')
and
add_months(MAIN.ending_date,-12) = aodd.ending_date(+)
)CalcDiff
WHERE CalcDiff.DIFF_6_5 != 0
AND
CalcDiff.PROPERTY_ACCOUNT NOT IN ('LAND', 'REAL', 'GFM', 'GPM')
)AB;
BEGIN
OPEN TEST_CURSOR;
LOOP
V_ENDING_DATE_KEY :=Null;
V_PROP_NBR_KEY :=Null;
V_CONTRACT_ID_KEY :=Null;
FETCH TEST_CURSOR INTO V_PRIME
, V_CONTRACT_ID
, V_DELIVERY_ORDER
, V_CLASS_CODE
, V_PROP_NBR
, V_CONTRACT_PO
, V_AMOUNT_ADD
, V_AMOUNT_DEL
, V_AMOUNT_ENDBAL
, V_BOOK_DATE
, V_LOCATION
, V_PROP_DESC
, V_EQTYPE;
EXIT WHEN TEST_CURSOR%NOTFOUND;
BEGIN
SELECT PROP_NBR,
CONTRACT_ID,
ENDING_DATE
INTO V_PROP_NBR_KEY,
V_CONTRACT_ID_KEY,
V_ENDING_DATE_KEY
FROM AO_DD1662_PROP_BKUP
WHERE (PROP_NBR = V_PROP_NBR AND
CONTRACT_ID = V_CONTRACT_ID AND
ENDING_DATE = '30-SEP-02');
IF (V_PROP_NBR_KEY IS NOT NULL AND
V_CONTRACT_ID_KEY IS NOT NULL AND
V_ENDING_DATE_KEY IS NOT NULL)
THEN RAISE V_UPDATE_EXCEPTION;
END IF;
EXCEPTION
WHEN V_UPDATE_EXCEPTION
THEN
UPDATE AO_DD1662_PROP_BKUP
SET PRIME = V_PRIME
, DELIVERY_ORDER = V_DELIVERY_ORDER
, CLASS_CODE = V_CLASS_CODE
, CONTRACT_PO = V_CONTRACT_PO
, AMOUNT_ADD = V_AMOUNT_ADD
, AMOUNT_DEL = V_AMOUNT_DEL
, AMOUNT_ENDBAL = V_AMOUNT_ENDBAL
, BOOK_DATE = V_BOOK_DATE
, LOCATION = V_LOCATION
, PROP_DESC = V_PROP_DESC
, EQTYPE = V_EQTYPE
WHERE (PROP_NBR = V_PROP_NBR AND
CONTRACT_ID = V_CONTRACT_ID AND
ENDING_DATE = '30-SEP-02');
WHEN NO_DATA_FOUND
then
INSERT INTO AO_DD1662_PROP_BKUP
(PRIME
, CONTRACT_ID
, DELIVERY_ORDER
, CLASS_CODE
, PROP_NBR
, CONTRACT_PO
, AMOUNT_ADD
, AMOUNT_DEL
, AMOUNT_ENDBAL
, BOOK_DATE
, LOCATION
, PROP_DESC
, EQTYPE
, ENDING_DATE)
VALUES
(V_PRIME
, V_CONTRACT_ID
, V_DELIVERY_ORDER
, V_CLASS_CODE
, V_PROP_NBR
, V_CONTRACT_PO
, V_AMOUNT_ADD
, V_AMOUNT_DEL
, V_AMOUNT_ENDBAL
, V_BOOK_DATE
, V_LOCATION
, V_PROP_DESC
, V_EQTYPE
, '30-SEP-02');
END;
COMMIT;
END LOOP;
CLOSE TEST_CURSOR;
END;
----------------------------------------------------------------------
|
|
|
|
|
|
| Re: NEED HELP - ASAP! invalid host/bind variable name [message #37717 is a reply to message #36761] |
Sun, 24 February 2002 12:15  |
Ike
Messages: 1
Registered: February 2002
|
Junior Member |
|
|
insert trigger move_soft after delete on soft
referencing old row as orow
for each row
begin
insert into table hard values(:orow.cid,'hard')
end
I receive an error message, saying that "Bind variable "OROW" not declared."
I get an error saying that bind variable
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 23:56:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
