Hello,
In an effort to trigger a compile of Oracle packages with invalid status I turned off the ParamCheck of some SQLStoredProcs. I found it did in fact cause the invalid packages to compile and execute. Unfortunately as testing continued we discovered that a particular procedure in a package which should return a complete string result was not. Instead each time it executed, it returned a substring of the expected result. Each subsequent time adding a character to the result. What is the appropriate way to force a compile of an invalid package upon calling a SQLStoredProc?
Thank you,
Mike Malinowski
TSQLStoredProc ParamCheck Unexpected Results
Hello,
SQLStoredProc cannot recompile invalid packages automatically, but you can try to compile a package automatically before calling the procedure from it, for example:
SQLStoredProc cannot recompile invalid packages automatically, but you can try to compile a package automatically before calling the procedure from it, for example:
Code: Select all
var
dataResult: TCustomSQLDataSet;
begin
dataResult := nil;
SQLConnection1.Execute('SELECT count(*) cnt ' + #13 +
'FROM dba_objects ' + #13 +
'WHERE STATUS = ''INVALID'' ' + #13 +
'AND OWNER = ''SCOTT'' ' + #13 +
'AND OBJECT_NAME = ''PKG_TEST'' ' + #13 +
'AND OBJECT_TYPE = ''PACKAGE''', nil, @dataResult);
if dataResult.FieldByName('cnt').AsInteger > 0 then
SQLConnection1.Execute('ALTER PACKAGE PKG_TEST COMPILE', nil);
....
Hello,
Thanks for the input on the package compile.
With regard to the ParamCheck property, any idea how it could cause the strange behavior of a procedure within a package which should return a field value from a simple select statement. With the package properly compiled, depending on ParamCheck being True or False, the procedure either returns a complete correct string as result or a substring. The output parameter appears to act like a variable into which the procedure incrementally updates the string result. As an example, the correct output would be a company name 'XYZ'. With ParamCheck set to False, the first time the procedure executes it returns ''. Each subsequent time the procedure is called, the output parameter is as follows: 'X' then 'XY' and finally 'XYZ'. Each time passing in the same input parameter value.
Thank you,
Mike Malinowski
Thanks for the input on the package compile.
With regard to the ParamCheck property, any idea how it could cause the strange behavior of a procedure within a package which should return a field value from a simple select statement. With the package properly compiled, depending on ParamCheck being True or False, the procedure either returns a complete correct string as result or a substring. The output parameter appears to act like a variable into which the procedure incrementally updates the string result. As an example, the correct output would be a company name 'XYZ'. With ParamCheck set to False, the first time the procedure executes it returns ''. Each subsequent time the procedure is called, the output parameter is as follows: 'X' then 'XY' and finally 'XYZ'. Each time passing in the same input parameter value.
Thank you,
Mike Malinowski