asked on

Problem with DBMS_SQL.PARSE

Hi !

I want to do a script to do disable all my constraint in DB except my primary keys.
To do that, I use that query :

SELECT 'ALTER TABLE '
|| SUBSTR (c.table_name, 1, 35)
|| ' DISABLE CONSTRAINT '
|| constraint_name
|| ' ;' AS QUERY
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name
AND constraint_name NOT LIKE 'PK%';

to get some "ready to execute" queries.
What I want to do is to automate the process, is not running that query, then getting the result and executing it in a new sql script window. I wanted to use DBMS_SQL package for that.
Here is my final script :

DECLARE
myQuery DBMS_SQL.VARCHAR2S;
myQueryIndex NUMBER := 1;
Cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
ret_val INTEGER;

CURSOR myCursor
      IS
SELECT 'ALTER TABLE '
|| SUBSTR (c.table_name, 1, 35)
|| ' DISABLE CONSTRAINT '
|| constraint_name
|| ' ;' AS QUERY
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name
AND constraint_name NOT LIKE 'PK%';

myRow myCursor%ROWTYPE;

BEGIN
-- 1. Open the cursor
IF NOT myCursor%ISOPEN THEN
       OPEN myCursor;
      END IF;
      -- 2. Iterate thru the cursor to take into account each line
      LOOP
       FETCH myCursor INTO myRow;
EXIT WHEN myCursor%NOTFOUND;

myQuery(myQueryIndex) := myRow.QUERY;
myQueryIndex := myQueryIndex + 1;
END LOOP;

CLOSE myCursor;

--Execute the query itself
DBMS_SQL.PARSE(Cursor_id, myQuery, 1, myQueryIndex - 1, TRUE, DBMS_SQL.NATIVE);
ret_val := DBMS_SQL.EXECUTE(Cursor_id);
DBMS_SQL.CLOSE_CURSOR(Cursor_id);
END;

But I get an error on the DBMS_QSL.PARSE line:
ORA-00911 : invalid character
ORA-06512 : at "SYS.DBMS_SYS_SQL", line 1485
ORA-06512 : at "SYS.DBMS_SQL" line 26

I have already dealt with this kind of script without any problem. Whay do I get an error here ? Is it because I want to do an ALTER TABLE ?
Any solution ?

Thx !

catchmeifuwant

try removing the semi-colon at the end of the query ";"

catchmeifuwant

To be precise...change this part of the code..

CURSOR myCursor
IS
SELECT 'ALTER TABLE '
|| SUBSTR (c.table_name, 1, 35)
|| ' DISABLE CONSTRAINT '
|| constraint_name
--|| ' ;' AS QUERY ------------------------------- semicolon removed here
|| ' ' AS QUERY
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name
AND constraint_name NOT LIKE 'PK%';

Jarodtweiss

ASKER

I have tried that and I still have an error message :

ORA-00933: SQL command not properly ended

Jarodtweiss

ASKER

To give an example of generated script, I have added a DBMS_OUPTUT sentence to see each line I am adding to my array, this is something like that :

ALTER TABLE AIRCRAFT DISABLE CONSTRAINT SYS_C0032810
ALTER TABLE AIRCRAFT DISABLE CONSTRAINT SYS_C0032811
ALTER TABLE AIRCRAFT DISABLE CONSTRAINT SYS_C0032812
ALTER TABLE AIRCRAFT DISABLE CONSTRAINT SYS_C0032813

And I am pretty sure there is no problem with my queries themselves because I am able to select all those queries and run them in TOAD

Helena Marková

Maybe you can try this modification:

CURSOR myCursor
IS
SELECT 'ALTER TABLE '
|| SUBSTR (c.table_name, 1, 35)
|| ' DISABLE CONSTRAINT '
|| c.constraint_name
|| ' ;' AS QUERY
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name
AND c.constraint_name NOT LIKE 'PK%';

Jarodtweiss

ASKER

No sorry, this doesn't change anything

Helena Marková

And this ?

