Solved

Problem with DBMS_SQL.PARSE

Posted on 2004-08-19
14
1,720 Views
Last Modified: 2007-11-27
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
Comment
Question by:Jarodtweiss
  • 6
  • 6
  • 2
14 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 11839206
try removing the semi-colon at the end of the query ";"

0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 11839241
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
 
LVL 4

Author Comment

by:Jarodtweiss
ID: 11839244
I have tried that and I still have an error message :

ORA-00933: SQL command not properly ended
0
 
LVL 4

Author Comment

by:Jarodtweiss
ID: 11839262
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11839326
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
 
LVL 4

Author Comment

by:Jarodtweiss
ID: 11839409
No sorry, this doesn't change anything
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11839447
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 11839457
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
 
LVL 4

Author Comment

by:Jarodtweiss
ID: 11839613
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
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 11839670
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
 
LVL 4

Author Comment

by:Jarodtweiss
ID: 11839884
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
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 250 total points
ID: 11839963
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
 
LVL 4

Author Comment

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

Thanks for your help !
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 11840026
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now