Perhaps replacing the "+" signs with || because Oracle does not use the plus sign for concatenation.

>Here is my code:
>
>

>  DECLARE @date_string CHAR(8), @SQLstring NVARCHAR(MAX), @SQLcmd NVARCHAR(MAX)
>  SET @date_string = CONVERT(CHAR(8), @start_dte, 112)
>  
>  SET @SQLstring = 'SELECT "HSP", '
>  SET @SQLstring = @SQLstring + 'HT.HEAT_NMBR, HT.TAP_DT, HC.CHMCL_ELM_SYMBL, '
>  SET @SQLstring = @SQLstring + 'HC.FINAL_MAX FROM HEAT AS HT JOIN HEAT_CHEM_AIM AS CA '
>  SET @SQLstring = @SQLstring + 'ON HT.ID_HEAT = CA.ID_HEAT '
>  SET @SQLstring = @SQLstring + 'WHERE TO_CHAR(HT.TAP_DT, "YYYYMMDD") >= "' + @date_string
>  SET @SQLstring = @SQLstring + '" AND (HT.HEAT_NMBR LIKE "' + CAST(9 AS CHAR(1)) + '%" OR '
>  SET @SQLstring = @SQLstring + 'HT.HEAT_NMBR LIKE "' + CAST(2 AS CHAR(1)) + '%") AND UPPER(CA.CHMCL_ELM_SYMBL) IN ('
>  SET @SQLstring = @SQLstring + '"S", "NI","MO" ,"CU" ,"PB" ,"SN")'''
>  
>  SET @SQLcmd = 'SELECT * FROM OPENQUERY(HIP, ''' + @SQLstring + ')'
>  INSERT INTO @ChemAims (plant_cde, heat_num, heat_dte, element_cde, chem_max) EXEC(@SQLcmd)
>

>
>Looks fine to me, but apparently it isn’t because I get this error back:
>
>OLE DB provider "MSDASQL" for linked server "HIP" returned message "[Oracle][ODBC][Rdb]%SQL-F-RTNNOTDEF, function or procedure TO_CHAR is not defined".
>Msg 7321, Level 16, State 2, Line 1
>
>An error occurred while preparing the query "SELECT "HSP", HT.HEAT_NMBR, HT.TAP_DT, HC.CHMCL_ELM_SYMBL, HC.FINAL_MAX FROM HEAT AS HT JOIN HEAT_CHEM_AIM AS CA ON HT.ID_HEAT = CA.ID_HEAT WHERE TO_CHAR(HT.TAP_DT, "YYYYMMDD") > "20120602" AND (HT.HEAT_NMBR LIKE "9%" OR HT.HEAT_NMBR LIKE "2%") AND UPPER(CA.CHMCL_ELM_SYMBL) IN ("S", "NI","MO" ,"CU" ,"PB" ,"SN")" for execution against OLE DB provider "MSDASQL" for linked server "HIP".