Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL : ORA-01006: bind variable does not exist (merged) (Oracle R12)
- Dynamic SQL : ORA-01006: bind variable does not exist (merged) [message #655956] Sun, 18 September 2016 12:25 Go to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Hi Friends,

I am using below procedure to execute a dynamic SQL.

PROCEDURE intfc_rec_update
(
p_upd_table IN VARCHAR2,
p_set_col_name1 IN VARCHAR2,
p_set_col_value1 IN VARCHAR2,
p_set_col_name2 IN VARCHAR2,
p_set_col_value2 IN VARCHAR2,
p_set_col_name3 IN VARCHAR2,
p_set_col_value3 IN VARCHAR2,
p_set_col_name4 IN VARCHAR2,
p_set_col_value4 IN VARCHAR2,
p_set_col_name5 IN VARCHAR2,
p_set_col_value5 IN VARCHAR2,
p_set_col_name6 IN VARCHAR2,
p_set_col_value6 IN VARCHAR2,
p_set_col_name7 IN VARCHAR2,
p_set_col_value7 IN VARCHAR2,
p_set_col_name8 IN VARCHAR2,
p_set_col_value8 IN VARCHAR2,
p_set_col_name9 IN VARCHAR2,
p_set_col_value9 IN VARCHAR2,
p_where_col_name1 IN VARCHAR2,
p_where_col_value1 IN VARCHAR2,
p_where_col_name2 IN VARCHAR2,
p_where_col_value2 IN VARCHAR2,
p_where_col_name3 IN VARCHAR2,
p_where_col_value3 IN VARCHAR2,
p_where_col_name4 IN VARCHAR2,
p_where_col_value4 IN VARCHAR2,
p_where_col_name5 IN VARCHAR2,
p_where_col_value5 IN VARCHAR2,
p_reterror OUT VARCHAR2,
p_retcode OUT NUMBER
)
IS
l_plsql_block VARCHAR2 (2000);
l_upd_table VARCHAR2 (2000);
l_set_col_name1 VARCHAR2 (2000);
l_set_col_value1 VARCHAR2 (2000);
l_set_col_name2 VARCHAR2 (2000);
l_set_col_value2 VARCHAR2 (2000);
l_set_col_name3 VARCHAR2 (2000);
l_set_col_value3 VARCHAR2 (2000);
l_set_col_name4 VARCHAR2 (2000);
l_set_col_value4 VARCHAR2 (2000);
l_set_col_name5 VARCHAR2 (2000);
l_set_col_value5 VARCHAR2 (2000);
l_set_col_name6 VARCHAR2 (2000);
l_set_col_value6 VARCHAR2 (2000);
l_set_col_name7 VARCHAR2 (2000);
l_set_col_value7 VARCHAR2 (2000);
l_set_col_name8 VARCHAR2 (2000);
l_set_col_value8 VARCHAR2 (2000);
l_set_col_name9 VARCHAR2 (2000);
l_set_col_value9 VARCHAR2 (2000);
l_where_col_name1 VARCHAR2 (2000);
l_where_col_value1 VARCHAR2 (2000);
l_where_col_name2 VARCHAR2 (2000);
l_where_col_value2 VARCHAR2 (2000);
l_where_col_name3 VARCHAR2 (2000);
l_where_col_value3 VARCHAR2 (2000);
l_where_col_name4 VARCHAR2 (2000);
l_where_col_value4 VARCHAR2 (2000);
l_where_col_name5 VARCHAR2 (2000);
l_where_col_value5 VARCHAR2 (2000);
l_quote VARCHAR2 (1):= CHR(39);
BEGIN

l_upd_table := p_upd_table ;
l_set_col_name1 := p_set_col_name1 ;
l_set_col_value1 := p_set_col_value1 ;
l_set_col_name2 := p_set_col_name2 ;
l_set_col_value2 := p_set_col_value2 ;
l_set_col_name3 := p_set_col_name3 ;
l_set_col_value3 := p_set_col_value3 ;
l_set_col_name4 := p_set_col_name4 ;
l_set_col_value4 := p_set_col_value4 ;
l_set_col_name5 := p_set_col_name5 ;
l_set_col_value5 := p_set_col_value5 ;
l_set_col_name6 := p_set_col_name6 ;
l_set_col_value6 := p_set_col_value6 ;
l_set_col_name7 := p_set_col_name7 ;
l_set_col_value7 := p_set_col_value7 ;
l_set_col_name8 := p_set_col_name8 ;
l_set_col_value8 := p_set_col_value8 ;
l_set_col_name9 := p_set_col_name9 ;
l_set_col_value9 := p_set_col_value9 ;
l_where_col_name1 := p_where_col_name1 ;
l_where_col_value1 := p_where_col_value1 ;
l_where_col_name2 := p_where_col_name2 ;
l_where_col_value2 := p_where_col_value2 ;
l_where_col_name3 := p_where_col_name3 ;
l_where_col_value3 := p_where_col_value3 ;
l_where_col_name4 := p_where_col_name4 ;
l_where_col_value4 := p_where_col_value4 ;
l_where_col_name5 := p_where_col_name5 ;
l_where_col_value5 := p_where_col_value5 ;


IF ( (l_where_col_name1 IS NULL)
AND (l_where_col_value1 IS NULL)
AND (l_where_col_name2 IS NULL)
AND (l_where_col_value2 IS NULL)
AND (l_where_col_name3 IS NULL)
AND (l_where_col_value3 IS NULL)
AND (l_where_col_name4 IS NULL)
AND (l_where_col_value4 IS NULL)
AND (l_where_col_name5 IS NULL)
AND (l_where_col_value5 IS NULL))
THEN
out (
'Please enter atleast one selection criteria to update the record and resubmit the program...');
p_retcode := 1;
p_reterror := 'Please enter atleast one selection criteria to update the record and resubmit the program...';

ELSE
BEGIN
-- Dynamic PL/SQL block invokes subprogram:
l_plsql_block :=
'BEGIN UPDATE '
|| l_upd_table
|| ' TBL SET TBL.'
|| l_set_col_name1
|| ' = '
|| l_quote
|| l_set_col_value1
|| l_quote
|| ' WHERE TBL.'
|| l_where_col_name1
|| ' = '
|| l_quote
|| l_where_col_value1
|| l_quote
|| '; COMMIT; END;';

EXECUTE IMMEDIATE l_plsql_block
USING IN OUT l_upd_table,
l_set_col_name1,
l_set_col_value1,
l_set_col_name2,
l_set_col_value2,
l_set_col_name3,
l_set_col_value3,
l_set_col_name4,
l_set_col_value4,
l_set_col_name5,
l_set_col_value5,
l_set_col_name6,
l_set_col_value6,
l_set_col_name7,
l_set_col_value7,
l_set_col_name8,
l_set_col_value8,
l_set_col_name9,
l_set_col_value9,
l_where_col_name1,
l_where_col_value1,
l_where_col_name2,
l_where_col_value2,
l_where_col_name3,
l_where_col_value3,
l_where_col_name4,
l_where_col_value4,
l_where_col_name5,
l_where_col_value5;

COMMIT;
p_retcode := 0;
p_reterror := 'Interface Table Record Updated successfully';
EXCEPTION WHEN OTHERS
THEN
p_retcode := 1;
p_reterror := 'Interface Table Record Update failed with exception :: ***' || l_plsql_block || ' ***' || SUBSTR (SQLERRM, 1, 1999);

END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_retcode := 1;
p_reterror := SUBSTR (SQLERRM, 1, 1999);
END;

And Using the below wrapper to call the procedure:

SET SERVEROUTPUT ON;

DECLARE
l_upd_table VARCHAR2 (2000) := 'APPS.XX_TEMP';
l_set_col_name1 VARCHAR2 (2000) := 'ATTRIBUTE1';
l_set_col_value1 VARCHAR2 (2000) := '2001';
l_set_col_name2 VARCHAR2 (2000) := 'ATTRIBUTE2';
l_set_col_value2 VARCHAR2 (2000) := 'TEST1';
l_set_col_name3 VARCHAR2 (2000) := 'ATTRIBUTE3';
l_set_col_value3 VARCHAR2 (2000) := '2002';
l_set_col_name4 VARCHAR2 (2000) := 'ATTRIBUTE4';
l_set_col_value4 VARCHAR2 (2000) := 'TEST2';
l_set_col_name5 VARCHAR2 (2000) := 'ATTRIBUTE5';
l_set_col_value5 VARCHAR2 (2000) := '2003';
l_set_col_name6 VARCHAR2 (2000) := 'ATTRIBUTE6';
l_set_col_value6 VARCHAR2 (2000) := 'TEST3';
l_set_col_name7 VARCHAR2 (2000) := 'ATTRIBUTE7';
l_set_col_value7 VARCHAR2 (2000) := '2004';
l_set_col_name8 VARCHAR2 (2000) := 'ATTRIBUTE8';
l_set_col_value8 VARCHAR2 (2000) := 'TEST4';
l_set_col_name9 VARCHAR2 (2000) := 'ATTRIBUTE9';
l_set_col_value9 VARCHAR2 (2000) := '2005';
l_where_col_name1 VARCHAR2 (2000) := 'ATTRIBUTE1';
l_where_col_value1 VARCHAR2 (2000) := '1001';
l_where_col_name2 VARCHAR2 (2000);
l_where_col_value2 VARCHAR2 (2000);
l_where_col_name3 VARCHAR2 (2000);
l_where_col_value3 VARCHAR2 (2000);
l_where_col_name4 VARCHAR2 (2000);
l_where_col_value4 VARCHAR2 (2000);
l_where_col_name5 VARCHAR2 (2000);
l_where_col_value5 VARCHAR2 (2000);
p_reterror VARCHAR2 (2000) := NULL;
p_retcode NUMBER := 0;

BEGIN
apps.intf_tbl_update.intfc_rec_update(l_upd_table,
l_set_col_name1,
l_set_col_value1,
l_set_col_name2,
l_set_col_value2,
l_set_col_name3,
l_set_col_value3,
l_set_col_name4,
l_set_col_value4,
l_set_col_name5,
l_set_col_value5,
l_set_col_name6,
l_set_col_value6,
l_set_col_name7,
l_set_col_value7,
l_set_col_name8,
l_set_col_value8,
l_set_col_name9,
l_set_col_value9,
l_where_col_name1,
l_where_col_value1,
l_where_col_name2,
l_where_col_value2,
l_where_col_name3,
l_where_col_value3,
l_where_col_name4,
l_where_col_value4,
l_where_col_name5,
l_where_col_value5,
p_reterror,
p_retcode);
dbms_output.put_line ('Return Code :: ' || p_retcode);
dbms_output.put_line ('Return Error :: ' || p_reterror);
END;

I am getting the below error:

ORA-01006: bind variable does not exist

Report message to a moderator

- Re: Dynamic SQL : ORA-01006: bind variable does not exist [message #655957 is a reply to message #655956] Sun, 18 September 2016 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

- Re: Dynamic SQL : ORA-01006: bind variable does not exist [message #655958 is a reply to message #655956] Sun, 18 September 2016 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
I am getting the below error:
So fix it.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Use SQL*Plus and copy and paste your session, the WHOLE session.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Report message to a moderator

- Re: Dynamic SQL : ORA-01006: bind variable does not exist [message #655959 is a reply to message #655958] Sun, 18 September 2016 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.

BTW - use of EXECUTE IMMEDIATE can indicate the design is flawed
EXECUTE IMMEDIATE forces hard parse for every execution & does not scale.

Report message to a moderator

- Re: Dynamic SQL : ORA-01006: bind variable does not exist [message #655960 is a reply to message #655959] Sun, 18 September 2016 12:58 Go to previous messageGo to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Thank you BackSwan.

I have already printed the string and it looks like:

BEGIN UPDATE XX_TEMP TBL SET TBL.ATTRIBUTE1 = '2001' WHERE TBL.ATTRIBUTE1 = '1001'; COMMIT; END;

After formatting:
BEGIN
UPDATE XX_TEMP TBL SET TBL.ATTRIBUTE1 = '2001' WHERE TBL.ATTRIBUTE1 = '1001';
COMMIT;
END;

Report message to a moderator

- [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655961 is a reply to message #655956] Sun, 18 September 2016 13:26 Go to previous messageGo to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Hi Friends,

I am using below procedure to execute a dynamic SQL.

CREATE OR REPLACE PACKAGE BODY intf_tbl_update
AS
PROCEDURE intfc_rec_update
(
p_action IN VARCHAR2,
p_table IN VARCHAR2,
p_set_col_name1 IN VARCHAR2,
p_set_col_value1 IN VARCHAR2,
p_set_col_name2 IN VARCHAR2,
p_set_col_value2 IN VARCHAR2,
p_set_col_name3 IN VARCHAR2,
p_set_col_value3 IN VARCHAR2,
p_set_col_name4 IN VARCHAR2,
p_set_col_value4 IN VARCHAR2,
p_set_col_name5 IN VARCHAR2,
p_set_col_value5 IN VARCHAR2,
p_set_col_name6 IN VARCHAR2,
p_set_col_value6 IN VARCHAR2,
p_set_col_name7 IN VARCHAR2,
p_set_col_value7 IN VARCHAR2,
p_set_col_name8 IN VARCHAR2,
p_set_col_value8 IN VARCHAR2,
p_set_col_name9 IN VARCHAR2,
p_set_col_value9 IN VARCHAR2,
p_set_col_name10 IN VARCHAR2,
p_set_col_value10 IN VARCHAR2,
p_where_col_name1 IN VARCHAR2,
p_where_col_value1 IN VARCHAR2,
p_where_col_name2 IN VARCHAR2,
p_where_col_value2 IN VARCHAR2,
p_where_col_name3 IN VARCHAR2,
p_where_col_value3 IN VARCHAR2,
p_where_col_name4 IN VARCHAR2,
p_where_col_value4 IN VARCHAR2,
p_where_col_name5 IN VARCHAR2,
p_where_col_value5 IN VARCHAR2,
p_reterror OUT VARCHAR2,
p_retcode OUT NUMBER
)
IS
l_action VARCHAR2 (10);
l_table VARCHAR2 (50);
l_set_col_name1 VARCHAR2 (50);
l_set_col_value1 VARCHAR2 (2000);
l_set_col_name2 VARCHAR2 (50);
l_set_col_value2 VARCHAR2 (2000);
l_set_col_name3 VARCHAR2 (50);
l_set_col_value3 VARCHAR2 (2000);
l_set_col_name4 VARCHAR2 (50);
l_set_col_value4 VARCHAR2 (2000);
l_set_col_name5 VARCHAR2 (50);
l_set_col_value5 VARCHAR2 (2000);
l_set_col_name6 VARCHAR2 (50);
l_set_col_value6 VARCHAR2 (2000);
l_set_col_name7 VARCHAR2 (50);
l_set_col_value7 VARCHAR2 (2000);
l_set_col_name8 VARCHAR2 (50);
l_set_col_value8 VARCHAR2 (2000);
l_set_col_name9 VARCHAR2 (50);
l_set_col_value9 VARCHAR2 (2000);
l_set_col_name10 VARCHAR2 (50);
l_set_col_value10 VARCHAR2 (2000);
l_where_col_name1 VARCHAR2 (50);
l_where_col_value1 VARCHAR2 (2000);
l_where_col_name2 VARCHAR2 (50);
l_where_col_value2 VARCHAR2 (2000);
l_where_col_name3 VARCHAR2 (50);
l_where_col_value3 VARCHAR2 (2000);
l_where_col_name4 VARCHAR2 (50);
l_where_col_value4 VARCHAR2 (2000);
l_where_col_name5 VARCHAR2 (50);
l_where_col_value5 VARCHAR2 (2000);
l_quote VARCHAR2 (1) := CHR (39);
l_plsql_block VARCHAR2 (2000) := NULL;
BEGIN
l_action := p_action;
l_table := p_table;
l_set_col_name1 := p_set_col_name1;
l_set_col_value1 := p_set_col_value1;
l_set_col_name2 := p_set_col_name2;
l_set_col_value2 := p_set_col_value2;
l_set_col_name3 := p_set_col_name3;
l_set_col_value3 := p_set_col_value3;
l_set_col_name4 := p_set_col_name4;
l_set_col_value4 := p_set_col_value4;
l_set_col_name5 := p_set_col_name5;
l_set_col_value5 := p_set_col_value5;
l_set_col_name6 := p_set_col_name6;
l_set_col_value6 := p_set_col_value6;
l_set_col_name7 := p_set_col_name7;
l_set_col_value7 := p_set_col_value7;
l_set_col_name8 := p_set_col_name8;
l_set_col_value8 := p_set_col_value8;
l_set_col_name9 := p_set_col_name9;
l_set_col_value9 := p_set_col_value9;
l_set_col_name10 := p_set_col_name10;
l_set_col_value10 := p_set_col_value10;
l_where_col_name1 := p_where_col_name1;
l_where_col_value1 := p_where_col_value1;
l_where_col_name2 := p_where_col_name2;
l_where_col_value2 := p_where_col_value2;
l_where_col_name3 := p_where_col_name3;
l_where_col_value3 := p_where_col_value3;
l_where_col_name4 := p_where_col_name4;
l_where_col_value4 := p_where_col_value4;
l_where_col_name5 := p_where_col_name5;
l_where_col_value5 := p_where_col_value5;

IF ( (l_where_col_name1 IS NULL)
AND (l_where_col_value1 IS NULL)
AND (l_where_col_name2 IS NULL)
AND (l_where_col_value2 IS NULL)
AND (l_where_col_name3 IS NULL)
AND (l_where_col_value3 IS NULL)
AND (l_where_col_name4 IS NULL)
AND (l_where_col_value4 IS NULL)
AND (l_where_col_name5 IS NULL)
AND (l_where_col_value5 IS NULL))
THEN
p_retcode := 1;
p_reterror :=
'Please enter atleast one selection criteria to update the record and resubmit the program...';
ELSE
BEGIN
-- Dynamic PL/SQL block invokes subprogram:
l_plsql_block :=
'BEGIN '
|| l_action
|| ' '
|| l_table
|| ' TBL SET TBL.'
|| l_set_col_name1
|| ' = '
|| l_quote
|| l_set_col_value1
|| l_quote
|| ' WHERE TBL.'
|| l_where_col_name1
|| ' = '
|| l_quote
|| l_where_col_value1
|| l_quote
|| '; COMMIT; END;';

EXECUTE IMMEDIATE l_plsql_block
USING IN OUT l_action,
l_table,
l_set_col_name1,
l_set_col_value1,
l_set_col_name2,
l_set_col_value2,
l_set_col_name3,
l_set_col_value3,
l_set_col_name4,
l_set_col_value4,
l_set_col_name5,
l_set_col_value5,
l_set_col_name6,
l_set_col_value6,
l_set_col_name7,
l_set_col_value7,
l_set_col_name8,
l_set_col_value8,
l_set_col_name9,
l_set_col_value9,
l_set_col_name10,
l_set_col_value10,
l_where_col_name1,
l_where_col_value1,
l_where_col_name2,
l_where_col_value2,
l_where_col_name3,
l_where_col_value3,
l_where_col_name4,
l_where_col_value4,
l_where_col_name5,
l_where_col_value5;

COMMIT;
p_retcode := 0;
p_reterror := 'Interface Table Record Updated successfully';
EXCEPTION
WHEN OTHERS
THEN
p_retcode := 1;
p_reterror :=
'Interface Table Record Update failed with exception :: *** '
|| l_plsql_block
|| ' ***'
|| SUBSTR (SQLERRM, 1, 1999);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_retcode := 1;
p_reterror := SUBSTR (SQLERRM, 1, 1999);
END;
END intf_tbl_update;

And Using the below wrapper to call the procedure:

SET SERVEROUTPUT ON;

DECLARE
l_action VARCHAR2 (10) := 'UPDATE';
l_table VARCHAR2 (50) := 'XX_TEMP';
l_set_col_name1 VARCHAR2 (50) := 'ATTRIBUTE1';
l_set_col_value1 VARCHAR2 (2000) := '2001';
l_set_col_name2 VARCHAR2 (50) := 'ATTRIBUTE2';
l_set_col_value2 VARCHAR2 (2000) := 'TEST1';
l_set_col_name3 VARCHAR2 (50) := 'ATTRIBUTE3';
l_set_col_value3 VARCHAR2 (2000) := '2002';
l_set_col_name4 VARCHAR2 (50) := 'ATTRIBUTE4';
l_set_col_value4 VARCHAR2 (2000) := 'TEST2';
l_set_col_name5 VARCHAR2 (50) := 'ATTRIBUTE5';
l_set_col_value5 VARCHAR2 (2000) := '2003';
l_set_col_name6 VARCHAR2 (50) := 'ATTRIBUTE6';
l_set_col_value6 VARCHAR2 (2000) := 'TEST3';
l_set_col_name7 VARCHAR2 (50) := 'ATTRIBUTE7';
l_set_col_value7 VARCHAR2 (2000) := '2004';
l_set_col_name8 VARCHAR2 (50) := 'ATTRIBUTE8';
l_set_col_value8 VARCHAR2 (2000) := 'TEST4';
l_set_col_name9 VARCHAR2 (50) := 'ATTRIBUTE9';
l_set_col_value9 VARCHAR2 (2000) := '2005';
l_set_col_name10 VARCHAR2 (50) := 'ATTRIBUTE10';
l_set_col_value10 VARCHAR2 (2000) := 'TEST5';
l_where_col_name1 VARCHAR2 (50) := 'ATTRIBUTE1';
l_where_col_value1 VARCHAR2 (2000) := '1001';
l_where_col_name2 VARCHAR2 (50);
l_where_col_value2 VARCHAR2 (2000);
l_where_col_name3 VARCHAR2 (50);
l_where_col_value3 VARCHAR2 (2000);
l_where_col_name4 VARCHAR2 (50);
l_where_col_value4 VARCHAR2 (2000);
l_where_col_name5 VARCHAR2 (50);
l_where_col_value5 VARCHAR2 (2000);
p_reterror VARCHAR2 (2000) := NULL;
p_retcode NUMBER := 0;
BEGIN
apps.intf_tbl_update.intfc_rec_update (l_action,
l_table,
l_set_col_name1,
l_set_col_value1,
l_set_col_name2,
l_set_col_value2,
l_set_col_name3,
l_set_col_value3,
l_set_col_name4,
l_set_col_value4,
l_set_col_name5,
l_set_col_value5,
l_set_col_name6,
l_set_col_value6,
l_set_col_name7,
l_set_col_value7,
l_set_col_name8,
l_set_col_value8,
l_set_col_name9,
l_set_col_value9,
l_set_col_name10,
l_set_col_value10,
l_where_col_name1,
l_where_col_value1,
l_where_col_name2,
l_where_col_value2,
l_where_col_name3,
l_where_col_value3,
l_where_col_name4,
l_where_col_value4,
l_where_col_name5,
l_where_col_value5,
p_reterror,
p_retcode);
DBMS_OUTPUT.put_line ('Return Code :: ' || p_retcode);
DBMS_OUTPUT.put_line ('Return Error :: ' || p_reterror);
END;

Upon execution, I am getting the below error:

ORA-01006: bind variable does not exist

Can someone please help me fix this error.

I have also printed the SQL generated:

BEGIN
UPDATE XX_TEMP TBL SET TBL.ATTRIBUTE1 = '2001' WHERE TBL.ATTRIBUTE1 = '1001';
COMMIT;
END;

Any pointers will be of great help.

[Updated on: Sun, 18 September 2016 13:32]

Report message to a moderator

- Re: [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655962 is a reply to message #655961] Sun, 18 September 2016 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

It is still not formatted.
Please read the links we gave and post what we requested.

Report message to a moderator

- Re: Dynamic SQL : ORA-01006: bind variable does not exist [message #655963 is a reply to message #655960] Sun, 18 September 2016 13:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
ajayhareesh wrote on Sun, 18 September 2016 13:58


I have already printed the string and it looks like:

BEGIN UPDATE XX_TEMP TBL SET TBL.ATTRIBUTE1 = '2001' WHERE TBL.ATTRIBUTE1 = '1001'; COMMIT; END;
So what else did you expect? Your dynamic SQL uses no bind variables while you issue:

EXECUTE IMMEDIATE l_plsql_block
USING IN OUT l_upd_table,
l_set_col_name1,
l_set_col_value1,
l_set_col_name2,
l_set_col_value2,
l_set_col_name3,
l_set_col_value3,
l_set_col_name4,
l_set_col_value4,
l_set_col_name5,
l_set_col_value5,
l_set_col_name6,
l_set_col_value6,
l_set_col_name7,
l_set_col_value7,
l_set_col_name8,
l_set_col_value8,
l_set_col_name9,
l_set_col_value9,
l_where_col_name1,
l_where_col_value1,
l_where_col_name2,
l_where_col_value2,
l_where_col_name3,
l_where_col_value3,
l_where_col_name4,
l_where_col_value4,
l_where_col_name5,
l_where_col_value5;


SY.

Report message to a moderator

- Re: [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655964 is a reply to message #655962] Sun, 18 September 2016 13:42 Go to previous messageGo to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Hi Michel Cadot,

I have attached the code for your reference since I am not able to format here.

Please see if you can help.


Thanks,
Ajay

Report message to a moderator

- Re: [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655965 is a reply to message #655964] Sun, 18 September 2016 13:44 Go to previous messageGo to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Hi SY,

I haven't used bind variables earlier and new to dynamic SQL. So not sure what bind variables are and how to pass them to EXECUTE statement. Please let me know if you can.


Thanks,
Ajay

Report message to a moderator

- Re: [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655966 is a reply to message #655965] Sun, 18 September 2016 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why did you decide to use dynamic code for this?

Please Read The Fine Manual below.

https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS01115

Your procedure can be abused to corrupt every table in your schema.
Why do you want to expose this schema to complete destruction?

Report message to a moderator

- Re: [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655967 is a reply to message #655965] Sun, 18 September 2016 13:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then you should read docs. Anyway:

Code: [Select all] [Show/ hide]
l_plsql_block :=
'BEGIN '
|| l_action
|| ' '
|| l_table
|| ' TBL SET TBL.'
|| l_set_col_name1
|| ' = :l_set_col_value1'
|| ' WHERE TBL.'
|| l_where_col_name1
|| ' = :l_where_col_value1
|| '; COMMIT; END;';

EXECUTE IMMEDIATE l_plsql_block
USING
l_set_col_value1,
l_where_col_value1;

SY.

Report message to a moderator

- Re: [Formatted] Dynamic SQL : ORA-01006: bind variable does not exist [message #655968 is a reply to message #655967] Sun, 18 September 2016 14:01 Go to previous message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Thanks a lot for your help SY, got the concept of bind variables.

Thank you BlackSwan for the link, will make a note of it.

Report message to a moderator

Previous Topic: ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b
Next Topic: SQL%ROWCOUNT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 28 17:17:35 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.