You Asked
I have written below part of code for writing a dynamic select sql ....but its keep on giving me the below error.
ORA-01006: bind variable does not exist
If print the select_query in the flow ,it printed fine. Please let me know where is the problem in binding the varaibles here......here my intenstion is to prepare a select statement with the where_caluse built and storing the result in mdstatus varaible.
Please suggest.
+++++++++++++++++++++
testlogging(where_clause);
select_query := select_query ||' '|| where_clause;
testlogging(select_query);
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, select_query, dbms_sql.native);
dbms_sql.define_column(cur, 1, mdstatus);
if AANum is not null then
set_clause := set_clause;
where_clause := where_clause || ' and AA_num = :XX1';
end if;
if MMseq is not null then
set_clause := set_clause;
where_clause := where_clause || ' and MM_seq = :XX2';
--testlogging(set_clause);
end if;
if XXBBAAHXXer is not null then
set_clause := set_clause || 'BB_AA_HXXER = :YY3,';
where_clause := where_clause || ' and BB_AA_HXXER = :XX3';
end if;
if XXBBAANumber is not null then
set_clause := set_clause || 'BB_AA_NUMBER = :YY4,';
where_clause := where_clause || ' and BB_AA_NUMBER = :XX4';
end if;
if XXBBCode is not null then
set_clause := set_clause || 'BB_CODE = :YY5,';
where_clause := where_clause || ' and BB_CODE = :XX5';
end if;
if XXBBRRNumber is not null then
set_clause := set_clause || 'BB_RR_NUMBER = :YY6,';
where_clause := where_clause || ' and BB_RR_NUMBER = :XX6';
end if;
if XXDDNumber is not null then
set_clause := set_clause || 'DD_NUMBER = :YY7,';
where_clause := where_clause || ' and DD_NUMBER = :XX7';
end if;
if XXDDExpiryDat is not null then
set_clause := set_clause || 'DD_EXPIRY_DAT =:YY8,';
where_clause := where_clause || ' and DD_EXPIRY_DAT =:XX8';
end if;
if XXDDIINum is not null then
set_clause := set_clause || 'DD_II_NUM = :YY10,';
where_clause := where_clause || ' and DD_II_NUM = :XX10';
end if;
if XXDDIIDat is not null then
set_clause := set_clause || 'DD_II_DAT =:YY9,';
where_clause := where_clause || ' and DD_II_DAT =:XX9';
end if;
if XXMMAttr1 is not null then
set_clause := set_clause || 'MM_ATTR_1 = :YY11,';
where_clause := where_clause || ' and MM_ATTR_1 = :XX11';
end if;
if XXMMAttr2 is not null then
set_clause := set_clause || 'MM_ATTR_2 = :YY12,';
where_clause := where_clause || ' and MM_ATTR_2 = :XX12';
end if;
if XXMMAttr3 is not null then
set_clause := set_clause || 'MM_ATTR_3 = :YY13,';
where_clause := where_clause || ' and MM_ATTR_3 = :XX13';
end if;
if XXMMAttr4 is not null then
set_clause := set_clause || 'MM_ATTR_4 = :YY14,';
where_clause := where_clause || ' and MM_ATTR_4 = :XX14';
end if;
if XXMMAttr5 is not null then
set_clause := set_clause || 'MM_ATTR_5 = :YY15,';
where_clause := where_clause || ' and MM_ATTR_5 = :XX15';
end if;
if XXMMAttr6 is not null then
set_clause := set_clause || 'MM_ATTR_6= :YY16,';
where_clause := where_clause || ' and MM_ATTR_6 = :XX16';
end if;
if AANum is not null then
dbms_sql.bind_variable(cur, 'XX1', AANum);
end if;
if MMseq is not null then
dbms_sql.bind_variable(cur, 'XX2', MMseq);
end if;
if XXBBAANumber is not null then
testlogging('entered into this');
dbms_sql.bind_variable(cur, 'XX4', XXBBAANumber);
dbms_sql.bind_variable(cur, 'YY4', tBBAANumber);
end if;
if XXBBAAHXXer is not null then
testlogging('entered into this1');
dbms_sql.bind_variable(cur, 'XX3', XXBBAAHXXer);
dbms_sql.bind_variable(cur, 'YY3', tBBAAHXXer);
end if;
if XXBBCode is not null then
dbms_sql.bind_variable(cur, 'XX5', XXBBCode);
dbms_sql.bind_variable(cur, 'YY5', tBBCode);
end if;
if XXBBRRNumber is not null then
dbms_sql.bind_variable(cur, 'XX6', XXBBRRNumber);
dbms_sql.bind_variable(cur, 'YY6', tBBRRNumber);
end if;
if XXDDNumber is not null then
dbms_sql.bind_variable(cur, 'XX7', XXDDNumber);
dbms_sql.bind_variable(cur, 'YY7', tDDNumber);
end if;
if XXDDExpiryDat is not null then
dbms_sql.bind_variable(cur, 'XX8', XXDDExpiryDat);
dbms_sql.bind_variable(cur, 'YY8', tDDExpiryDat);
end if;
if XXDDIIDat is not null then
dbms_sql.bind_variable(cur, 'XX9', XXDDIIDat);
dbms_sql.bind_variable(cur, 'YY9', tDDIIDat);
end if;
if XXDDIINum is not null then
dbms_sql.bind_variable(cur, 'XX10', XXDDIINum);
dbms_sql.bind_variable(cur, 'YY10', tDDIINum);
end if;
if XXMMAttr1 is not null then
dbms_sql.bind_variable(cur, 'XX11', XXMMAttr1);
dbms_sql.bind_variable(cur, 'YY11', tMMAttr1);
end if;
if XXMMAttr2 is not null then
dbms_sql.bind_variable(cur, 'XX12', XXMMAttr2);
dbms_sql.bind_variable(cur, 'YY12', tMMAttr2);
end if;
if XXMMAttr3 is not null then
dbms_sql.bind_variable(cur, 'XX13', XXMMAttr3);
dbms_sql.bind_variable(cur, 'YY13', tMMAttr3);
end if;
if XXMMAttr4 is not null then
dbms_sql.bind_variable(cur, 'XX14', XXMMAttr4);
dbms_sql.bind_variable(cur, 'YY14', tMMAttr4);
end if;
if XXMMAttr5 is not null then
dbms_sql.bind_variable(cur, 'XX15', XXMMAttr5);
dbms_sql.bind_variable(cur, 'YY15', tMMAttr5);
end if;
if XXMMAttr6 is not null then
dbms_sql.bind_variable(cur, 'XX16', XXMMAttr6);
dbms_sql.bind_variable(cur, 'YY16', tMMAttr6);
end if;
--set_clause := substr(set_clause, 1, length(set_clause)-1);
-- describe defines
--execute immediate select_query into mdstatus;
dummy := dbms_sql.execute(cur);
--
--DBMS_SQL.RETURN_RESULT(rc);
IF DBMS_SQL.FETCH_ROWS(cur)>0 THEN
-- get column values of the row
dbms_sql.column_value(cur, 1, mdstatus);
end if;
testlogging(mdstatus);
dbms_sql.close_cursor(cur);
and Connor said...
It seems to be that you are parsing your SQL before you have built the sql text ?
You can only parse the SQL statement once it is complete, that is, you have added all of the WHERE clause predicates.
So move the DBMS_SQL.PARSE down to just before the DBMS_SQL.EXECUTE, and hence you'll need to split the code up a little
first part: build the where clause, and remember how many binds you have
second part: now do the dbms_sql.parse, and dbms_sql.bind_variable calls
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment