Create local temporary table in procedure

[#1]

Hi folks,

I have a procedure (I wrote in the procedure editor in Project Explorer).  In the procedure I'm trying to create a local temporary column table however when I attempt to activate the procedure I get error;

feature not supported: DDL is not supported in the READ ONLY procedure

In the past when I created a procedure via old method (via CONTENT/NEW PROCEDURE) I had the option of choosing READ or READ/WRITE.  I'm not seeing this option in the new procedure editor.  Also I noticed a second tab next to SQLScript tab called LOCAL TABLE TYPES.  I can't seem to place my create local temporary table there either.

I've read in other posts that this is possible... what do I need to do in order to be able to create and update this temp table?

Thanks,

-Patrick

[vivekbhoj]

In the past when I created a procedure via old method (via CONTENT/NEW PROCEDURE) I had the option of choosing READ or READ/WRITE.  I'm not seeing this option in the new procedure editor. 

-> I still get this option when trying to create a procedure from my package in the content folder -

this option is not available when you try creating a procedure in Project Explorer in your project

[thomas_jung]

Sure you can specify READ or READ/WRITE.  Its just in the syntax toward the beginning right before the BEGIN

For Read Only (the default)

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

For Dynamic or READ/WRITE you remove the READS SQL DATA and just have AS

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS

BEGIN

[vivekbhoj]

Thanks Thomas,

Earlier I tried removing READS SQL DATA AS but I removed the whole line and it gave me error

with keeping AS everything works fine

Regards,

Vivek

[patrickbachmann]

Thomas I tried your suggestion (removing READS SQL DATA) and now when I activate I get this error;

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [258]: insufficient privilege:  [258] _SYS_BIC.MyPackage/MyProc: line 7 col 1 (at pos 317): [258] (range 3) insufficient privilege exception: insufficient privilege: Not authorized

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:334)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:174)

  at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102)

  at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1142)

  at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:886)

  at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:489)

  at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:338)

  at com.sap.db.jdbc.trace.CallableStatement.execute(CallableStatement.java:1142)

  at com.sap.ndb.studio.sqlscript.debugger.core.communication.jdbc.SQLScriptDbgJdbcHandler.executeEditorStoredProcedure(SQLScriptDbgJdbcHandler.java:135)

  at com.sap.ndb.studio.sqlscript.debugger.core.communication.SQLScriptDbgConnectorManager.executeEditorStoredProcedure(SQLScriptDbgConnectorManager.java:33)

  at com.sap.ndb.studio.sqlscript.debugger.core.model.SQLScriptDbgSessionManager.executeEditorStoredProcedure(SQLScriptDbgSessionManager.java:293)

  at com.sap.ndb.studio.sqlscript.debugger.core.threads.ExecuteStoredProcedureThread.run(ExecuteStoredProcedureThread.java:25)

  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

  at java.lang.Thread.run(Unknown Source)

Any thoughts?

-Patrick

[patrickbachmann]

Guys,

By the way I am still on rev 68 not rev 70 as most of you are on.  I'm trying to build my case for upgrading to 70 here.  I've also noticed the procedure editor is not very stable.  I'm constantly getting freezes in the procedure editor or debug terminating randomly etc.

-Patrick

[patrickbachmann]

Also I think I have isolated the problem to this line of code;

CREATE LOCAL TEMPORARY COLUMN TABLE "#RECEIPTS" ("PO" NVARCHAR(10),

  "POLINE" NVARCHAR(2),

  "DATERECV" NVARCHAR(8),

  "QTYRECV" DECIMAL(13,

  3) CS_FIXED NOT NULL ,

  "QTY" DECIMAL(13,

  3) CS_FIXED) ;

However I can run this fine via SQL editor.  But via procedure I get the insufficient privilege error.  Which makes me wonder if it's not my username privileges that are the problem but some other user??  ie: when calling the procedure who's rights are used?

-Patrick

[patrickbachmann]

Wow, I can't believe I have stumped all you geniuses.  Sigh.  Especially the great powerful Oz (Lars).  Well I created a message with SAP on this so will update this thread hopefully soon. 

-Patrick

[lbreddemann]

Hmm... didn't see your post before,,,

Anyhow, I think the error message is simply misleading and for SQLScript temporary column tables are not supported yet.

Does it work without the column keyword?

- Lars

[patrickbachmann]

Perhaps.  I believe I tried yesterday without the column keyword.  Let me try again...

[patrickbachmann]

Wait actually I used your example which does not have column so yes I tried that.  Thus far SAP has said nothing about version, they are going to logon to our system and try for themselves.

[former_member182302]

Adding to that ,

I just tried Column, and it works.

Regards,

Krishna Tangudu

[patrickbachmann]

Krishna are you also on 70 like Lars?

Thanks

[former_member182302]

No patrick am on 68. have a question to you,,

were you able to activate a Read procedure w/o Temporary table?

And you are getting issues only while creating Read-Write procedure with Temporary table?? ( Also did you try activating a Read Write procedure w/o Temporary Table)??

Regards,

Krishna Tangudu

[patrickbachmann]

Krishna, that's very promising news that you have 68!  I was afraid we would have to upgrade to get this feature.  To answer your question, yes I have created and successfully activated and used other procedures that are doing more simplistic read-only things and I have definitely pinpointed the problem to the specific create temporary table function.

-Patrick