Calling a stored procedure/function from Oracle.

Hello!!
My name is Lucian. I am relatively new to 1C and I have a difficulty in calling a stored procedure or function fr om 1C. I succesfully connected to the needed database through a external data source and now I need to call a procedure/function from that database. Few mentions: I am connected to the database with a user diiferent from "SYS"; even tough I am not using "SYS" I have complete access from the database and I can add the required function to my External Data Source in functions; the designer recognises the function because I can see the needed parameters for it. The only problem I think I have, is that I am not calling it properly and I didn't found any examples for 1C.
Can anyone please help me ?

Function text in Oracle is :

create or replace FUNCTION smart_sum(x IN NUMBER, y IN NUMBER)
  RETURN NUMBER IS ret_val NUMBER(11,2);
  BEGIN
     SEL ECT x + y
     INTO ret_val
     FROM DUAL;
     RETURN(ret_val);
   END;  

Error received while calling the function is:

{DataProcessor.ORCL.Form.Form.Form(18)}: Error calling context method (SYS_SMART_SUM): External database error:
error executing query: ODBC error. SQLSTATE: HY000
Error number: 923
Description: [Oracle][ODBC][Ora]ORA-00923: FR OM keyword not found wh ere expected

I would very much appeciate your help!

Lucian,

actually, this should work.

Possibly the value of "DBMS" connection parameter is incorrect, please check it. For your source, it must be "OracleDatabase".

Please let me know if this helped.

Thank you for your short answer.
I figured it out. The error was received from Oracle ( since the code for the error was ORA-... ). In 1C, when you import a procedure/function , your 1C will automatically complete for that specific function the data field in "Expression in the data source". There I modified the expression from sys.smart_sum(&1, &2) to the call of the function from sql developer. The result ended up something like this :"smart_sum(6, 3) from DUAL;". The sintax needed in sql was with keyword "select" up front but I think 1C sends out automatically that keyword when calling the function.
And in 1C the call of the function was as simple as SmartSum = ExternalDataSource.MyDataSource.Sys_smart_sum(param1, param2).
For the moment, the problem is that I don t see the newly created functions in Oracle. When I connect to the database I don t find these functions. Guess that's some update feature somewhere that's not working.

Ok. Sorry for being so "unprofessional".
I have a new question. The return values from a function in external data type are quite limited in 1C ( or there may be something that has not been considered so far or a bug ). In a few words, I can only sel ect as a return type : number, string, date, bool, uuid and binary data. As far as I am concerned a function can return any data type : valuetable, table, array etc. In my case I have a function that returns a table with numbers in it. I have considered the fact that you can call your procedure/function from 1C, that specific procedure/function does what's supposed to do and the result is put into a TABLE from Oracle. This is a happy case because I think I can add a table in 1C which links to that table and get my data from there. But what if I don t have that table in Oracle; what if there is created just a temporary table and I need to get my data all at once? In my case I just receive the first number fr om the table and that's it. Can someone please help me ?

Lucian,

it is totally okay to ask questions that might seem "unprofessional". That's how one becomes a professional

Please explain the exact scenario. Did I get it right that  you create a function in Oracle, then run the external data source function wizard in Designer, and it cannot find the new function?


Yes, you can use a function that returns a table. First, in the configuration tree, add a table to your external data source. In the table properties, on the Data tab, set the table type to Expression. Then, in the Expression in data source field, specify the function with table columns used as parameters.

For example, if your function name is MYFUNCTION and it has 3 columns, in the Expression in data source field, enter MYFUNCTION(&1, &2, &3).

Note that, instead of settings all these options manually, you can use the table creation wizard, which can automatically discover the function in your data source.

You got the first question right. The problem with finding the function was solved after a system restart.
So, to sort out things. I found MyTable in external data source wizard ( when I sel ect it the type field says that is a "non-object data"). I added it to MyExternalDataSource. In the Data tab it is automatically completed like you said: Table type = "Expression"; Expression in the data source = "TABLE(MyFunctionName(&1, &2))"-the function has 2 parameters (numbers); Further below i have 2 fields ( TEST_ID , TEST_DESC ) which are the columns names from my table. Now the big question is: How do I call the function and how do I get through all the values which sould be returned?

Mentions: If I do something like this: SomeData = ExternalDataSources.Oracle.MyFunctionName(param1, param2), SomeData receives just the first item value ( first column, first line ); Another thing that I discovered is that, when I add a function to MyExternalDataSource with the help of the wizard, in PROPRIETES->DATA tab it adds the name of the function with the parameters that you specified ( no problems so far ). When I try to call a function I receive some errors from Oracle ( errors that are solved by modifying the Expression in the data source with the call of the function from Oracle; something like this: * FR OM TABLE(MyProcedureName(&1, &2)); ). Another mention is that if , in the function PROPRIETES, I want to select the type of the return value, I only have a few types available ( number, string, date, bool, uuid, binarydata ). By playing a little bit with 1C, I found out that if I add a table in MyExternalDataSource and set it's data proprietes to "Table"and set the Data type of external data source table to "Object Data", the table that I set in this way, appears as a return value for the functions in MyExternalDataSource. Unfortunately, this either does not meet my needs because even though I receive a record in SomeData mentioned above, it returns just the first one.

Sorry for the misspelled words and rough explanations.
Thank you for your help and I hope you can give me more suggestions for my problem.

Lucian,

actually, you do not need an external data source function at all. You need an external data source table with Expression in data source specified.

You can use this table in:

• Queries (specify the parameters after the table name in parentheses, comma-separated)
  
• Dynamic lists (use a custom dynamic list query that includes the parameter values)

When 1C:Enterprise generates a query to a data source, it fills the FROM section as follows:

• If the table type is Table: NameInDataSource
  
• If the table type is Expression in data source: ExpressionInDataSource

So, the entire expression that you specify goes to the FROM section of the query to the data source.

Example 1

You create a table Table1 with columns Col1 and Col2. Its NameInDataSource is "MyTable1". The query that you specify in the platform is:

SELECT
T1.Col1,
T1.Col2
FROM
ExternalDataSource.ExternalDataSource1.Table.Table1 AS T1

The query to the data source is:

SELECT
T1.Col1,
T1.Col2
FROM
MyTable1 AS T1

Example 2

The table's ExpressionInDataSource is "MyFunc1(&1, &2)".

The query that you specify in the platform is:

SELECT
T1.Col1,
T1.Col2
FROM
ExternalDataSource.ExternalDataSource1.Table.Table1(100, 67) AS T1

The query to the data source is:

SELECT
T1.Col1,
T1.Col2
FROM
MyFunc1(100, 67) AS T1

Example 3

The table's ExpressionInDataSource is "(SELECT 100 AS Col1, 500 AS Col2 FROM DUAL)".

The query that you specify in the platform is:

SELECT
T1.Col1,
T1.Col2
FROM
ExternalDataSource.ExternalDataSource1.Table.Table1 AS T1

The query to the data source is:

SELECT
T1.Col1,
T1.Col2
FROM
(SELECT 100 AS Col1, 500 AS Col2 FROM DUAL) AS T1

Thank you for your help.
I succeded in retreiving the data I needed from the table.
I very much appreciate your help and I have one more question. If i have just a function in Oracle called MyFunction(&1) and the syntax in SQL developer for calling it is like " sel ect MyFunction(myValue) fr om Dual " how do I call that function in 1C and how do I retrive it's data?
Thank you very much,
Lucian.

I succeeded with calling the function I needed. Thank you very much fro your help.
Lucian.