Home » SQL & PL/SQL » SQL & PL/SQL » Execute immediate
Execute immediate [message #312478] Wed, 09 April 2008 00:42 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,
i need to enable the all disable constraints in schema.
I wrote query using execute immediate statement but it shows a
identifier is too long error.

scripts:

DECLARE
CURSOR c1 IS
SELECT table_name, r_constraint_name
FROM user_constraints
WHERE status = 'DISABLED';
v_table_name user_constraints.table_name%type;
v_constraint_name user_constraints. r_constraint_name%type;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO v_table_name, v_constraint_name;
EXECUTE IMMEDIATE 'ALTER TABLE'|| v_table_name || 'ENABLE CONSTRAINTS'|| v_constraint_name;
EXIT WHEN C1%NOTFOUND;
END LOOP;

CLOSE c1;
END;

ORA-00972: identifier is too long
ORA-06512: at line 14

how can we resolve it?

Thanks,
Michael
Re: Execute immediate [message #312491 is a reply to message #312478] Wed, 09 April 2008 00:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need spaces on either side of your variables to separate them from the words before and after:

EXECUTE IMMEDIATE 'ALTER TABLE '|| v_table_name || ' ENABLE CONSTRAINTS '|| v_constraint_name;

It can help to save your dynamic string to a variable, then display it using dbms_output and test that in sql, to find such things.
Re: Execute immediate [message #312497 is a reply to message #312478] Wed, 09 April 2008 01:08 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you refuse to follow the guidelines as requested many times?

Regards
Michel
Previous Topic: Compute Sum Compute count
Next Topic: can procedures created in sql server be converted into oracle (merged)
Goto Forum:
  


Current Time: Fri Dec 09 09:22:00 CST 2016

Total time taken to generate the page: 0.18215 seconds