| ORA-01745 invalide host bind variable [message #352410] |
Tue, 07 October 2008 08:52  |
keizersoz
Messages: 16
Registered: April 2006
|
Junior Member |
|
|
Hello all,
Wrote a procedure that should return the value from certain dynamic column& table.
However when I run the statement I receive ORA-01745 invalide host bind variableFollowed by ORA-06512.
Description on the Oracle site is that I'm using a reserved key word for my bind variable. However I'm just using a function parameter?
thanks in adavance
CREATE OR REPLACE FUNCTION RPT_GET_UD_FIELD_VALUE(p_strTableName VARCHAR2, p_strFieldName VARCHAR2, p_strTableID VARCHAR2, p_strTableType VARCHAR2)
RETURN VARCHAR2
IS
strQuery VARCHAR2(500);
strUDI VARCHAR2(10);
strTblColName VARCHAR2(50);
CURSOR cUDI_EXISTS(p_strTableName VARCHAR2, p_strFieldName VARCHAR2) IS
SELECT
COLUMN_NAME
FROM
USER_TAB_COLUMNS
WHERE
TABLE_NAME =p_strTableName
AND COLUMN_NAME = p_strFieldName;
BEGIN
strUDI:='';
FOR C1 IN cUDI_EXISTS(p_strTableName,p_strFieldName) LOOP
IF C1.COLUMN_NAME IS NOT NULL THEN
CASE p_strTableType
WHEN 'IP' THEN strTblColName:='MT_INSTALL_PART_ID';
WHEN 'I' THEN strTblColName:='MT_INSTALLATION_ID';
ELSE strTblColName:='';
END CASE;
strQuery := 'SELECT ' || p_strFieldName || ' ' ||
'FROM ' || p_strTableName ||' '||
'WHERE ' ||strTblColName ||' = :' || p_strTableID ;
EXECUTE IMMEDIATE strQuery
INTO strUDI
USING p_strTableID ; --error is on that bind variable
END IF;
END LOOP;
return strUDI;
END;
/[Updated on: Tue, 07 October 2008 08:55] Report message to a moderator |
|
|
|
|
|
| Re: ORA-01745 invalide host bind variable [message #352415 is a reply to message #352412] |
Tue, 07 October 2008 09:13  |
keizersoz
Messages: 16
Registered: April 2006
|
Junior Member |
|
|
My session is as follows:
I still don't get the error
SQL> CREATE OR REPLACE FUNCTION RPT_GET_UD_FIELD_VALUE(p_strTableName VARCHAR2,
p_strFieldName VARCHAR2, p_strTableID VARCHAR2, p_strTableType VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 strQuery VARCHAR2(500);
5 strUDI VARCHAR2(10);
6 strTblColName VARCHAR2(50);
7 CURSOR cUDI_EXISTS(p_strTableName VARCHAR2, p_strFieldName VARCHAR2) IS
8 SELECT
9 COLUMN_NAME
10 FROM
11 USER_TAB_COLUMNS
12 WHERE
13 TABLE_NAME =p_strTableName
14 AND COLUMN_NAME = p_strFieldName;
15
16 BEGIN
17 strUDI:='';
18 FOR C1 IN cUDI_EXISTS(p_strTableName,p_strFieldName) LOOP
19 IF C1.COLUMN_NAME IS NOT NULL THEN
20 CASE p_strTableType
21 WHEN 'IP' THEN strTblColName:='MT_INSTALL_PART_ID';
22 WHEN 'I' THEN strTblColName:='MT_INSTALLATION_ID';
23 ELSE strTblColName:='';
24 END CASE;
25 strQuery := 'SELECT ' || p_strFieldName || ' ' ||
26 'FROM ' || p_strTableName ||' '||
27 'WHERE ' ||strTblColName ||' = :' || p_strTableID ;
28 EXECUTE IMMEDIATE strQuery
29 INTO strUDI
30 USING p_strTableID;
31 END IF;
32 END LOOP;
33 return strUDI;
34
35 END;
36 /
Functie is aangemaakt.--function created
SQL>
SQL> SELECT
2 INSTAL_PART,
3 LOC.LOCATION_REF,
4 IP_T.REFERENCE,
5 IP_T.MT_PART_TYPE_ID,
6 RPT_GET_UD_FIELD_VALUE(TABLE_NAME,'U_STATUS',IP.MT_INSTALL_PART_ID,'IP')
AS STATUS
7 FROM
8 (SELECT
9 IP.MT_INSTALL_PART_ID,
10 LPAD(' ', (LEVEL - 1 ) * 4, ' ')||'+ '||IP.REFERENCE AS INSTAL_PART,
11 LEVEL AS P_LEVEL,
12 SYS_CONNECT_BY_PATH(LPAD(IP.ORDER_INDEX,4,'0'), '/') AS PATH
13 FROM
14 MT_INSTALLATION_PARTS IP
15 WHERE
16 IP.MT_INSTALLATION_ID='0000049852'
17 START WITH
18 IP.MT_PARENT_INST_PART_ID IS NULL
19 CONNECT BY
20 IP.MT_PARENT_INST_PART_ID=PRIOR IP.MT_INSTALL_PART_ID ) IP_TREE,
21 MT_INSTALLATION_PARTS IP,
22 LOCATIONS LOC,
23 MT_INSTALL_PART_TYPES IP_T,
24 UDI_TABLES UT
25 WHERE
26 IP.MT_INSTALL_PART_ID = IP_TREE.MT_INSTALL_PART_ID AND
27 IP.LOCATION_ID=LOC.LOCATION_ID AND
28 IP.MT_PART_TYPE_ID=IP_T.MT_PART_TYPE_ID AND
29 IP_T.UDI_TABLE_ID=UT.UDI_TABLE_ID(+)
30 ORDER BY PATH
31 ;
RPT_GET_UD_FIELD_VALUE(TABLE_NAME,'U_STATUS',IP.MT_INSTALL_PART_ID,'IP') AS ST
ATUS
*
error in line 6:
.ORA-01745: invalid host/bind variable name
ORA-06512: at "USER.RPT_GET_UD_FIELD_VALUE", line 28
[Updated on: Tue, 07 October 2008 09:18] Report message to a moderator |
|
|
|
| Re: ORA-01745 invalide host bind variable [message #352422 is a reply to message #352415] |
Tue, 07 October 2008 10:07 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You formated your first post, why didn't you format the second one?
25 strQuery := 'SELECT ' || p_strFieldName || ' ' ||
26 'FROM ' || p_strTableName ||' '||
27 'WHERE ' ||strTblColName ||' = :' || p_strTableID ;
should be
...'WHERE ' ||strTblColName ||' = :1';
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
