Design Advice for OracleScript

[George Spears]

Hi,

I am using OracleScript to execute a set of commands. What I need to be able to do is to generate certain types of pseudo-random data, for example RAND_CHAR_10 would be a 10 character random string.

I need to be able to allow the end user to write the SQL for the OracleScript component, such as

Insert into X values (&rand_char_10);

and then have the Oracle script component generate a 10 character string, and do the variable substitution.

Where I'm having the problem is determining how to exchange '&rand_char_10' for 'XYKIuj7R3S' or whatever the random value is.

What should I be looking at?

Thanks

 

[jpickup]

You will probably want to parse the Lines of the OracleScript component before executing it so that you can perform the substitution using Delphi. Writing parsers can be fairly complex if you want to do it properly (look for LEX and YACC for Delphi if you want to go down this route) but in this case you might well be able to get away with searching for some token markers (&, ' ', ',', ')', etc.)

You could also write some Oracle functions that perform the generation using the DBMS_RANDOM package. Your users would then write calls to these functions in their scripts rather than embedding tokens.

e.g. create function rand_char(length in integer) return varchar2;
...

insert into X values (rand_char(10));

[This message has been edited by jpickup (edited 18 July 2001).]

 

[George Spears]

Thanks for the reply, but your approach of LEX and YACC seems to be a lot more complicated than it needs to be. OracleScript provides the capability already for substitution variables. Surely there is someplace where I can programmatically set the value of a variable.

So the question still remains: When OracleScript is executing, and comes across a variable that has not been 'DEFINED' in the script, is there an event that gets fires where I can substitute a value for that variable?

Thanks

 

[mwlang]

I don't know about events firing just before a variable's used, but you could write your script per the usual standards....

insert into X values var1, :var2);

insert into Y values var3);

Then generate variables from SQL (on the variable dialog of object inspector).

Then before calling the Execute method, loop through all your variables and set them accordingly...something like:

for i := 0 to aScript.VariableCount - 1 do begin
aScript.SetVariable(aScript.VariableName(i), RandStr(10));
end; { for }

Of course, if you have different variable types, then you'll need to set the Variable type and string size in the object inspector and then use a case statement or if/else/then blocks in your for loop, but at least you don't have to parse the SQL script.