Re: Doubt with WHERE clause on OCIStmtPrepare statement with OCI

On 7 Nov 2006 01:18:36 -0800, albert.medela_at_gmail.com wrote:

>I didn't know I coudn't put the 'to_date' in the bound variable. But I
>also tested what you tell me, and it didn't work, neither. But I don't
>remember what was the error that Oracle gave me. I guess I made another
>mistake.

 OK, but I'd say you should consider trying again in that direction.

>I solved the problem in other way without binding any variable. I
>attached all query in a character string in this way:
>
>string sSQL = "select offc_ipbase,offc_id, offc_name,
>offc_tolerance,offc_inactive, offc_monitor, offc_type, coty_bandwith,
>offc_center_id from nexqoffice, connection_type where offc_type =
>coty_type and offc_lastsincro > ";
>sSQL = sSQL + dSQLDate;
>sSQL = sSQL + " order by offc_id";
>
>where dSQLDate =
>to_date('%04d/%02d/%02d:00:00:00','yyyy/mm/dd:hh24:mi:ss') replacing
>year, month and day :-)

 This is not a good solution because it will hard parse for each different date value - the golden rule with Oracle is to always use bind variables and not to embed variable values in the statement. (There's exceptions to the rule, but they're very rare indeed).

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool