How to call oracle function with no parameter from SQL Server

Question

I need to call an Oracle function Function PK_DM_API_EDIT_ENTITY.DONE from SQL Server.

I call it like this in Oracle:

begin
    PK_DM_API_EDIT_ENTITY.CHANGE ('dep', '322'); 
    PK_DM_API_EDIT_ENTITY.SET_VALUE ('id_sub', '102'); 
    PK_DM_API_EDIT_ENTITY.DONE;
end

It works fine in Oracle - string was updated.

But if I call this function from SQL Server:

EXECUTE ( 'begin PK_DM_API_EDIT_ENTITY.CHANGE ( ?,? ); end; ', 'dep', '322') AT [MSD]
EXECUTE ( 'begin PK_DM_API_EDIT_ENTITY.SET_VALUE ( ?,? ); end; ', 'id_sub', '102') AT [MSD]
EXECUTE ( 'begin ? := PK_MDM_API_EDIT_ENTITY.done; end;') AT [MSD]

Then the string is not updated. I don't know why... can you please help?

Calling an Oracle function from SQL Server Linked Server

Abra
– Abra

2024-01-16 14:47:44 +00:00
Commented Jan 16, 2024 at 14:47 — Abra
– Abra, Commented Jan 16, 2024 at 14:47

MT0 · Accepted Answer · 2024-01-16 15:32:42Z

0

Are you sure your code is a function?

I call it like this in Oracle:
begin
   PK_DM_API_EDIT_ENTITY.CHANGE ('dep', '322'); 
   PK_DM_API_EDIT_ENTITY.SET_VALUE ('id_sub', '102'); 
   PK_DM_API_EDIT_ENTITY.DONE;
end
It works fine in Oracle - string was updated.

That would not work if PK_DM_API_EDIT_ENTITY.DONE is a function as you do not assign the return value to a variable. However, it would work if PK_DM_API_EDIT_ENTITY.DONE is a procedure.

fiddle

If it is a PROCEDURE (and not a FUNCTION) and you want to execute it from SQL Server then just use exactly the same code that worked in Oracle.

EXECUTE ( 'begin PK_DM_API_EDIT_ENTITY.CHANGE ( ?,? ); PK_DM_API_EDIT_ENTITY.SET_VALUE ( ?,? ); PK_MDM_API_EDIT_ENTITY.done; end; ', 'dep', '322', 'id_sub', '102') AT [MSD]

or:

EXECUTE ( 'begin PK_DM_API_EDIT_ENTITY.CHANGE ( ?,? ); end; ', 'dep', '322') AT [MSD]
EXECUTE ( 'begin PK_DM_API_EDIT_ENTITY.SET_VALUE ( ?,? ); end; ', 'id_sub', '102') AT [MSD]
EXECUTE ( 'begin  PK_MDM_API_EDIT_ENTITY.done; end;') AT [MSD]

answered Jan 16, 2024 at 15:32

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kirik Over a year ago

PK_DM_API_EDIT_ENTITY - it is a package

MT0 Over a year ago

@Kirik PK_DM_API_EDIT_ENTITY is a package but what is PK_DM_API_EDIT_ENTITY.DONE? You state in the question that it is a function but the way you state "works" you are using it as if it is a procedure (and your code would have invalid syntax if it was a function) which is why I am questioning whether your description of it is correct.

Collectives™ on Stack Overflow

How to call oracle function with no parameter from SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related