Re: bind variable problem

Hi everyone,

Actually I am not getting any error message. The query seems to be
executing which I found by querying the v$session
v$sqltext_with_newlines sql and eventually succeeds. However I found
a few things. But not sure if this is oracle related or perl related
stuff.
I have posted the same in comp.databases.oracle.server.

Here is the problem I am facing.

A perl script hangs when executing the following piece of sql

$lsql = "SELECT NVL2(partition_name,
segment_name || ':' || partition_name,
segment_name)
FROM user_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
AND
segment_name NOT IN
(SELECT object_name
FROM recyclebin bin) AND
segment_name NOT IN (?,?,?,?,?) order by desc;

However the query went through fine if
- the "use encoding;" (that enforces utf8 encoding) is commented out
in the script or
- if the bind variables are hard-coded or
- if less than 5 bind variables are used

Execution plan showed that a cartesian join was implemented when use
encoding or no bind varaibles or less than 5 bind variables are used
whereas hash join
was implemented when no encoding is used. Let me know if you need the
execution plans for the scenarios.

What is even more confusing is that the exact same query works in an
environment which uses oracle 9i whereas fails in the env which uses
oracle 10g.

Tried hinting FIRST_ROWS, then NO_MERGE. Didn't help.

Any ideas?

Regards,
Anand.


On Jan 24, 1:19 am, martin.ev...@xxxxxxxxxxxx (Martin Evans) wrote:
Ronald Kimball wrote:

Martin Evans [mailto:martin.ev...@xxxxxxxxxxxx] wrote:
You haven't called $lcsr->bind_param for the parmeters!

You don't need to call bind_param() if you pass the parameter values in the call
to execute(), as Anand is doing.

I know that, sorry, I missed that.

Perhaps if Anand told us what the error is.

Martin
--
Martin J. Evans
Easysoft Limitedhttp://www.easysoft.com


.

Relevant Pages

  • Re: SELECT MAX(...) performance in SQL Server/ODBC
    ... I tried the Profiler and compared the results and execution plans in the two ... executing the query from C++ code by calling SQLExecDirect. ... > Have you tried using SQL Profiler to profile the events on the server? ...
    (microsoft.public.data.odbc)
  • Re: DataAdapter retrieves no row from Oracle left join count query
    ... What results do you get when you try executing the queries below through ... If you run this query in sql plus or similiar tool, ... > suspect this is a bug of the .NET client provider for oracle. ...
    (microsoft.public.dotnet.framework.adonet)
  • Simple Access SQL Query?
    ... executing this query does NOT make it the source for your ... So, after executing your query, your form continues to ... Filter By Form menu button to enter the criteria, ... learning Access SQL, ...
    (microsoft.public.access.queries)
  • Re: SQL/ASP - Timout Problem w/ Particular Statement
    ... inside the stored proc and i was executing one query or the other based ... > My asp page calls a stored procedure passing many parameters. ... > I used the SQL profiler to get the exact stored procedure with all ... > After executing 2-3 times the stored procedure in the Query Analyzer, ...
    (comp.databases.ms-sqlserver)
  • Re: limiting table access and RWOP queries
    ... your code is not executing a stored query. ... Queries that are executed through code, ... > If I log in as the owner of the table, ...
    (microsoft.public.access.security)