Error while activating a Stored Procedure containing EXECUTE IMMEDIATE

[Former Member]

Hello,

I have an issue on my account on NEO (Database Version: 1.00.96.00.1432206182).

I get the following error when I try to activate a Stored Procedure from an .hdbprocedure file:

sap.iu.ea.content2:AggregationTestSP.hdbprocedure

Syntax error in procedure object: identifier must be declared; _total_ucnt

and the following stack trace:

java.lang.NumberFormatException: For input string: "4294967295"

  at java.lang.NumberFormatException.forInputString(Unknown Source)

  at java.lang.Integer.parseInt(Unknown Source)

  at java.lang.Integer.parseInt(Unknown Source)

  at com.sap.ndb.studio.dwb.repository.internal.datamapper.CheckResultDataMapper.parseLocation(CheckResultDataMapper.java:108)

  at com.sap.ndb.studio.dwb.repository.internal.datamapper.CheckResultDataMapper.getObject(CheckResultDataMapper.java:63)

  at com.sap.ndb.studio.dwb.repository.internal.datamapper.CheckResultDataMapper.getObject(CheckResultDataMapper.java:1)

  at com.sap.ndb.studio.dwb.repository.internal.datamapper.CheckResultsDataMapper.getObject(CheckResultsDataMapper.java:34)

  at com.sap.ndb.studio.dwb.repository.internal.datamapper.CheckResultsDataMapper.getObject(CheckResultsDataMapper.java:1)

  at com.sap.ndb.studio.dwb.repository.internal.RepositoryClient.activateFiles(RepositoryClient.java:652)

  at com.sap.ndb.studio.dwb.team.internal.runnables.RepositoryActivateRunnable.doRun(RepositoryActivateRunnable.java:104)

  at com.sap.ndb.studio.dwb.team.internal.runnables.RepositoryRunnable.run(RepositoryRunnable.java:82)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryActivateJob.doRun(RepositoryActivateJob.java:34)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob$2.doRun(RepositoryJob.java:332)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob$SubTask.run(RepositoryJob.java:234)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob$SubTasks.run(RepositoryJob.java:192)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob.runInternal(RepositoryJob.java:326)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob.access$0(RepositoryJob.java:259)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob$4.run(RepositoryJob.java:411)

  at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2313)

  at com.sap.ndb.studio.dwb.team.internal.jobs.RepositoryJob.run(RepositoryJob.java:406)

  at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

The code from the file is:

PROCEDURE "SAP_IU_EA"."sap.iu.ea.content2::AggregationTestSP" ( ) 
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER 
  DEFAULT SCHEMA "SAP_IU_EA"
  READS SQL DATA AS
BEGIN
  DECLARE lv_sql NVARCHAR(255) := 'SELECT * FROM "SAP_IU_EA"."sap.iu.ea.content2::Device.DeviceBusinessUsage"';
  EXECUTE IMMEDIATE :lv_sql;
END;

I can create the same Stored Procedure from the SQL Console without problem:

CREATE PROCEDURE "SAP_IU_EA"."sap.iu.ea.content2::AggregationTestSPTOTO" ( ) AS
BEGIN
  DECLARE lv_sql NVARCHAR(255) := 'SELECT * FROM "SAP_IU_EA"."sap.iu.ea.content2::Device.DeviceBusinessUsage"';
  EXECUTE IMMEDIATE :lv_sql;
END;

My environment is as follow:

eclipse.buildId=4.4.2.M20150204-1700

java.version=1.7.0_75

java.vendor=Oracle Corporation

BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en_US

Framework arguments:  -product org.eclipse.epp.package.jee.product

Command-line arguments:  -os win32 -ws win32 -arch x86_64 -product org.eclipse.epp.package.jee.product

Any idea how I could create this SP and activate it from eclipse? I have to use Dynamic SQL for a special case, this is not the final query I will call.

Best regards

[Former Member · Accepted solution]

Hi Nick,

Read-only procedures (having syntax 'READS SQL DATA ') do not allow Dynamic SQL statements. 

>Syntax error in procedure object: identifier must be declared; _total_ucnt

Above error will go if you select the output of query into a table variable in your procedure i.e. without using dynamic sql.

So, if you have to use Dynamic SQL statements, then you have create the procedure without READS SQL DATA syntax.

-Tarun

[lbreddemann]

As I don't know the rest of your application design, this is a shot into the blue, but have you tried setting the DEFAULT schema just like it is in the original procedure?

Could be that the object from the error message is actually present in another schema...

- Lars