• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1784
  • Last Modified:

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 !
0
Jarodtweiss
Asked:
Jarodtweiss
  • 6
  • 6
  • 2
1 Solution
 
catchmeifuwantCommented:
try removing the semi-colon at the end of the query ";"

0
 
catchmeifuwantCommented:
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%';
0
 
JarodtweissAuthor Commented:
I have tried that and I still have an error message :

ORA-00933: SQL command not properly ended
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
JarodtweissAuthor Commented:
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
0
 
Helena Markováprogrammer-analystCommented:
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%';
0
 
JarodtweissAuthor Commented:
No sorry, this doesn't change anything
0
 
Helena Markováprogrammer-analystCommented:
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
0
 
catchmeifuwantCommented:
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 !
0
 
JarodtweissAuthor Commented:
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 ?
0
 
catchmeifuwantCommented:
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 ?
???
0
 
JarodtweissAuthor Commented:
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)
0
 
catchmeifuwantCommented:
Ok this is what I presume is your problem. From Oracle documentation,

-----------
"There are two versions of the PARSE procedure: one uses a VARCHAR2 statement as an argument, and the other uses a VARCHAR2S (table of VARCHAR2) as an argument.

The procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a single VARCHAR2 variable by splitting up the statement"

To parse SQL statements larger than 32 KB, DBMS_SQL makes use of PL/SQL tables to pass a table of strings to the PARSE procedure. These strings are concatenated and then passed on to the Oracle server.

--------------
-which ideally is a single statement that is broken down into smaller units and concatenated later on.

What we were trying to do is combine more than 1 sql statement itself...
0
 
JarodtweissAuthor Commented:
Right !
I have read that but.... I hadn't make the link... :-D
Ok, now this is clear !

Thanks for your help !
0
 
catchmeifuwantCommented:
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;
/
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now