Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01745 invalide host bind variable
ORA-01745 invalide host bind variable [message #352410] Tue, 07 October 2008 08:52 Go to next message
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 #352412 is a reply to message #352410] Tue, 07 October 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session when you received the error.

Regards
Michel

Re: ORA-01745 invalide host bind variable [message #352415 is a reply to message #352412] Tue, 07 October 2008 09:13 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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


Previous Topic: Calling JAVA program from ORACLE database
Next Topic: How to keep sql code
Goto Forum:
  


Current Time: Tue Dec 06 02:38:53 CST 2016

Total time taken to generate the page: 0.16523 seconds