OracleScript and variables error

[peter.baars]

Hi there,

I am trying to run an oracle script with the OracleScript component and am getting a ORA-01008 error. However, I search the variables and set them:

VarList:=TStringList.Create;
     AdvMemo.Clear;
     VarLIst:=FindVariables(OracleScript1.Lines.Text,False);
     For i := 0 to VarList.Count-1 do
     begin
          if InputQuery('Variabele waarde opgeven',VarList.Strings[i],variabel) then
          begin
               OracleScript1.SetVariable(VarList.Strings[i],variabel);
               ShowMessage(OracleScript1.GetVariable(VarList.Strings[i]));
          end;
     end;

I get the InputQuery stating that I have to fill in HEFFINGJAAR and the Showmessage shows the value that I enter...

This is the SQL statement I am trying to run:

select subj.anr                  ANR
,      subj.voorletters          VOORLETTERS
,      subj.voorvoegsel       VOORVOEGSEL
,      subj.subjectnaam     NAAM
,      snaw.verblijfadres     ADRES
,      substr(verblijfwoonpl1,1,4)||' '||substr(verblijfwoonpl1,5,2)   POSTCODE
,      substr(snaw.verblijfwoonpl1,7)  WOONPLAATS
,      kwsv.ddfiat   FIATTEERDATUM
from inm_kwsverzoek  kwsv
,    sjm_subject     subj
,    sjm_naw         snaw
,    inm_kwsbiljet   kwsb
,    hfm_aanslagbiljet   bilj
where subj.subjectnr       = kwsv.subjectnr
and   kwsv.ddfiat is not null
and   kwsv.ddintrekken is  null
and   subj.subjectnr       = snaw.subjectnr
and   snaw.indvervallen    = 'N'
and   kwsv.kwsverzoeknr    = kwsb.kwsverzoeknr
and   kwsb.aanslagbiljetnr = bilj.aanslagbiljetnr
and   bilj.jjheffing       = :HEFFINGJAAR
and   exists (select ''
              from inm_kwsregel            kwsr
              ,    inm_kwsbeslissingcode   besl
              where kwsr.kwsbeslissingcode = besl.kwsbeslissingcode
              and   kwsr.KWSBILJETNR       = kwsb.KWSBILJETNR
              and   besl.uitspraakcode  in ('KWS'))

I just run OracleScript1.Execute and then I am getting the error...
what is going wrong here??? Some help would be appreciated. I am using D7 and DOA 4.0.7

 

[Marco Kalter]

You cannot use bind variables in a TOracleScript, only substitution variables. The TOracleScript.SetVariable procedure call applies to such a substition variable. To fix this replace :HEFFINGJAAR by &HEFFINGJAAR in the script text.

 

[peter.baars]

Marco,

when I do as you suggest, FindVariables finds no variables!

 

[Marco Kalter]

That's correct, FindVariables only finds bind variables. If you want to use FindVariables, replace :varname by &varname before assigning the text to the TOracleScript.