3

I have a script I'm trying to run via sqlplus. At the top of the script, I have "SET DEFINE OFF" set. However, when I run it, I'm receiving this error:

SP2-0552: Bind variable "CHANDAWALA" not declared.

I believe this is happening because I have a couple update statements that include colons within a string like this:

UPDATE ADDRESS SET ADDRESS_STREET_LINE1 = 'Moh: Chandawala'...
UPDATE ADDRESS SET ADDRESS_STREET_LINE1 = 'Moh:chandawala'...

So it's treating them like bind variables. I was under the impression that SET DEFINE OFF would make it treat anything within a quoted string as characters and not interpret anything within, including perceived bind variables.

This obviously isn't the case - So in this situation, what am I supposed to do? Do I have to escape these manually?

Thanks.

EDIT - The posted question at When or Why to use a "SET DEFINE OFF" in Oracle Database doesn't help me since that person is trying to escape ampersands for variable substitution, and not colons as bind variables. I'm looking for a way to tell the client to ignore anything (including colons) in quoted strings.

EDIT - Okay, so it sounds like Oracle shouldn't be interpreting that as a bind variable within quotes. So now the question is, why is it doing that?

Community
  • 1
  • 1
the-nick-wilson
  • 566
  • 4
  • 18
  • I've seen that article, but there's no mention of bind variables and escaping colons. Unless I'm missing something... – the-nick-wilson May 09 '17 at 17:35
  • 1
    `:chandawala` should not be interpreted as bind variable when enclosed in single quotes. – Nick Krasnov May 09 '17 at 17:47
  • Thanks - Then I guess the question is why is it trying to interpret it as a bind variable then? I do in fact have it enclosed in single quotes. What I pasted in my code block in my question is exactly how my script looks, and there's nowhere else that has the word "chandawala" in the script. – the-nick-wilson May 09 '17 at 17:50
  • Run one of those update statements, not the whole script, spool the result and post it here. Let's take a look at the result. – Nick Krasnov May 09 '17 at 18:01
  • Are you able to show us the whole script, at least up to the point the error occurs; or at least all of the `set` commands (not that I think they can be relevant) and a minimal script that actually shows the same behaviour for you? – Alex Poole May 09 '17 at 18:10
  • Yeah let me see what I can do. Obviously some of the data is private which is why I didn't show all of it before, but I can mask it. – the-nick-wilson May 09 '17 at 18:12
  • 1
    Is it possible that you have the wrong kind of quotes - if you had backticks or smart quotes or something (maybe from copying and pasting a query from Word or similar) then you would get this error. What you've posted is OK, but have you copied that from your code or retyped it for posting? – Alex Poole May 09 '17 at 18:14
  • Nah, what I have in the script itself is the same character (as far as I can tell). I can even copy the statements to SQL Developer and it works just fine. – the-nick-wilson May 09 '17 at 18:22
  • What's strange is when I take everything else out and just leave in those two updates, it works (from sqlplus). I'm going to try a couple more things really quick and get back to you here. – the-nick-wilson May 09 '17 at 18:22
  • Well guys, I have no idea. When I ran the script by putting everything in buffer and executing with a forward slash, it would give that bind error. But if I add semi-colons and execute everything as it goes, I get some "ORA-01756: quoted string not properly terminated" errors. My guess is that I had some single quotes somewhere in there that I didn't escape with dual-single quotes, and that caused a phantom error when running with the slash. – the-nick-wilson May 09 '17 at 19:07
  • PS - I thought through what I'm trying to do, and it wasn't worth it to update the street numbers, nor was it entirely necessary for my case. I just needed to update the cities and states, which were a lot cleaner with no extra quotes or ampersands or anything. When just running the update on those fields, it worked fine. – the-nick-wilson May 09 '17 at 19:08
  • 2
    If the statements work fine by themselves, but not as part of the larger script, the immediate suspect should be an errant quote in the REST of the script (before these UPDATE statements). Very often the result of a name like O'Meara where the apostrophe is not properly escaped. –  May 09 '17 at 19:45
  • I'm more than confident that that's the case. Especially because even when I ran those two statements as part of the script using sqlplus, but took everything else out, they still worked (not just in SQL Developer). But it would've taken forever to find the rogue apostrophe because this was about 2500 update statements. I tried scanning quickly, looking for font changes in Notepad++ (since it'll change the font for strings), but I wasn't seeing it. And this would explain why it was treating a seemingly quoted section as actual SQL with the bind variable. – the-nick-wilson May 09 '17 at 20:24

2 Answers2

1

Set Define Off is a SQLPlus command and it prevents SQLPlus from treating & as a special character. & is also specific to SQLPlus and have no meaning outside it.

When define is on, the SQLPlus replaces the substitution variables with actual values and then pass it to the Oracle SQL engine.

On the other hand, : are real bind variable that only Oracle SQL engine recognises and look for a to-be-bound value. The : are not recognized as bind meta character by SQLPlus and is passed as such to the SQL engine.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • So you're saying that there isn't a way to turn this off, basically? But also, according to the comment by Nicholas Krasnov, it shouldn't be parsing that as a bind variable anyway if it's enclosed in quotes. I'm not sure why that's happening... Thanks. – the-nick-wilson May 09 '17 at 17:55
  • 2
    In your second paragraph, I'd say it "replaces the **substitution** variables" to avoid confusion with bind variables, as they are very different things? – Alex Poole May 09 '17 at 17:58
  • 1
    @AlexPoole You are right. Updated. But not sure why Oracle is treating `:` inside the single quotes as bind variables. I didn't really see that at first. I think there is some problem on OP's part. – Gurwinder Singh May 09 '17 at 18:00
  • I don't doubt there is, but I'd like to find it :) – the-nick-wilson May 09 '17 at 18:07
  • I think as we talked about in the main string of comments above, I must have had a rogue comment somewhere. I took out some superfluous fields from the UPDATE statements and now it's working. – the-nick-wilson May 09 '17 at 20:26
1

I cannot duplicate your issue. I created a table called dually with a column of a single character. When I attempt to run the update statement, no substitution is permitted.

SQL> UPDATE brianl.dually
  2         SET dummy   = ':x';
       SET dummy   = ':x'
                     *
ERROR at line 2:
ORA-12899: value too large for column "BRIANL"."DUALLY"."DUMMY" (actual: 2,
maximum: 1)

Have you tried using concatenation?

UPDATE ADDRESS SET ADDRESS_STREET_LINE1 = 'Moh' || ':' || Chandawala'
Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • I think to duplicate what I think the issue was, would be to try to update two fields, but the value for the first field contains an un-escaped single quote. So then Oracle would think the second field value is actually a bind var. I hope that makes sense (see the comment thread on my original post). Funny you mention the concatenation though, my boss suggested that as well, but I found a workaround before trying it. Anyway thanks for this though. – the-nick-wilson May 10 '17 at 00:26