ORAScript followed by OraProcedure Problem

jr_rushmeyer · Post by **jr_rushmeyer** » Wed 16 Mar 2005 16:06

Prelim Info:
Delphi 7, Oracle 9.1.2

I have an OraScript that runs about 5 scripts that basically drops a table and create a new one out of other tables.

I follow that with an OraProcedure componet to run an existing procedure. This procedure inserts data from a column in a reference table into my new table (takes last name from one table and inserts it into a col of the same name in my new table.

I know that the new table is created. However, when I go to run the procedure it bombs out and says that PROMDBA (the procedure is PROMDBA.MYPROCEDURE) is invalid.

After looking around, I went into TOAD, pulled up the procedures and saw that the procedures are "INVALID". If I "recompile the invalid procedures" in TOAD and then go back to my app (which I did not close down) and trty to run the procedure again and it works.

SO, something is not happening but I dont know how to make it happen!! Is there some property that I need to set or command that I am forgetting. Basically all I do is say

MYORAPROC.EXECPROC;

Is there something I should do before that to "recompile the invalid proc?"

How this is clear.

JR Rushmeyer
State of Ohio
Dept of Alc and Drug

Martin · Post by **Martin** » Wed 16 Mar 2005 20:01

Execute an 'Alter Procedure compile' after recreating the tabel and before executing the procedure. This should do the trick.

hth,
Martin

jr_rushmeyer · Post by **jr_rushmeyer** » Wed 16 Mar 2005 20:55

Martin

I thought I knew what to do and then I went to the code and got really confused!!

My code looks like this

*****************
OraScriptBigOne.Execute;

OraStoredProc1.ExecProc;

************************

I am not sure how to put in your suggestion. Does that go in another Proc component? Sorry I am so dense here!!

JR

Martin · Post by **Martin** » Wed 16 Mar 2005 21:39

Something like:
(qryUtil is an OraQuery)

qryUtil.SQL.Text := 'Alter procedure '+OraStoredProc1.StoredProcName+' compile';

OraScriptBigOne.Execute;

qryUtil.ExecSQL;

OraStoredProc1.ExecProc;

hth
Martin

jr_rushmeyer · Post by **jr_rushmeyer** » Thu 17 Mar 2005 13:06

Martin:

Eureka!!! That solved the problem. It took a couple times to get my spacing correct but once that was fixed, it works on my test app. Now for the REAL THING!

Thanks for your help Martin

JR

Devart Forums