How do I call a stored procedure in Oracle using 1C:Enterprise?



This forum is for the exchange of ideas and suggestions regarding the 1C:Developer Network

#1
People who like this:0Yes/0No
Just came
Rating: 0
Joined: May 23, 2017
Company:

Can some one give me a small example of code that teaches me how to set a connection between 1C:Enterprise and a Oracle server and how to call a stored procedure in Oracle from 1C?

Edited: Birtu Andrei Bogdan - Aug 29, 2017 09:23 PM
 
#2
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Nov 3, 2011
Company: 1C Company

Hello and welcome to the forum!

Here's an example that features a simple Oracle function and a simple Oracle procedure. The function returns "Hello, world!", while the procedure does nothing but has some parameters.

Code
create or replace
FUNCTION FUNCTION_0 RETURN VARCHAR
AS
BEGIN
RETURN 'Hello, world!";
END;

Code
create or replace
PROCEDURE PROCEDURE1
(
  PARAM1 IN NUMBER
, PARAM2 IN VARCHAR2
) AS
BEGIN
  NULL;
END PROCEDURE1;

1. In the configuration tree, in the External data sources branch, add an external data source.

2. In this data source, start adding a function (no matter if you have an Oracle function or procedure, you still add a function to the external data source). This opens the "External data source function wizard".

3. Click "Select from list of external data functions" and click Next.

4. In the "Connect to data source" dialog box, enter the database connection string and set DMBS type to Oracle Database, then click OK. A connection string looks like this:

Driver={Oracle in OraClient11g_home1};Dbq=myTNSServiceName;Uid=myUsername;
Pwd=myPassword;


5. Select the function or procedure that you want to use and click Finish. This adds the selected procedures and functions to the Functions branch of the configuration tree.

6. For procedures, double-click a procedure name to open its property palette and then clear the Returns a value check box.

7. If your procedure or function has parameters, ensure that they are listed in the Expression in data source property:

MYUSERNAME.FUNCTION_0()
MYUSERNAME.PROCEDURE1(&1, &2)


Here's a script that calls the procedure and the function:

Code
&AtServer
Procedure Command1AtServer()
   
    ConnectionParameters = ExternalDataSources.ExternalDataSource1.GetCommonConnectionParameters();
    ConnectionParameters.ConnectionString = "Driver={Oracle in OraClient11g_home1};Dbq=myTNSServiceName;";
    ConnectionParameters.DBMS = "OracleDatabase";
    ConnectionParameters.UserName = "myUsername";
    ConnectionParameters.Password = "myPassword";
    ConnectionParameters.StandardAuthentication = True;
   
    ExternalDataSources.ExternalDataSource1.SetCommonConnectionParameters(ConnectionParameters);
    ExternalDataSources.ExternalDataSource1.Connect();
    
    // Use the function name from the configuration tree, you can change it if you like.
    Message(ExternalDataSources.ExternalDataSource1.MYUSERNAME_FUNCTION_0()); 

    // Use the procedure name from the configuration tree, you can change it if you like.   
    ExternalDataSources.ExternalDataSource1.MYUSERNAME_PROCEDURE1(10, "aaa");
   
EndProcedure

&AtClient
Procedure Command1(Command)
    Command1AtServer();
EndProcedure

 
Subscribe
Users browsing this topic (guests: 1, registered: 0, hidden: 0)