DBMS_SQL error

[rshivagami]

Hi all,

I get this error when I try to execute my procedure using DBMS_SQL package.

ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 795
ORA-06512: at "SYS.DBMS_SQL", line 44
ORA-06512: at "W_PROVISION.CONTACT_NO", line 89
ORA-06512: at line 1

But the bind variable does exists. Does anyone know why I am getting this error?

Shiva.

[akkerend]

Maybe it doesn't exist. Is it possible to post your code?

[rshivagami]

DBMS_SQL package does exist. I have another procedure using the same package and that seems to work fine.

Here is the part of my code using DBMS_SQL package:
--RECIEVE A UNIQUE CURSOR ID
cur_nm := dbms_sql.open_cursor;
sql1 := sql1 || 'update ip_contact_details set';
if medium <> 'EM' then
if countryindex is NOT NULL then
if length(final_no) > 0 then
final_no := final_no || '-' || countryindex;
else
final_no := countryindex;
end if;
end if;
if nodesc is NOT NULL then
if length(final_no) > 0 then
final_no := final_no || '-' || nodesc;
else
final_no := nodesc;
end if;
end if;
if extno is NOT NULL then
if length(final_no) > 0 then
final_no := final_no || '-' || extno;
else
final_no := extno;
end if;
end if;
--dbms_output.put_line (final_no);
if medium = 'PH' then
sql1 := sql1 || ' primary_phone = ';
elsif medium = 'TX' then
sql1 := sql1 || ' telex = ';
elsif medium = 'FX' then
sql1 := sql1 || ' fax = ';
elsif medium = 'CL' then
sql1 := sql1 || ' mobile = ';
elsif medium = 'PG' then
sql1 := sql1 || ' Pager = ';
end if;
sql1 := sql1 || ' :final_no ';
--dbms_output.put_line (sql1);
elsif medium = 'EM' then
final_no := nodesc;
sql1 := sql1 || ' primary_email = :final_no';
end if;
--PARSE THE UPDATE STATEMENT FOR ANY ERRORS
dbms_sql.parse(cur_nm,sql1,dbms_sql.native);
--BIND ALL THE VARIABLES WITH VALUES
dbms_sql.bind_variable(cur_nm, ':final_no', final_no);
--dbms_output.put_line ('sql = '||sql1);
sql1 := sql1 || ' where customer_cd = :orgno and
contact_id = :contactid ';
dbms_sql.bind_variable(cur_nm, ':orgno', orgno);
dbms_sql.bind_variable(cur_nm, ':contactid', contactid);
--dbms_output.put_line ('sql = '||sql1);
--EXECUTE THE STATEMENT
rows_updated := dbms_sql.execute(cur_nm);
dbms_output.put_line ('updated rows = '||rows_updated);
--CLOSE THE CURSOR
dbms_sql.close_cursor(cur_nm);
commit;

I get this error at line 89, which is
dbms_sql.bind_variable(cur_nm, ':orgno', orgno);

but the variable orgno exists and I am able to see the value if I use dbms_output.put_line.
Thanks for any suggestions.

Shiva.

[rshivagami]

Due to the special characters of this editor, wherever I have used a colon+o has come as :o.

[chrisrlong]

Your problem is that you added code to sql1 *after* you parsed it using dbms_sql.parse. This is like adding something to a string after you printed it and then wondering why it wasn't printed. Move the parse call down *after* you finish building the string.

- Chris