hi,

I added some function, views procs... to
a new database.
When i compile these with a toraclequery
i fetch after some time an

"ora-0604: error occured at recursive SQL level 1
ora-0100 maximum open cursors exceeded."

My (pseudo)code is like this
qry1.SQL := Select all Invalid Objects
qry1.Execute
While not qry1.EOF Do
begin
qry2.SQL := 'Alter 'Object Name compile';
qry2.Execute;
qry1.Next;
end;


The last error (open_Cursors) is not true. I have set open_cursors to 200 and also the plsql-dev can compile invalid objects.

I just want to do the same as the plsql-dev function: "compile invalid objects".

Does i have something overseen?

Thanks

Sven

[This message has been edited by Sven (edited 19 July 2000).]

Just use a Package and a Query

type
...

DBMS_DDL: TOraclePackage;
Proc_Items_qry: TOracleQuery;
...
public
...
procedure Recompile_All;
end;

and use this procedure:
procedure TDataModule_Procs.Recompile_All;
var
Param1, Param2, Param3: Variant;
begin
with Param0_qry do
try
SQL.Clear;
SQL.Add('SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE STATUS = 'INVALID' AND
OBJECT_TYPE IN ('PROCEDURE', ' FUNCTION', 'PACKAGE', 'PACKAGE BODY')');
execute;
while not EOF do
begin
Param1 := Field('OBJECT_TYPE');
Param2 := NULL;
Param3 := Field('OBJECT_NAME');
with DBMS_DDL do
try
CallProcedure('ALTER_COMPILE', [Param1, Param2, Param3]);
except
on E: EOracleError do
ShowMessage(E.Message);
end;
next;
end;
except
on E: EOracleError do
ShowMessage(E.Message);
end;
end;

------------------
JohaViss

Many thanks Johan,

i have trouble to get you example running

This i added to the code.

DBMS_DDL := OraclePackage.Create(NIL);
DBMS_DDL.Session := MySession;
DBMS_DDL.Name := 'DBMS_DDL'; ??


If i execute CallProcedure i get an error:

Identifier 'ALTER_COMPILE' must be declared.
[...]

But an exec dbms_DDL.alter_Compile (..)
on the plsql/dev command shell works.

I have to miss something very obvious [Linked Image].

Any ideas?

Thanks in advance
Sven

[Linked Image]

arrgh. tomatoes on my eyes,

I set

DBMS_DDL.Name := 'dbms_ddl';

instead of

DBMS_DDL.PackageName := 'dbms_ddl';

Sorry for bothering you and thanks again.

Sven