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 |