Question Re: Can not insert data from temp table in Technology Q&A

Can not insert data from temp table

Former Member — Tue, 13 Nov 2012 09:19:04 GMT

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

Re: Can not insert data from temp table

Ravi_Channe — Tue, 13 Nov 2012 09:37:03 GMT

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

Re: Can not insert data from temp table

Former Member — Wed, 14 Nov 2012 05:57:26 GMT

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

Re: Can not insert data from temp table

Ravi_Channe — Wed, 14 Nov 2012 07:40:47 GMT

Hi Vishwatej,

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

Regards,

Ravi

Re: Can not insert data from temp table

Former Member — Wed, 14 Nov 2012 15:13:53 GMT

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

Re: Can not insert data from temp table

Ravi_Channe — Wed, 14 Nov 2012 15:24:20 GMT

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

Re: Can not insert data from temp table

Former Member — Wed, 14 Nov 2012 15:39:06 GMT

Ravi,

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

Re: Can not insert data from temp table

Former Member — Thu, 15 Nov 2012 14:05:59 GMT

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

Re: Can not insert data from temp table

Former Member — Fri, 18 Jan 2013 13:13:39 GMT

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

Re: Can not insert data from temp table

Ravi_Channe — Fri, 18 Jan 2013 13:35:56 GMT

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

Re: Can not insert data from temp table

lbreddemann — Mon, 21 Jan 2013 09:36:04 GMT

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

Re: Can not insert data from temp table

Former Member — Mon, 21 Jan 2013 09:49:01 GMT

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

Re: Can not insert data from temp table

Former Member — Wed, 30 Jan 2013 21:44:57 GMT

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 )

Re: Can not insert data from temp table

Former Member — Wed, 30 Jan 2013 23:27:14 GMT

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.

Re: Can not insert data from temp table

lbreddemann — Wed, 30 Jan 2013 23:59:38 GMT

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.

Re: Can not insert data from temp table

Former Member — Thu, 31 Jan 2013 17:01:50 GMT

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.

Re: Can not insert data from temp table

Former Member — Thu, 19 Mar 2015 06:55:41 GMT

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""