Print Thread
Package wizard does not interface procedure without parameters
#62685 08/19/21 09:26 AM
Joined: Mar 2005
Posts: 26
Italy
R
RamosIt Offline OP
Member
OP Offline
Member
R
Joined: Mar 2005
Posts: 26
Italy
Hello I had a look to a previous old thread but it seems to be too old to be re-activeted so i'm trying to write a a new thread about following issue:

I'm using following DB version:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

When i try to create Delphi interface to a package using Package Wizard all procedures without parameters are not listed and then of course not interfaced in pas file.

Is there the possibility to debug wizard procedure or consult log?

I really have no idea why this is happening.

Thank you

Re: Package wizard does not interface procedure without parameters
RamosIt #64170 08/25/22 07:41 AM
Joined: Aug 2022
Posts: 3
S
Member
Offline
Member
S
Joined: Aug 2022
Posts: 3
All of package procedures without arguments are ignored by DOA Package Manager. That happens because the Package Manager reads the sys.all_arguments - View (see source code below). But "starting with Oracle Database 12c release 1 (12.1.0.2), this view omits procedures with no arguments. Prior to Oracle Database 12c release 1 (12.1.0.2), a procedure with no arguments was presented as a single row in this view" ( https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/ALL_ARGUMENTS.html )


That's why I patched the procedure TPackageDefinition.ReadFromDictionary from unit OracleTools.pas (see lines between ---PATCH-BEGIN--- and ---PATCH-END--- ) and now my Package Manager shows procedures without arguments at the bottom of the list. I tested my patch with both Oracle 9 and 19 - it works fine.


//UNIT OracleTools.pas
PHP Code

// Read the package definition from the database dictionary
procedure TPackageDefinition.ReadFromDictionary;
var Query: TOracleQuery;
	Obj: TPackageObject;
	LastObject, LastOverload: string;
	ThisObject, ThisOverload: string;
	ThisDataType: string;
	Par, ParentParam: TPackageParameter;
	ThisDataLevel: Integer;
	ParentIndex: Integer;