CURSOR myCursor
IS
SELECT 'ALTER TABLE '
|| SUBSTR (c.table_name, 1, 35)
|| ' DISABLE CONSTRAINT '
|| c.constraint_name
|| ' ;' AS QUERY
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name
AND c.constraint_name NOT LIKE 'PK%'''; -- here seems to be a problem

catchmeifuwant

I tried the same thing here...without the ";" it worked fine...

===========================

SQL> select table_name,constraint_name,status
2 from user_constraints
3 where table_name = 'TEMP';

------------- CONSTRAINT STATUS

TABLE_NAME CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ --------
TEMP SYS_C003200 ENABLED

------------- EXECUTING WITH SEMI-COLON

SQL> DECLARE
2 myQuery DBMS_SQL.VARCHAR2S;
3 myQueryIndex NUMBER := 1;
4 Cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
5 ret_val INTEGER;
6 CURSOR myCursor
7 IS
8 SELECT 'ALTER TABLE '|| SUBSTR (c.table_name, 1, 35)|| ' DISABLE CONSTRA
INT '|| constraint_name|| ';' AS QUERY
9 --SELECT 'ALTER TABLE '|| SUBSTR (c.table_name, 1, 35)|| ' DISABLE CONST
RAINT '|| constraint_name|| '' AS QUERY
10 FROM user_constraints c, user_tables u
11 WHERE c.table_name = u.table_name
12 and c.table_name = 'TEMP'
13 AND constraint_name NOT LIKE 'PK%';
14 myRow myCursor%ROWTYPE;
15 BEGIN
16 -- 1. Open the cursor
17 IF NOT myCursor%ISOPEN THEN
18 OPEN myCursor;
19 END IF;
20 -- 2. Iterate thru the cursor to take into account each line
21 LOOP
22 FETCH myCursor INTO myRow;
23 EXIT WHEN myCursor%NOTFOUND;
24 myQuery(myQueryIndex) := myRow.QUERY;
25 myQueryIndex := myQueryIndex + 1;
26 END LOOP;
27 CLOSE myCursor;
28 --Execute the query itself
29 DBMS_SQL.PARSE(Cursor_id, myQuery, 1, myQueryIndex - 1, TRUE, DBMS_SQL.NATI
VE);
30 ret_val := DBMS_SQL.EXECUTE(Cursor_id);
31 DBMS_SQL.CLOSE_CURSOR(Cursor_id);
32 exception
33 when others then
34 dbms_output.put_line(sqlerrm);
35 END;
36 /
ORA-00911: invalid character

PL/SQL procedure successfully completed.

------------- EXECUTING WITHOUT THE SEMI-COLON

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 myQuery DBMS_SQL.VARCHAR2S;
3 myQueryIndex NUMBER := 1;
4 Cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
5 ret_val INTEGER;
6 CURSOR myCursor
7 IS
8 --SELECT 'ALTER TABLE '|| SUBSTR (c.table_name, 1, 35)|| ' DISABLE CONST
RAINT '|| constraint_name|| ';' AS QUERY
9 SELECT 'ALTER TABLE '|| SUBSTR (c.table_name, 1, 35)|| ' DISABLE CONSTRA
INT '|| constraint_name|| '' AS QUERY
10 FROM user_constraints c, user_tables u
11 WHERE c.table_name = u.table_name
12 and c.table_name = 'TEMP'
13 AND constraint_name NOT LIKE 'PK%';
14 myRow myCursor%ROWTYPE;
15 BEGIN
16 -- 1. Open the cursor
17 IF NOT myCursor%ISOPEN THEN
18 OPEN myCursor;
19 END IF;
20 -- 2. Iterate thru the cursor to take into account each line
21 LOOP
22 FETCH myCursor INTO myRow;
23 EXIT WHEN myCursor%NOTFOUND;
24 myQuery(myQueryIndex) := myRow.QUERY;
25 myQueryIndex := myQueryIndex + 1;
26 END LOOP;
27 CLOSE myCursor;
28 --Execute the query itself
29 DBMS_SQL.PARSE(Cursor_id, myQuery, 1, myQueryIndex - 1, TRUE, DBMS_SQL.NATI
VE);
30 ret_val := DBMS_SQL.EXECUTE(Cursor_id);
31 DBMS_SQL.CLOSE_CURSOR(Cursor_id);
32 exception
33 when others then
34 dbms_output.put_line(sqlerrm);
35* END;
SQL> /

