[patch_done] Oracle - stored procedures packages
[patch_done] Oracle - stored procedures packages
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
бесплатный хостинг картинок
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
бесплатный хостинг картинок
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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:
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;
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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..
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..
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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:
Parameters names in packages is same, no additional prefixes.
Now look at pictures above - procedure names in StoredProcName dropbox embraced by double quotes ( " ):
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:
I don't checks, allow Oracle that prefixes or not, but prefixes in parameters names is unneccesary.
Code: Select all
standard_procedure(param1, param2)
package_name.package_procedure(param1, param2)
Now look at pictures above - procedure names in StoredProcName dropbox embraced by double quotes ( " ):
Code: Select all
"package_name.proc_name"()
^-this ^- and this
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
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
Me too. All i did is to get the examples running i had before.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:
Sure? Really. Think about case sensitive MetaInformations and procedure calls.Oracle don't allow such symbols in procedure name.
This is a unique user backend solution for the parameters. Have a look to:don't checks, allow Oracle that prefixes or not, but prefixes in parameters names is unneccesary.
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;
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..
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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:
TZSQLMonitor reports:
Example 2
try to remove double quotes
StoredProcName=MEAL_PKG.DISABLE_MEAL (without double quotes)
Params:
(none)
code:
Project ZeosTest raised exception class "EDatabaseError" with message:
ZProc: Parameter "DISABLE_MEAL.L_ID" not found
Example 3
try to set procedure name at runtime
StoredProcName='' (none)
Params:
(none)
code:
Project ZeosTest raised exception class "EDatabaseError" with message:
ZProc: Parameter "DISABLE_MEAL.L_ID" not found
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();
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
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();
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
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();
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
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
serbod,
i'll add a test case and verify your suggestions the next week..
i'll add a test case and verify your suggestions the next week..
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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:
I suggest, "where package_name" not correct. Maybe, package name not autodetected from procedure name.
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
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
serbod,
didn't forgot you.. Bussy currently.
didn't forgot you.. Bussy currently.
IIRC are the Paremeter-name of PackageName+StoredProc-name equal to StoredProc-name. Right?they dont't filled on .StoredProcName change.
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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():
it must be something like:
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
Code: Select all
select * from user_arguments where (package_name like 'MEAL_PKG' AND object_name like 'DISABLE_MEAL'
^^^^^^^^^^^^^^^ - there
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
Sorry, we don't have such procedures in our projects. I try to convince our DBA create one for testing.EgonHugeist wrote:serbod,
Oracle supports Procedure-Overloads. Can you provide a little example how to call such a overloaded procedure-name?
UPD: DBA says, that TZSQLProcessor with Oracle reporting some error about wrong line separators. More info will be soon.