begin
  FObjects.Clear;
  Query := TOracleQuery.Create(nil);
  try
	Query.Session := Session;
	Query.SQL.Add('select * from sys.all_arguments');
	Query.SQL.Add('where package_name = :package_name');
	Query.SQL.Add('and owner = :owner');
	Query.SQL.Add('order by object_name, overload, sequence');
	Query.DeclareVariable('package_name', otString);
	Query.DeclareVariable('owner', otString);
	Query.SetVariable('package_name', Name);
	Query.SetVariable('owner', Owner);
	Query.Execute;
	while not Query.Eof do
	begin
	  ThisObject   := Query.Field('object_name');
	  ThisOverload := Query.Field('overload');
	  LastObject := ThisObject;
	  LastOverload := ThisOverload;
	  Obj := TPackageObject.Make(Self, ThisObject, ThisOverload);
	  repeat
		ThisDataLevel := Query.Field('data_level');
		ThisDataType  := Query.Field('data_type');
		Par := nil;
		if ThisDataType <> '' then
		begin
		  if ThisDataLevel = 0 then
		  begin
			Par := TPackageParameter.Make(Obj, Query.Field('argument_name'));
		  end else begin
			ParentIndex := Obj.FlatParameterCount;
			repeat
			  Dec(ParentIndex);
			  ParentParam := Obj.FlatParameters[ParentIndex];
			until (ParentParam.DataLevel = ThisDataLevel - 1) or (ParentIndex = 0);
			Par := TPackageParameter.MakeSub(ParentParam, Query.Field('argument_name'))
		  end;
		end;
		if Par <> nil then
		begin
		  Par.FDataLevel := ThisDataLevel;
		  Par.FSequence := Query.Field('sequence');
		  Par.FMode := pmIn;
		  if Query.Field('in_out') = 'OUT' then Par.FMode := pmOut;
		  if Query.Field('in_out') = 'IN/OUT' then Par.FMode := pmInOut;
		  Par.FDataType := Query.Field('data_type');
		  Par.FDefaultValue := Query.Field('default_value');
		  Par.FDataLength := Query.Field('data_length');
		  Par.FDataPrecision := Query.Field('data_precision');
		  Par.FDataScale := Query.Field('data_scale');
		  if Query.FieldIndex('type_owner') >= 0 then
			Par.FTypeOwner := Query.Field('type_owner')
		  else
			Par.FTypeOwner := '';
		  if Query.FieldIndex('type_name') >= 0 then
			Par.FTypeName := Query.Field('type_name')
		  else
			Par.FTypeName := '';
		  if Query.FieldIndex('type_subname') >= 0 then
			Par.FTypeSubName := Query.Field('type_subname')
		  else
			Par.FTypeSubName := '';
		end;
		Query.Next;
		if not Query.Eof then
		begin
		  ThisObject   := Query.Field('object_name');
		  ThisOverload := Query.Field('overload');
		end;
	  until Query.Eof or (ThisObject <> LastObject) or (ThisOverload <> LastOverload);
	  Obj.Selected := Obj.IsSupported;
	end;

	// ---PATCH-BEGIN---
	Query.SQL.Clear;
	Query.SQL.Add('select p.PROCEDURE_NAME');
	Query.SQL.Add('from all_procedures p');
	Query.SQL.Add('where');
	Query.SQL.Add('   p.OWNER = :owner and');
	Query.SQL.Add('   p.OBJECT_NAME = :package_name and');
	Query.SQL.Add('   p.PROCEDURE_NAME is NOT NULL and');
	Query.SQL.Add('   not exists');
	Query.SQL.Add('   (');
	Query.SQL.Add('	   select 1');
	Query.SQL.Add('	   from sys.all_arguments aa');
	Query.SQL.Add('	   where');
	Query.SQL.Add('		aa.OWNER = p.OWNER and');
	Query.SQL.Add('		aa.PACKAGE_NAME = p.OBJECT_NAME and');
	Query.SQL.Add('		aa.OBJECT_NAME = p.PROCEDURE_NAME');
	Query.SQL.Add('   )');
	Query.SQL.Add('order by p.PROCEDURE_NAME');
	Query.Execute;
	while not Query.Eof do
	begin
	  ThisObject   := Query.Field('PROCEDURE_NAME');
	  ThisOverload := '';

	  //Obj :=
	  TPackageObject.Make(Self, ThisObject, ThisOverload);

	  Query.Next;
	end;
	// ---PATCH-END---

  except
	FObjects.Clear;
	Query.Free;
	raise;
  end;
  Query.Free;
end;
 

Last edited by Simon K.; 08/25/22 09:28 AM.
Re: Package wizard does not interface procedure without parameters
RamosIt #64175 08/25/22 09:33 AM
Joined: Aug 2022
Posts: 3
S
Member
Offline
Member
S
Joined: Aug 2022
Posts: 3
P.S.
I hope that my patch will be added to the next version of DOA smile

Last edited by Simon K.; 08/25/22 02:36 PM.
Re: Package wizard does not interface procedure without parameters
RamosIt #64275 10/14/22 01:12 PM
Joined: Aug 2013
Posts: 32
A
Member
Offline
Member
A
Joined: Aug 2013
Posts: 32
Hi,
We have reported this bug to support@allroundautomations.com in 2019.
Still no solution.

Re: Package wizard does not interface procedure without parameters
Simon K. #64343 12/06/22 01:30 PM
Joined: Mar 2005
Posts: 26
Italy
R
RamosIt Offline OP
Member
OP Offline
Member
R
Joined: Mar 2005
Posts: 26
Italy
Thank you for patch but unfortunatelly i haven't source smirk


Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.028s Queries: 15 (0.008s) Memory: 2.5726 MB (Peak: 3.0377 MB) Data Comp: Off Server Time: 2024-03-29 05:43:31 UTC
Valid HTML 5 and Valid CSS