[patch_done] Oracle - stored procedures packages

serbod · Post by **serbod** » 08.05.2013, 13:39

Lazarus 1.0.8 Win7 32 bit
Zeos 7.1 alpha SVN rev.2224

Some strange behavior of Oracle stored procedures packages. They showed in procedure list witch quotes, and they params prefixed with procedure name. Procedure name without quotes or in lower case don't working.

Even with suggested names procedure not working - OCI_ERROR: ORA-01741: illegal zero-length identifier

бесплатный хостинг картинок

Post by **EgonHugeist** » 22.05.2013, 12:18

serbod,

Thougth oracle and other providers work case sensitive. It was me who made the modifications on the other hand Oracle isn't able to detect the right procedures. So we decided to add an Identifier quoting for the procedure names.

Some parameters and field names have to be Quoted too, i know it. I know about some old Mantis bugreports we had. This is a remaing part.

I was bussy with private things. But quoted FieldNames like "Field 1" and Parameters are on our todo list..

AFAICS is this an minior issue and a Property-Editor thing only, am i wrong?

serbod · Post by **serbod** » 27.05.2013, 11:47

Procedure packages don't working with quotes or without quotes - problem in ZStoredProc for Oracle.

When i use ZQuery for package procedure call - it working fine:

Code: Select all

procedure Tdm_ora_main.SaveGrant(Item: TGrant);
var
  Query: TZQuery;
begin
  Query:=ZQuery;
  if not Query.Connection.Connected then Query.Connection.Connect();

  Query.SQL.Text:='BEGIN school.meal_pkg.add_meal_discount(:l_id, :l_discount_name, :l_discount_value, :l_school_id, :l_meal_discount_type_id, :l_meal_discount_kind); END;';
  Query.Prepare();
  Query.ParamByName('l_id').ParamType:=ptInputOutput;
  if Item.ID<>0 then Query.ParamByName('l_id').AsInteger:=Item.ID;
  Query.ParamByName('l_discount_name').AsString:=Item.Name;
  Query.ParamByName('l_discount_value').AsFloat:=Item.Value;
  Query.ParamByName('l_school_id').AsInteger:=Item.School.ID;
  Query.ParamByName('l_meal_discount_type_id').AsInteger:=1;
  Query.ParamByName('l_meal_discount_kind').AsInteger:=0;
  Query.ExecSQL();
  Item.ID:=Query.ParamByName('l_id').AsInteger;
  Query.Close();

end;

Post by **EgonHugeist** » 27.05.2013, 21:00

serbod,

the Oracle package support is rarely tested. I made the current code. It might be helpfull if you attach an example to check and see clearly what you mean. Do you use SVN? Thought we've upgrade some MetaInformations. My Oracle is vinny nilly curently... I'll restart the server tonight. But the last test i did didn't show me such major hickups. An exapmle would be great..

Post by **EgonHugeist** » 28.05.2013, 10:08

serbod,

it might be possible i didn't understand your corectly.

As i wrote the package support we decided to give each parameter a unique name. The reason is simple: ZP.ParamByName('xx') should work properly.

Oracle allows to have StoredProcedure overloads. And the name of a stored procedure can be equal to a package contained stored procedure. All names are unique to get the stored procedures running like expected. That's why we suffix the package name to the StoredProcedureName. On the other hand i'm sure we'll get some bugreports.

Main intention was to decompose the Parameter-names and theire types correctly.

Internaly Zeos decomposes the StoredProcedure- and Parameter-names. So we're able to execute allways the right procedure. Imagine we do not send Strings like you mentioned the way you did it with the TZQuery-component, nope we access the OCI Api which is faster. The second goal if my written code.

We are also able to execute the whole package contained stored procedure if you choose the package name. Than you'll see all parameters of all package contained stored procedures.
What do you think? Is this answer sufficent for you?

serbod · Post by **serbod** » 30.05.2013, 13:53

Sorry, i don't know much details about Oracle packages, but i use it just like other Oracle procedures and functions, exept they have package name prefix:

Code: Select all

standard_procedure(param1, param2)
package_name.package_procedure(param1, param2)

Parameters names in packages is same, no additional prefixes.