PL/SQL procedure successfully completed.

------------- CONSTRAINT STATUS AFTER EXECUTING

SQL> select table_name,constraint_name,status
2 from user_constraints
3 where table_name = 'TEMP';

TABLE_NAME CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ --------
TEMP SYS_C003200 DISABLED

SQL>

==============================================
hth !

Jarodtweiss

ASKER

catchmeifuwant,

I have done an extra test here to modify my cursor adding this were clause :
AND c.CONSTRAINT_NAME = 'SYS_C0032881'
and it works (without the semicolumn)
But when doing
AND c.CONSTRAINT_NAME IN ('SYS_C0032881', 'SYS_C0032882');
it doesn't work --> SQL Command not working properly
apparenlty the problem is because we are dealing with several alter table successively

So If I replace my PARSE command with a EXECUTE IMMEDIATE inside my loop it is working (without the semicolumn)
But to understand, do you know why there is an error with several orders ?

catchmeifuwant

I'm quite confused here now...why are you altering your SQL statement to fetch and formulate the "alter table" statement ?

>But to understand, do you know why there is an error with several orders ?
???

Jarodtweiss

ASKER

In your last comment, you were saying that you were able to test the code succesfully on your side.
And the only difference I saw is that on your test, you were working on 1 table with 1 constraint only while I am dealing with maybe 20 tables and 300 constraints.
So I wanted to test with one constraint in particular if it was working (and it was, like you)
BUt when dealing with more constraint, eg 2, it was not working any more.

Is that answer to your question ? (I am not sure I understood correctly...)

Then for my second message part, i wanted to test if dealing with one constraint at a time (ie inside my loop) will work (and it is, with the EXECUTE IMMEDIATE statement) instead of working with an array of all constraint like I was doing in the code I sent.

My last question is : ok it is working with ONE constraint (ie one array of 1 line with the PARSE method or with the EXECUTE IMMEDIATE method inside my loop) but why do cannot I use the PARSE method with a full array (~300 lines of ALTER TABLE statements for all my constraints)

ASKER CERTIFIED SOLUTION

catchmeifuwant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jarodtweiss

ASKER

Right !
I have read that but.... I hadn't make the link... :-D
Ok, now this is clear !

Thanks for your help !

catchmeifuwant

Thanks for the points...with modifications your code works..

DECLARE
myQuery DBMS_SQL.VARCHAR2S;
myQueryIndex NUMBER := 1;
Cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
ret_val INTEGER;

CURSOR myCursor
IS
SELECT 'ALTER TABLE '|| SUBSTR (c.table_name, 1, 35)|| ' DISABLE CONSTRAINT '|| constraint_name|| '' AS QUERY
FROM user_constraints c, user_tables u
WHERE c.table_name = u.table_name
and c.table_name like 'TEMP%'
AND constraint_name NOT LIKE 'PK%';
myRow myCursor%ROWTYPE;

BEGIN
-- 1. Open the cursor
IF NOT myCursor%ISOPEN THEN
OPEN myCursor;
END IF;
-- 2. Iterate thru the cursor to take into account each line

LOOP

FETCH myCursor INTO myRow;
EXIT WHEN myCursor%NOTFOUND;

myQuery(myQueryIndex) := myRow.QUERY;
myQueryIndex := myQueryIndex + 1;
END LOOP;
CLOSE myCursor;
--Execute the query itself

------------------- Modification start

for i in 1..myquery.last
loop
DBMS_SQL.PARSE(Cursor_id, myQuery(i),DBMS_SQL.NATIVE);
end loop;

------------------- Modification end

ret_val := DBMS_SQL.EXECUTE(Cursor_id);
DBMS_SQL.CLOSE_CURSOR(Cursor_id);
exception
when others then
dbms_output.put_line(sqlerrm);
END;
/