oracle - error with getting input in runtime in sql script -

oracle - error with getting input in runtime in sql script -

i have next running code:

declare rec_present number (10) := 0; begin select count (*) rec_present ref_cross_exchange_rate local_ccy_pk = &localccy , base_ccy_pk = &baseccy , base_date = &basedate; if rec_present = 0 insert ref_cross_exchange_rate ( cross_exchange_rate_pk, base_date , local_ccy_pk , base_ccy_pk ) values ( (select nvl (max (cross_exchange_rate_pk), 0) + 1 ref_cross_exchange_rate ) , &basedate, &localccy, &baseccy ); dbms_output.put_line ('1 record inserted'); else dbms_output.put_line ('record exists'); end if; end; / commit;

but problem have come in same value several times, have done modification follows:

declare rec_present number (10) := 0; bdate date := &basedate; local_ccy number(10,0) := &localccy; base_ccy number(10,0) := &baseccy; exchange_rate number(20,10) := &exchangerate; begin select count (*) rec_present ref_cross_exchange_rate local_ccy_pk = local_ccy , base_ccy_pk = base_ccy , base_date =to_date (bdate) ; if rec_present = 0 insert ref_cross_exchange_rate ( cross_exchange_rate_pk, base_date , local_ccy_pk , base_ccy_pk ) values ( (select nvl (max (cross_exchange_rate_pk), 0) + 1 ref_cross_exchange_rate ) , to_date (bdate), local_ccy, base_ccy ); dbms_output.put_line ('1 record inserted'); else dbms_output.put_line ('record exists'); end if; end; / commit;

but unfortunately not working. tried suggestion this question, not working.

error:

error report:

ora-06550: line 3, column 26: pls-00382: look of wrong type ora-06550: line 3, column 9: pl/sql: item ignored ora-06550: line 13, column 24: pls-00320: declaration of type of look incomplete or malformed ora-06550: line 13, column 24: pl/sql: ora-00904: "bdate": invalid identifier ora-06550: line 8, column 3: pl/sql: sql statement ignored ora-06550: line 40, column 9: pls-00320: declaration of type of look incomplete or malformed ora-06550: line 40, column 9: pl/sql: ora-00904: "bdate": invalid identifier ora-06550: line 15, column 5: pl/sql: sql statement ignored 06550. 00000 - "line %s, column %s:\n%s" *cause: pl/sql compilation error. *action: commited.

added quotes in declare part assign values variables properly.

this should work,

declare rec_present number (10) := 0; bdate date := '&basedate'; local_ccy number(10,0) := '&localccy'; base_ccy number(10,0) := '&baseccy'; exchange_rate number(20,10) := '&exchangerate'; begin select count (*) rec_present ref_cross_exchange_rate local_ccy_pk = local_ccy , base_ccy_pk = base_ccy , base_date =to_date (bdate) ; if rec_present = 0 insert ref_cross_exchange_rate ( cross_exchange_rate_pk, base_date , local_ccy_pk , base_ccy_pk ) values ( (select nvl (max (cross_exchange_rate_pk), 0) + 1 ref_cross_exchange_rate ) , to_date (bdate), local_ccy, base_ccy ); dbms_output.put_line ('1 record inserted'); else dbms_output.put_line ('record exists'); end if; end; / commit;

sql oracle plsql