Can not insert data from temp table

[Former Member]

Hi all,

in HANA Studio i am trying to execute a procedure by calling the procedure with overview in order to fill a predefined column table with the procedure result.

Now when executing the procedure I get below error message in Log:

SAP DBTech JDBC: [1301]: numeric or value error: [1301] BIAPPL.CE_SQL_TEST2: line 15 col 2 (at pos 452): SQLException1301 exception

When i execute the same procedure call without passing the table name but NULL it will write it in a temp table where i can read then the data from.

Does anyone else already faced this issue?

BR,

Bastian

[Former Member]

Hi Bastian,

Just FYI I'm facing the same error (SQLException 1301 - numeric or value error). Haven't had any luck narrowing down the source - it's in the middle of lots of SQLScript, and when I start whittling down my script (not touching the problematic code) the error magically disappears... will let you know if I find anything out.

Cheers

Jody

(Lars, if I can simplify the code and reproduce the error I'll post it - you tend to see right through these things )

[Former Member]

Not sure exactly what the source was, seems HANA was being finicky about storing data in a temp variable. All I did was create a temporary table and store the data there, solved the problem.

[lbreddemann]

Well I also tried just laying my hands on - but this never worked

Without the source code for the failing procedures, I really can't do anything.

[Former Member]

Well the source code is quite involved and the bug was quite obscure so I figured I'd wait to post it. Workaround above seems to be working, but if that changes I may post some snippets. Thanks, Lars.

[Former Member]

I got something like this, but the same script works fine if I use it with subset of the data-sets I am trying to process. Looks like it ran out of memory? I am going to increase memory and try it out, in the meanwhile if anyone had any idea?

Could not execute 'CALL _SYS_AFL.PAL_DT(V_DT_DATA, DT_PARAMS, DT_MODEL_JSON, DT_MODEL_TREEMODEL) WITH OVERVIEW' in 162 ms 515 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed;Can not insert data from temp table "SYSTEM:P3_550A04985809171AE1000000AC1F001B (-1)" into table ""TITANIC"."DT_MODEL_JSON""

[lbreddemann]

Hello Bastian,

please provide the DDL for the procedure and the calc view that you're trying to build.

Also, it's always a good idea to mentioned what HANA revision you're using.

I just tried to create a script based calc view that runs a read/write stored procedure and correctly got this activation error (I edited in some line breaks to improve readability):

[...]

Internal deployment of object failed;
Repository: Encountered an error in repository runtime extension;
Internal Error:Deploy Calculation View:
SQL: transaction rolled back by an internal error:
EXPLAIN PLAN and CALL statement are not allowed:
CALL for non read only procedure/function is not supported in the READ ONLY procedure/function:

[...]

Based on your description of your requirements I assumed that you would have seen the same error.

Since you apparently had been able to deal with that, I wonder how your procedure looks like.

- Lars

[Former Member]

Hi

I think you cant insert a record to the table with HANA procedures.

As far as i believe procedures in HANA are useful only for select statements.

Hope it helps!!!

Regards

vishwatej

[Ravi_Channe]

Hi Vishwatej,

You can insert / update / delete data records to a table through HANA procedure.

Regards,

Ravi

[Former Member]

Ravi, Not if you call the procedure from CV right?

[Ravi_Channe]

Hi Muruga,

I am not sure what do you mean when you say "call a procedure from Calculation view". The original question was to use a table and insert the data to the table from the result view of a procedure. This should be possible, but does not necessarily need a result view. The data can be inserted to a table inside a procedure.

If the procedure is returning a result view, then I think even that can be used in another procedure to insert the data to a table.

If you are talking about a script based calculation view, then in that case you are right. It is read operation, which means you use SELECT statement in the calculation view.

But I think the original question was related to procedure and did not mention any Calc View.

Regards,

Ravi

[Former Member]

Ravi,

Agreed, Just wanted to state that you cannot DDL functions in Script Based Calculation View that's all.

[Former Member]

Hi Muruga,

Calculation View are read only, you even don't have this option when creating it. And to call a procedure which is readwrite you must call it from outside or for an RW object.

You only have the option to set read write for procedures.

So, my conclusion is that you can't change or call RW procedures from an CV which is read only.

Regards, Fernando Da Rós

[Former Member]

Hi Friends,

I am also having the same issue similar to Bastian. I have created a procedure and written a SQL statement to fill my temperory table, I am able to successfully execute my procesure. But when I am calling ,my procedure from SQL editor, it is giving the following error :-

Could not execute 'CALL "_SYS_BIC"."xxx/STEP/PROCESS_STEP"(?)' in 204 ms 62 µs Started: 2013-01-18 18:38:17. SAP DBTech JDBC: [1301]: numeric or value error: [1301] _SYS_BIC.STEP/PROCESS_STEP: line 49 col 3 (at pos 1709): SQLException1301 exception

Thanks in advance.

Siddhartha

[Ravi_Channe]

Hi Siddhartha,

I'd suggest you open a new thread for the discussion and also it will help if you can post the code.

Regards,

Ravi

[Former Member]

Hi Siddhartha,  You can not use a DDL statment (Write statement) inside a calculation view. As Fernando rightly mentioned, a calculation view is of Read Only nature and can not call a Read-Write Procedure.  Regards, Piyush

[Ravi_Channe]

Hi Bastian,

Is the table name passed as the input to the procedure. Can you please post the procedure declaration and call statement.

Regards,

Ravi