Devart Forums

Posted: **Thu 14 Jul 2011 15:51**

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

Posted: **Mon 18 Jul 2011 09:53**

Hello,

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);

....

Posted: **Mon 18 Jul 2011 20:14**

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

Posted: **Tue 19 Jul 2011 09:50**

Hello,

I cannot reproduce this problem.
Please try to define the string output parameter size explicitly.
If it does not help, please send us a small sample demonstrating the problem including the script to create the procedure to alexp*devart*com.

Devart Forums

TSQLStoredProc ParamCheck Unexpected Results

TSQLStoredProc ParamCheck Unexpected Results