This discussion is archived
7 Replies Latest reply: Dec 21, 2012 2:08 PM by rp0428 RSS

Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?

Kim2012 Newbie
Currently Being Moderated
Hi all,

While running PL/SQL in SQL Developer 3.2.09, I got the following error after adding a section of code. Note that nothing I'm doing has anything to do with dynamic SQL (which is what 01006 is supposedly about).

Error report:
ORA-01006: bind variable does not exist
01006. 00000 - "bind variable does not exist"
*Cause:   
*Action:

After commenting out the new chunk of code that caused the error, the error persisted.

After deleting the new code, the error went away.

What the heck!?!? If you have any ideas, please lay 'em on me.

Thanks so much,
Kim

P.S. It's a few hundred lines of code, so I didn't append it to this post and I don't see how to attach a file.
  • 1. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    Marwim Expert
    Currently Being Moderated
    Hello Kim,

    Is this the complete error message, no line number where the error occued? Perhaps it is suppressed by a WHEN OTHERS exception handler.
    It's a few hundred lines of code, so I didn't append it to this post and I don't see how to attach a file.
    Maybe you can add code step by step and then you see where the error occured.

    Does your code contain dynamic SQL? That's what the error description suggests
    A program issued a BIND call for a variable not listed in the associated SQL statement. Only those variables prefixed by either a colon (:) or ampersand (&) in the SQL statement may be referenced in a BIND call
    Nevertheless this is not a question for a forum about the tool {forum:id=260}. Please go to {forum:id=75}.

    Regards
    Marcus
  • 2. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    Kim2012 Newbie
    Currently Being Moderated
    The error gives no indication of where the error occurred in the code.

    To reiterate, there is no dynamic SQL in the code.

    Good thought about the "when others" clause. The code only has one occurrence of that, which is in the primary error handling part of the code. I removed that clause and it had no effect.

    The really strange part is that when I delete the commented code, the error goes away. That makes no sense. Commented code should have no effect, deleted or not. That's what made me think that perhaps this is a SQL Developer quirk.

    Also, is there some size limitation to PL/SQL blocks? This one is an anonymous block and it has about 275 lines of code. I wouldn't think PL/SQL would be limited to such a small amount of code, but I'm grasping at straws here.

    Thanks!
  • 3. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    Let's review a few facts:
    1. You post a question in the sql developer forum that has no apparent relation to sql developer. You could confirm this by doing the test using sql*plus or some other tool.

    2. You provide NO information about what database you are even using. The only clue is an ORA -xxx message that means SOME version of Oracle is involved.

    3. You provide NO information about what the four digit version of Oracle DB is being used.

    4. You provide NO information about what the code in general or what that specific section of the code is even doing. The code could be doing literally anything that Oracle is capable of doing.

    That's a bit like calling a mechanic you don't know, telling them your car is making a funny noise and asking them what the problem with your car is.
    >
    While running PL/SQL in SQL Developer 3.2.09, I got the following error after adding a section of code. Note that nothing I'm doing has anything to do with dynamic SQL (which is what 01006 is supposedly about).

    Error report:
    ORA-01006: bind variable does not exist
    01006. 00000 - "bind variable does not exist"
    *Cause:
    *Action:
    . . .
    The error gives no indication of where the error occurred in the code.
    >
    Your first clue that your troubleshooting method is seriously flawed is when you make statements that aren't supported by any evidence at all but appear to be nothing but your opinion.

    Your second clue is when those statements appear to directly contradict what Oracle is telling you.

    I'm talking about these two statements you make; which you state as if they were universal truths
    >
    nothing I'm doing has anything to do with dynamic SQL
    . . .
    (which is what 01006 is supposedly about).
    >
    If thoe were true then your 'unstated' conclusion appears to be that Oracle is wrong in saying 'bind variable does not exist'

    Sorry - but I would place my money on Oracle.

    I'm sure the above may sound harsh - it is intended to be. My four major rules (there are more) when troubleshooting Oracle problems.

    1. Assume that Oracle is CORRECT when it tells you there is a problem.

    2. Assume that you are WRONG if your opinion conflicts with what Oracle is telling you - see rule #1.

    3. Don't make ANY other assumptions. You can form hypotheses but don't state them as facts until they are proven.

    4. Your 'opinion' is only useful to the extent that it can help you form meaningful hypotheses - see rule #3.
    >
    To reiterate, there is no dynamic SQL in the code.
    >
    To reiterate - if, as you state, that error relates to dynamic SQL and bind variables then it can not be disputed that:

    ORACLE DOESN'T AGREE WITH YOU!

    So let's try it my way and hypothesize that Oracle is correct (see rule #1 above).

    Then by definition this statement by Oracle is correct
    >
    ORA-01006: bind variable does not exist
    >
    And that should immediately raise this question:

    1. where might there be a bind variable that does not exist?

    which leads to a prerequisite question:

    2. where are ALL of the bind variables that might be being used?

    Question #2 is where you need to start your search. Here are at least five possibilities (there are more)

    1. your code - this should be easiest to check and you state that your 'anonymous' block does not have any.

    2. a table trigger - triggers that use the :NEW, :OLD or :PARENT pseudocolumns. Those psuedocolumns are BIND variables and the trigger code that uses them is, by definition, dynamic sql.

    3. a pl/sql package/function/procedure - any of these might be being called from trigger code or a view that is involved in the transaction. Any of these could take a parameter and/or use bind variables and cause your problem if that bind variable does 'not exist'.

    4. a functional index - can also trigger code from #3 above

    5. an audit trigger that logs the audit activity that your user or your code is performing. This code could be trying to use a SYS_CONTEXT environment variable that has not been created and cause a 'bind variable does not exist' error.

    See rule #1 - Oracle is CORRECT. Until you have identified and examined ALL of the code (not just your anonymous block) being executed and ruled it out you should continue with the hypotheses that Oracle is CORRECT.

    In the (extremely) unlikely event that you can show that Oracle is NOT correct you should file a SOR with Oracle.
  • 4. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    Kim2012 Newbie
    Currently Being Moderated
    We may assume that you are knowledgeable about Oracle and PL/SQL.

    We can be absolutely assured that your skills of diplomacy are woefully lacking.

    I'll continue researching the issue elsewhere.
  • 5. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    Marwim Expert
    Currently Being Moderated
    Hello Kim,

    don't give up too fast.

    Your question is far from optimal, but you can still give us the necessary information {message:id=9360002}
    I'll continue researching the issue elsewhere.
    My recommendation: {forum:id=75}

    Above I suggested that you add the new code step by step. The reason is that there are (rare) cases when Oracle does not tell the true error. Therefore your ORA-01006 might be misleading. A syntax error in line 20 can give you a seemingly unrelated error message in a completely different section of your code.

    From the answer of rp0428:
    You could confirm this by doing the test using sql*plus or some other tool.
    This is a valuable hint. Always use SQL*PLUS as reference when you want to confirm an error. Other tools might suppress parts of the error, not only SQL Developer, I have seen it in Toad too.

    Regards
    Marcus
  • 6. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    Marwim Expert
    Currently Being Moderated
    Sorry - but I would place my money on Oracle.
    Don't bet too much money. About a month ago I had a similar situation where Oracle reported an error at a certain line while the error was at least 20 lines above and the message was in no way connected to the real error. That's why I believe that there is no dynamic SQL even when the error message claims something different :-)

    Regards
    Marcus
  • 7. Re: Why an "ORA-01006: bind variable does not exist" error when no dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    >
    We can be absolutely assured that your skills of diplomacy are woefully lacking.

    I'll continue researching the issue elsewhere.
    >
    Sounds like you are taking my comments personally. My comments weren't personal at all; they were strictly technical. I would note that you did not dispute a single comment or suggestion that I made.

    You are free to reseach your issue anywhere you like but technical issues require technical responses. I gave you 4 specific ways that bind variables could be involved without your anonymous block using them at all.

    One of the biggest mistakes people, even experienced ones, make when troubleshooting is to make assumptions about what they think the problem is rather than gather the proper evidence first.

    You stated pretty unequivocally that dynamic sql wasn't used
    >
    Note that nothing I'm doing has anything to do with dynamic SQL (which is what 01006 is supposedly about).
    . . .
    To reiterate, there is no dynamic SQL in the code.
    >
    Those statements are not accurate and I was correct to point that out. I'm sorry you took the correction personally and appear to have gotten your feelings hurt but the statements are misleading at best and totally inaccurate at worst. As I said in my reply even assuming that your anonymous block code does not use bind variables or dynamic sql there are many other ways that the problem might have 'anything to do with dynamic SQL'.

    Marwim is certainly correct that Oracle isn't always right and their error message aren't always specific enough to be very helpful.

    But if you want to be successful diagnosing and resovling Oracle issues you would be well served to start with the hypothesis that Oracle is far more likely to be correct based on the information is had available than you are to be correct given the very limited initial information you are starting with.

    The first goal is always to try to determine why Oracle made the decision it did. That requires gathering as much of the information as possible that Oracle used to make its decision.

    My apologies if you think my comments were personal; they weren't.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points