How to use sql plus variable in anonymous block

[Thargy]

I am trying to do something apparently simple, but am struggling.

I need to renumber every primary and foreign key in a database. I am using surrogate keys, all integers generated by a trigger which gets the NEXTVAL from a sequence. There is one trigger and matching sequence for every table in the database. All key fields end in '_ID', and can therefore be identified from USER_TAB_COLUMNS.

I am attempting this via sql plus, as I am required to deliver a scripted solution, which requires that the user need have no knowledge of Oracle. The technicalities of doing the renumbering are easy, what I can't do is successfully get the user to enter a number into sql plus, and then use that number in an anonymous pl/sql block.

Here is the relevant part of my script

--Show available departments to user
SELECT DEPARTMENT_ID, CODE, DESCRIPTION
  FROM DEPARTMENT
 ORDER BY DEPARTMENT_ID;
 
PROMPT 'Enter the department number you wish to use:-'
ACCEPT department_number

VAR start_offset NUMBER(1)
BEGIN :start_offset := 5; END;

BEGIN
   FOR X IN
       (SELECT TABLE_NAME, COLUMN_NAME
          FROM USER_TAB_COLUMNS
         WHERE SUBSTR(COLUMN_NAME,-3,3) = '_ID'
           AND SUBSTR(TABLE_NAME,1,2) != 'V_'
         ORDER BY TABLE_NAME, COLUMN_NAME)
   LOOP
EXECUTE IMMEDIATE ( 'UPDATE '||X.TABLE_NAME||'SET '||X.COLUMN_NAME 
                       ||'= ('||X.COLUMN_NAME||'* 100)+'||:start_offset);
   END LOOP;
END;
/
COMMIT;

I want to use the number entered by the user as the variable called start_offset. The above does not work, and I neglected to bring the errors home with me from work - my apologies. Nonetheless, I still post the question.

I suspect that using a bind variable would be more efficient, so I would also welcome advice regarding this.

Note that I have hard-coded start_offset as 5, in order to attempt to solve the problem. It should be whatever value the user has entered.

A debug in progress...

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

I suspect that using a bind variable would be more efficient...

Since this is an anonymous block, Oracle must re-parse the code into executable code anyway, so whether you use a bind variable ("var") or a substitution string ("accept"), it makes virtually no difference in performance.

I have hard-coded start_offset as 5...It should be whatever value the user has entered.

The prompt says, "PROMPT 'Enter the department number you wish to use:-' "...it seems a bit flakey to me to use a "department number" as an offset value. Remember, good Primary Key values should never contain values with any business significance. You are definitely building business significance into your key values.


Regardless, to replace the "5" with the prompted department_number, just say:

...:start_offset := &department_number;...

Let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,

I'll try that at work on Monday. Am I correct in thinking that I heed to something like

--Show available departments to user
SELECT DEPARTMENT_ID, CODE, DESCRIPTION
  FROM DEPARTMENT
 ORDER BY DEPARTMENT_ID;
 
PROMPT 'Enter the department number you wish to use:-'
ACCEPT department_number

VAR start_offset NUMBER(1)
BEGIN :start_offset := &department_number; END;

BEGIN
   FOR X IN
       (SELECT TABLE_NAME, COLUMN_NAME
          FROM USER_TAB_COLUMNS
         WHERE SUBSTR(COLUMN_NAME,-3,3) = '_ID'
           AND SUBSTR(TABLE_NAME,1,2) != 'V_'
         ORDER BY TABLE_NAME, COLUMN_NAME)
   LOOP
EXECUTE IMMEDIATE ( 'UPDATE '||X.TABLE_NAME||'SET '||X.COLUMN_NAME
                       ||'= ('||X.COLUMN_NAME||'* 100)+'||:start_offset);
   END LOOP;
END;
/
COMMIT;

with the assignation of department number to a variable in its own anonymous block (as shown above) or should I include the assignment of the variable in the same block as the cursor for loop?

FYI, the shenanigans is to do with users dropping a late requirement on us. They want to take a standard database, strip it of all data except that for one department and then use it to add site-specific data. They want to do this with several different sites and then merge all the data back together in one big whopper. In order to avoid PK/FK collisions, I am having to renumber all the keys.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Frankly, John, you don't even need the...

VAR start_offset NUMBER(1)
...:start_offset := &department_number;

Simply use your replacement label in your code as follows:

...||'= ('||X.COLUMN_NAME||'* 100)+&department_number')

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Dave,

as you said, it worked just fine without the bind variable.

Thank you for the assistance.

T

Grinding away at things Oracular