cancel
Showing results for 
Search instead for 
Did you mean: 

Calling a read-write procedure from a read-only calc view

patrickbachmann
Active Contributor
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

former_member182500
Contributor
0 Kudos

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.

patrickbachmann
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hey Patrick -

Just curious why you couldn't use a table variable rather than a temp table?

Also, from a performance perspective - read/write procedures may result in limited parallelization.

Just a few thoughts as you moved forward with your result.

Cheers,

Jody

patrickbachmann
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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.

patrickbachmann
Active Contributor
0 Kudos

That is indeed interesting work around.  I'm dealing with a lot of records so I think this wouldn't work in my case but cool idea that I will stash in the back of my mind for the future! 

Answers (2)

Answers (2)

former_member187673
Active Participant
0 Kudos

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

patrickbachmann
Active Contributor
0 Kudos

Thanks Peter I will give that a shot!

former_member182500
Contributor
0 Kudos

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).


patrickbachmann
Active Contributor
0 Kudos

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