Now look at pictures above - procedure names in StoredProcName dropbox embraced by double quotes ( " ):

Code: Select all

"package_name.proc_name"()
^-this                 ^- and this

Oracle don't allow such symbols in procedure name.

Then look at procedure parameters list on small picture. That parameters prefixed by procedure name. Procedure will be like that:

Code: Select all

package_name.proc_name(proc_name.param1, proc_name.param2)
                       ^--------^ there  ^--------^ and there

I don't checks, allow Oracle that prefixes or not, but prefixes in parameters names is unneccesary.

Post by **EgonHugeist** » 30.05.2013, 15:58

Sorry, i don't know much details about Oracle packages, but i use it just like other Oracle procedures and functions, exept they have package name prefix:

Me too. All i did is to get the examples running i had before.

Oracle don't allow such symbols in procedure name.

Sure? Really. Think about case sensitive MetaInformations and procedure calls.

don't checks, allow Oracle that prefixes or not, but prefixes in parameters names is unneccesary.

This is a unique user backend solution for the parameters. Have a look to:

Code: Select all

function TZOracleCallableStatement.GetProcedureSql(SelectProc: boolean): ZAnsiString;
var
  sFunc: string;
  I, IncludeCount, LastIndex: Integer;
  PackageBody: TStrings;
  TempResult: String;

  function GenerateParamsStr(Count: integer): string;
  var
    I: integer;
  begin
    for I := 0 to Count - 1 do
    begin
      if ( FDBParamTypes[I] = 4 ) then //ptResult
      begin
        sFunc := ' :'+FOracleParams[0].pName+' := ';
        continue;
      end;
      if Result <> '' then
        Result := Result + ',';
      if IsFunction then
        Result := Result + ':'+FOracleParams[I+1].pName
      else
        Result := Result + ':'+FOracleParams[I].pName;
    end;
    Result := '('+Result+')'
  end;

var
  InParams: string;
begin
  sFunc := '';
  if PackageIncludedList.Count > 0 then
  begin
    PackageBody := TStringList.Create;
    PackageBody.Add('BEGIN');
    LastIndex := 0;
    for IncludeCount := 0 to PackageIncludedList.Count -1 do
    begin
      InParams := '';
      sFunc := '';
      for i := LastIndex to high(FOracleParams) do
        if IncludeCount = FOracleParams[i].pProcIndex then
          if ( FOracleParams[I].pType = 4 ) then //ptResult
            sFunc := ' :'+StringReplace(FOracleParams[I].pName, '.', '', [rfReplaceAll])+' := '
          else
            if InParams <> '' then
              InParams := InParams +', :'+StringReplace(FOracleParams[I].pName, '.', '', [rfReplaceAll])
            else
              InParams := InParams +':'+StringReplace(FOracleParams[I].pName, '.', '', [rfReplaceAll])
        else
        begin
          LastIndex := I;
          break;
        end;
      PackageBody.Add('BEGIN '+sFunc+GetConnection.GetMetadata.GetIdentifierConvertor.Quote(SQL)+
        '.'+GetConnection.GetMetadata.GetIdentifierConvertor.Quote(PackageIncludedList[IncludeCount])+'('+InParams+'); END;');
    end;
    PackageBody.Add('END;');
    TempResult := TrimRight(PackageBody.Text);
    FreeAndNil(PackageBody);
  end
  else
  begin
    InParams := GenerateParamsStr( FOracleParamsCount );
    TempResult := 'BEGIN ' + sFunc +SQL + InParams+'; END;';
  end;
  Result := ZPlainString(TempResult);
end;

Here you can see i replace the '.' chars and assamble the whole query. The construction is a bit more complicated like you can see. (:

Hint add the TZSQLMonitor to log the statements and events.

Give me an example to work with. I need something to confirm a broken support..

serbod · Post by **serbod** » 31.05.2013, 10:01

Example 1
Set procedure and parameter name as suggested in StoredProcName an Params editor.

StoredProcName="MEAL_PKG.DISABLE_MEAL"
Params:
MEAL_PKG.L_ID

code:

Code: Select all

  ZProc.ParamByName('DISABLE_MEAL.L_ID').AsInteger:=14;
  ZProc.ExecProc();

TZSQLMonitor reports:

Code: Select all

2013-05-31 12:40:24 cat: Connect, proto: oracle-9i, msg: CONNECT TO "10.1.0.1/XE" AS USER "school"
2013-05-31 12:40:24 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT
2013-05-31 12:40:25 cat: Execute, proto: oracle-9i, msg: select * from user_arguments where (package_name = 'MEAL_PKG' AND object_name like 'DISABLE_MEAL'  OR package_name like 'DISABLE_MEAL' )AND POSITION > 0 ORDER BY POSITION
2013-05-31 12:40:25 cat: Execute, proto: oracle-9i, msg: select * from user_arguments where (package_name = 'MEAL_PKG' AND object_name like 'DISABLE_MEAL'  OR package_name like 'DISABLE_MEAL' )AND POSITION = 0 ORDER BY POSITION
2013-05-31 12:40:25 cat: Execute, proto: oracle-9i, msg: BEGIN
BEGIN ""MEAL_PKG.DISABLE_MEAL"".DISABLE_MEAL(:DISABLE_MEALL_ID); END;
END;, errcode: 1741, error: OCI_ERROR: ORA-01741: illegal zero-length identifier

Example 2
try to remove double quotes

StoredProcName=MEAL_PKG.DISABLE_MEAL (without double quotes)
Params:
(none)

code:

Code: Select all

  ZProc.ParamByName('DISABLE_MEAL.L_ID').AsInteger:=14;
  ZProc.ExecProc();

Project ZeosTest raised exception class "EDatabaseError" with message:
ZProc: Parameter "DISABLE_MEAL.L_ID" not found

Code: Select all

2013-05-31 12:45:11 cat: Connect, proto: oracle-9i, msg: CONNECT TO "10.1.0.1/XE" AS USER "school"
2013-05-31 12:45:11 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT

Example 3
try to set procedure name at runtime

StoredProcName='' (none)
Params:
(none)

code:

Code: Select all

  ZProc.StoredProcName:='MEAL_PKG.DISABLE_MEAL';
  ZProc.ParamByName('DISABLE_MEAL.L_ID').AsInteger:=14;
  ZProc.ExecProc();

Project ZeosTest raised exception class "EDatabaseError" with message:
ZProc: Parameter "DISABLE_MEAL.L_ID" not found

Code: Select all

2013-05-31 12:55:09 cat: Connect, proto: oracle-9i, msg: CONNECT TO "10.1.0.1/XE" AS USER "school"
2013-05-31 12:55:09 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT
2013-05-31 12:56:03 cat: Execute, proto: oracle-9i, msg: select * from user_arguments where (package_name IS NULL AND object_name like 'DISABLE_MEAL'  OR package_name like 'DISABLE_MEAL' )AND POSITION > 0 ORDER BY POSITION
2013-05-31 12:56:03 cat: Execute, proto: oracle-9i, msg: select * from user_arguments where (package_name IS NULL AND object_name like 'DISABLE_MEAL'  OR package_name like 'DISABLE_MEAL' )AND POSITION = 0 ORDER BY POSITION

Post by **EgonHugeist** » 02.06.2013, 11:33

serbod,

i'll add a test case and verify your suggestions the next week..

Post by **EgonHugeist** » 04.06.2013, 15:58

EgonHugeist,

patch done R2285 /testing7.1 (SVN). Can you test it please? Give me a sign if it fixes your issues or other examples.

May i ask you if you've some experiences with OCI?

serbod · Post by **serbod** » 24.06.2013, 09:32

EgonHugeist, sorry for silence for long time. I just returned from travel.

I just tested last SVN snapshot (2415) on Lazarus 1.0.10 (win32). Procedure names now showed correctly, but parameters is empty - they dont't filled on .StoredProcName change.

Some log output:

Code: Select all

2013-06-24 12:21:46 cat: Execute, proto: oracle-9i, msg: select * from user_arguments where (package_name IS NULL AND object_name like 'DISABLE_MEAL'  OR package_name like 'DISABLE_MEAL' )AND POSITION > 0 ORDER BY POSITION
2013-06-24 12:21:46 cat: Execute, proto: oracle-9i, msg: select * from user_arguments where (package_name IS NULL AND object_name like 'DISABLE_MEAL'  OR package_name like 'DISABLE_MEAL' )AND POSITION = 0 ORDER BY POSITION

I suggest, "where package_name" not correct. Maybe, package name not autodetected from procedure name.

Post by **EgonHugeist** » 30.06.2013, 17:16

serbod,

didn't forgot you.. Bussy currently.

they dont't filled on .StoredProcName change.

IIRC are the Paremeter-name of PackageName+StoredProc-name equal to StoredProc-name. Right?

serbod · Post by **serbod** » 01.07.2013, 08:06

Sorry, don't understand your question.

StoredProcName combobox (drop-down list) working fine now. Next, when we specify StoredProcName, Zeos look up into metadata for parameters list. If it normal procedure, it get it's parameters.

If it packaged procedure (with package name prefix), then parameters list is empty. I suggest, problem in incorrect query. For example, stored procedure MEAL_PKG.DISABLE_MEAL():

Code: Select all

select * from user_arguments where (package_name IS NULL AND object_name like 'DISABLE_MEAL'
                                                 ^^^^^^^ - there

it must be something like:

Code: Select all

select * from user_arguments where (package_name like 'MEAL_PKG' AND object_name like 'DISABLE_MEAL'
                                                 ^^^^^^^^^^^^^^^ - there

Post by **EgonHugeist** » 02.07.2013, 10:01

serbod,

Issue confirmed.

Why it happens: TZStoredPprocdure.SetProcedureNames calls a splitting procedure which decomposes the Object string. The dot is the delimiter. So UncachedGetProcedureColumns have the package name as SchemaName and the ProcedureName is retrieved as is. If there exists a procedure with equal name than we use the wrong parameters or can't find these.

Well i've a patch ready for that behavior of SplitQualifierObjectNames. Now the next function AddEscapeCharsToWilldcards makes troube if the PackageName or ProcedureName is casesensitive. That can be solved too by checking the DatabaseInformations of the DatabaseMetadata. That i haven't done yet and i can going on tonight only.

But before going on; there is another case i want to close too:

Oracle supports Procedure-Overloads. Can you provide a little example how to call such a overloaded procedure-name?

serbod · Post by **serbod** » 02.07.2013, 12:10

EgonHugeist wrote:serbod,
Oracle supports Procedure-Overloads. Can you provide a little example how to call such a overloaded procedure-name?

Sorry, we don't have such procedures in our projects. I try to convince our DBA create one for testing.

UPD: DBA says, that TZSQLProcessor with Oracle reporting some error about wrong line separators. More info will be soon.