on 02-18-2014 4:24 PM
Hi folks,
I have a procedure that does some processing on a cursor and as it's processing it writes results to a TEMPORARY table. Finally it joins the temporary table to another physical table. Because it's using a temp table I have this as a read/write procedure.
Now I'm attempting to consume my procedure via a calculation view using a simple CALL myProcedure (:var_out) however I'm getting the following error;
" 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"
Is it not possible to consume a read-write procedure via an SQL script calculation view? If not I question the value of being able to create read-write procedures in the first place.
Thanks,
-Patrick
PS: Ravi if you are reading this is it possible to call my read-write procedure directly from Microstrategy and avoid the calc view completely?
Apologies Patrick, my bad, read too quick after whats been a long day.
As I understand it, calculation views are read-only side-effect free information views. Because of this reason I assume you cannot call a read-write procedure.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hey Jon-Paul,
I was pretty sure you were correct however I was hoping for miracles. Anyway I worked closely with our Microstrategy developer and they were able to consume the procedure directly in a Microstrategy report eliminating the need for a calculation view so that solves my problem for now. So I will close this thread.
Thanks,
-Patrick
It's because I'm looping through a cursor, and for each result I'm doing some complex logic and then at the end of each result I'm setting a flag. I needed a way to basically flag records as I'm looping through my cursor. Originally I was going to write to an array but then found I wanted to update more than one flag so doing an insert into temp table with multiple values per each iteration of my cursor.
Hmmm okay. It sounds like all your requirements are met and you're good to go.
I've been in similar situations, using temp tables, and we were able to re-factor the code to be read-only be "faking" inserts on table variables by doing SELECT and then UNION ALL (essentially appending to the table variable by increments). It's not efficient, but if the number of records is small and querying a column view (i.s. scripted CalcView) is more straight-forward, this approach can have some advantages.
Hi Patrick, there is a way around this, however would not be recommended and most probably not supported!
you can manually create the view as follows:
CREATE COLUMN VIEW "_SYS_BIC"."myViewName" WITH PARAMETERS (indexType=11,
'PROCEDURE_SCHEMA'='_SYS_BIC',
'PROCEDURE_NAME'='myProcedureName';
Try it out. As I say probably not a supported way of doing it but has worked for me in the past for procedures where I am writing to temp tables.
Peter
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Patrick,
Do you have the following in your procedure, if so remove it (and place AS in the previous line):
READS SQL DATA AS
If you are on Rev.68 there is also a system configuration setting you will probably need to amend - Configuration>repository>sqlscript_mode>UNSECURE (if it's not there then create it).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jon-Paul,
Actually I have both those things. In the procedure being called it's indeed setup without the READ SQL DATA AS. The issue however, I believe, is that the calculation view is not allowing that type of procedure to be used. ie: I have calc views I created that consume READ procedures successfully however I can't get this write type to work with the calc view.
-Patrick
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.