Variables in PL/SQL

Question

I am working on a rather large SQL script to be used with Oracle, but I'm running into an issue. First, let me outline how the script operates.

1. Declare variables
   • `CUSTOMERID NUMBER;`
   • `SERVERID NUMBER;`
2. Create a customer if it doesn't exist
3. `SELECT ID INTO CUSTOMERID FROM CUSTOMER WHERE NAME = 'The Customer I just Inserted';`
4. Create the server if it doesn't exist, using the `CUSTOMERID` value to relate the server to the customer.
5. `SELECT ID INTO SERVERID WHERE HOSTNAME = 'the.server.i.just.created';`
6. For each service belonging to this server, insert the service using the `SERVERID` value to relate the service to the server.
7. Go to 2

Now this process seems to work well for just one customer with 15 servers, each having 6 services. But as soon as the next customer is introduced, I receive prompts for variable substitution. The way I'm using the variables on my insert is pretty straightforward:

INSERT INTO SERVERS(CUSTOMER_ID, HOSTNAME)
SELECT CUSTOMERID, 'the.server.i.just.created' FROM DUAL
WHERE NOT EXISTS (
    SELECT *
    FROM SERVERS
    WHERE HOSTNAME = 'the.server.i.just.created'
);

I have also attempted using the DECLARE ... BEGIN ... END; method, but I receive the same general results. Some examples I've seen suggest to use the :CUSTOMERID style variables, but those don't seem to work at all, where they are ending up with null values, which shouldn't be happening given the previous queries.

What I am needing help with is in understanding how to achieve this. I have very limited access to the production environment, so anything I do needs to be kept basic (e.g., no new functions, types, or procedures).

Answer 1

I actually stumbled across the answer after beating my head on the desk repeatedly.

Basically, what was happening was some of the customer names had ampersands in them, and the word immediately following the ampersand was attempting to bind as a variable. The solution was to SET DEFINE OFF; and all was well after that.

Thank you all for your time and consideration.

Answer 2

You can avoid some of the requerying using a RETURNING clause. For example:

SQL> var v number
SQL> print v
v
---------


SQL> insert into demo1 (col1) values (12345) returning col1 into :v;

1 row inserted

SQL> print v
v
---------
12345

This tends to be cleaner and more controllable in PL/SQL than in in a series of standalone statements called from a script.