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 07:23 AM
 
#2
People who like this:0Yes/0No
Administrator
Rating: 7
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

 
#3
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 24, 2017
Company: Smart ID Dynamics

Hello. I was trying to use this guide to do the same thing ( make that oracle connection ) but i had the error shown in the picture below. Mentions : windows server-x64; 1c-x86; oracle_db v11.2; oracle client v12.1; Can someone please help me solve this problem? Another mention is that I need to use OraOLEDB.Oracle as a provider and the connection string to be TNS-less ( I don t have the specified server on the same network ).
Picture link below :
https://imgur.com/a/lsPPH

 
#4
People who like this:0Yes/0No
Administrator
Rating: 7
Joined: Nov 3, 2011
Company: 1C Company

Lucian, hello and welcome! I apologize for the late response.

Did I get it right that you have solved this problem because your latest question implies that you have established the connection?

As for that question, we're investigating this, I'll let you know when I get an answer from the devs.

 
#5
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 24, 2017
Company: Smart ID Dynamics

Yes. I finally managed to connect to the database. My errors were because of the drivers that were used in the connection string. In order for the connection to work you need both 1C and Oracle installed on x86 , or either both on x64. There are conflicts with the drivers in windows if you want to call from different versions ( x64 to x86 and x86 to x64 ). Now I tried to use one function from that database and I received that error. It is obvious that in order to call my function I shouldn't use a query ( it is not a TABLE ), but that error persists. Even if I try creating some sort of a query I receive an error that I don't have a table ( logical as I don t have one ). I researched a lot on the internet but I couldn't find any examples ( and the whole chapter 23 in developer guide ), at least none that works.
If you have any ideas I'm open to suggestions.
Thank you for your interest.

 
#6
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 24, 2017
Company: Smart ID Dynamics

I posted a couple of hours ago the problem I have.
If you need more details, they are in this post:
https://1c-dn.com/forum/forum1/topic1802/

 
Subscribe
Be the first to know tips & trick on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.