Oracle Database Discussions

You have to grant access directly to your user.

GRANT EXECUTE ON dbdel_proc TO user;

Link work through sysdba firebird user and link create as public.

Yes, it worked for tables. You'll need execute grant to execute procedures. The fact that your db link is public as nothing to do with grant on procedure.

In remote database i have all grants on this procedure, if I grant function in oracle database I have:

ORA-02021: DDL operations are not allowed on a remote database

Are you sure you have the name of the procedure correct? Do you need to qualify the procedure name with the owner? I can reproduce that error calling an oracle procedure over a link if I have the incorrect name for the procedure or if the procedure is owned by someone other than the user specified in the link

Maybe this help about my problem:

On firebird side I create two procedures

First procedure (maybe analog pipelined function in oracle):

firebird code:

create or alter procedure show_country

returns (

  id char(2),

  name varchar(30)

)

as

begin

  for select id, name from country order by id

  into :id, :name

  do

    suspend;

end;

In oracle:

select * from show_country@firebird_link;

returns me rows from firebird table country

Second procedure:

firebird code:

create or alter procedure edit_country (

  id char(2),

  name varchar(30)

)

as

begin

  update country set name = :name where id = :id;

end;

In oracle:

begin

  edit_country@firebird_link('TT','TEST');

end;

PLS-00201: identifier 'EDIT_COUNTRY@FIREBIRD_LINK' must be declared

I find answer how do this in documentation oracle :

declare

  num_rows integer;

begin

  num_rows := dbms_hs_passthrough.execute_immediate@firebird_link('EXECUTE PROCEDURE EDIT_COUNTRY(''TT'', ''TEST'')');

end;

after this statement I see update on firebird side.

Thank you all, oracle docs very helpfull for me.