Menu ▾ ▴

#27 utplsql_util.execute_ddl BUG

Closed-UnableToReproduce
Paul Walker
None
[other - please specify]
9
Medium
[unknown]
2013-10-24
2005-04-29
Anonymous
No

Hi,

First of all, thanks a lot for this great project. I am
starting to play around with it and it looks very good.

I just wanted to let you know the problem I found when
using the utAssert.eq_refc_table
or utAssert.eq_refc_query procedures.

I was getting the following error: ORA-00955 name is already used by an existing object

I could not find anything wrong in the way of using those
procedures so I started to go deeper and deeper in the
code until I found the problem in
the utplsql_util.execute_ddl procedure.

The code for this procedure is the following:

PROCEDURE execute_ddl (stmt VARCHAR2)
IS
   --/* Ignore Oracle7 code! 
   fdbk   PLS_INTEGER;
   cur    PLS_INTEGER := DBMS_SQL.open_cursor;
   --Ignore Oracle7 code! */
BEGIN
   --/* Use 8i code! */
   EXECUTE IMMEDIATE stmt;
   --/* Use 8i code! */
   --/* Ignore Oracle7 code! 
   DBMS_SQL.parse (cur, stmt, DBMS_SQL.native);
   fdbk := DBMS_SQL.EXECUTE (cur);
   DBMS_SQL.close_cursor (cur);
   --Ignore Oracle7 code! */
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

If the stmt parameter is a CREATE TABLE statement,
it will fail raising the error: ORA-00955 name is already used by an existing object because it is trying to
create the table twice.

Just leaving the EXECUTE statement works perfectly
with my Oracle 9i database.

Hope this comment helps to make the utPLSQL
software better.

Cheers,

Oscar.

PD: my email is: o.martin-prieto@accenture.com

Discussion

  • Paul Walker

    Paul Walker - 2013-10-12
    • Status: open --> Open
    • Priority: 5 --> Medium
     

  • Paul Walker

    Paul Walker - 2013-10-12
    • Fixed in version: --> [none]
     

  • Paul Walker

    Paul Walker - 2013-10-24
    • labels: utPLSQL -->
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -4,46 +4,46 @@
 starting to play around with it and it looks very good.

 I just wanted to let you know the problem I found when 
-using the "utAssert.eq\_refc\_table"  
-or "utAssert.eq\_refc\_query"  procedures.
+using the `utAssert.eq_refc_table`  
+or `utAssert.eq_refc_query`  procedures.

-I was getting the following error: "ORA-00955 name is 
-already used by an existing object"
+I was getting the following error: `ORA-00955 name is already used by an existing object`

 I could not find anything wrong in the way of using those 
 procedures so I started to go deeper and deeper in the 
 code until I found the problem in 
-the "utplsql\_util.execute\_ddl" procedure.
+the `utplsql_util.execute_ddl` procedure.

 The code for this procedure is the following:

-PROCEDURE execute\_ddl \(stmt VARCHAR2\)
-IS
-\--/\* Ignore Oracle7 code\! 
-fdbk   PLS\_INTEGER;
-cur    PLS\_INTEGER := DBMS\_SQL.open\_cursor;
-\--Ignore Oracle7 code\! \*/
-BEGIN
-\--/\* Use 8i code\! \*/
-EXECUTE IMMEDIATE stmt;
-\--/\* Use 8i code\! \*/
-\--/\* Ignore Oracle7 code\! 
-DBMS\_SQL.parse \(cur, stmt, DBMS\_SQL.native\);
-fdbk := DBMS\_SQL.EXECUTE \(cur\);
-DBMS\_SQL.close\_cursor \(cur\);
-\--Ignore Oracle7 code\! \*/
-EXCEPTION
-WHEN OTHERS
-THEN
-RAISE;
-END;
+    :::SQL
+    PROCEDURE execute_ddl (stmt VARCHAR2)
+    IS
+       --/* Ignore Oracle7 code! 
+       fdbk   PLS_INTEGER;
+       cur    PLS_INTEGER := DBMS_SQL.open_cursor;
+       --Ignore Oracle7 code! */
+    BEGIN
+       --/* Use 8i code! */
+       EXECUTE IMMEDIATE stmt;
+       --/* Use 8i code! */
+       --/* Ignore Oracle7 code! 
+       DBMS_SQL.parse (cur, stmt, DBMS_SQL.native);
+       fdbk := DBMS_SQL.EXECUTE (cur);
+       DBMS_SQL.close_cursor (cur);
+       --Ignore Oracle7 code! */
+    EXCEPTION
+       WHEN OTHERS
+       THEN
+          RAISE;
+    END;

-If the "stmt" parameter is a CREATE TABLE statement, 
-it will fail raising the error: "ORA-00955 name is already 
-used by an existing object" because it is trying to 
+If the `stmt` parameter is a `CREATE TABLE` statement, 
+it will fail raising the error: `ORA-00955 name is already 
+used by an existing object` because it is trying to 
 create the table twice.

-Just leaving the EXECUTE statement works perfectly 
+Just leaving the `EXECUTE` statement works perfectly 
 with my Oracle 9i database.

 Hope this comment helps to make the utPLSQL
    • Seen in version: --> [other - please specify]
    • Oracle version: --> 9
     

  • Paul Walker

    Paul Walker - 2013-10-24

    Applied Markdown formatting to improve readability

     

  • Paul Walker

    Paul Walker - 2013-10-24

    The new Version Detection system should ensure that only the EXECUTE IMMEDIATE or DBMS_SQL.EXECUTE are run. This should mitigate this issue.

    On a side-note, [bugs:#37] describes a related bug with the Version Detection tags in this procedure.

     

    Related

    Bugs Archive (Use GitHub now): #37

  • Paul Walker

    Paul Walker - 2013-10-24
    • status: Open --> Closed-UnableToReproduce
    • assigned_to: Paul Walker
    • Fixed in version: [none] --> [unknown]
     

Log in to post a comment.