Desktop version

Main > Forum > Supplementary > Suggestions, comments, feedback > How do I call a stored procedure in Oracle using 1C:Enterprise?

Forum

Search UsersRules
How do I call a stored procedure in Oracle using 1C:Enterprise?
#1
Just came
Points:: 0
Joined:: May 23, 2017

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?

Profile
#2
Active user
Points:: 0
Joined:: Nov 3, 2011

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

Profile
#3
Active user
Points:: 0
Joined:: Jul 24, 2017

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

Profile
#4
Active user
Points:: 0
Joined:: Nov 3, 2011

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.

Profile
#5
Active user
Points:: 0
Joined:: Jul 24, 2017

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.

Profile
#6
Active user
Points:: 0
Joined:: Jul 24, 2017

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/

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



© 1C LLC. All rights reserved
1C Company respects the privacy of our customers and visitors
to our Web-site.