I try to write rare errors i got during SAP HANA jobs. Usually I can not find an entry in INTERNET about the error.

Below error was occurring during activation of a scenario.

sap hana java.lang.IllegalStateException: java.sql.SQLException: Table type not found

I created a stored procedure as below

PROCEDURE "SRM_GRP_REPL"."RFX_Business_Scenario::chk_replication" ( out OUTPUT_TABLE table(TOTAL_COUNT integer) )

calling this function is legal. But when I use this in a scenario it generates error.
Unexpected problem ocurred while validating scenario artifacts
java.lang.IllegalStateException: java.sql.SQLException: Table type not found





Probably OPInt have difficulty with a temp table declaration. ( table(TOTAL_COUNT integer) )

Converting it into a real table type fixed the problem.

PROCEDURE "TARGET_SCHEMA"."RFX_Business_Scenario::chk_replication" ( out OUTPUT_TABLE "MYSCHEMA"."DAILYCOUNT" )

SAP Hana Analyrical Privilege Implement Super User

You have a context calculation view and want to implement a super user who has access to all contents in cockpit.


For this purpose you opened an Authorization table with 2 columns (KEY_NAME KEY_VALUE)


Table AUTH_OPINT

KEY_NAME	KEY_VALUE
SUPERUSER	U1,U2,U3

According to this table U1, U2 and U3 are super users.
When you define below sql as dynamic sql

It will return 'SUPERUSER' if current SESSION_USER is a substring of KEY_VALUE ( "U1,U2,U3" ).



'SUPERUSER' =
(
SELECT KEY_NAME FROM YOURSCHEMA.AUTH_OPINT where KEY_NAME = 'SUPERUSER'
and KEY_VALUE like concat(concat('%', SESSION_USER ),'%')
)

Querying HANA Calculation views from Java

Say you want to extract data from a calculation view.
You need to define a jdbc data source ( SYS_BICDB )on netweaver administration pages.

( Configuration -> Infrastructure -> Application Resources )

Then you will refer calculation view as follows :

String sql = "SELECT GROUP FROM \"_SYS_BIC\".\"MY_Business_Scenario/SPVR_MY_SCENARIO_CONTEXT\" where DEF = '" + rfxId + "' ";

MY_Business_Scenario : name of your scenario
SPVR_MY_SCENARIO_CONTEXT : name of your calculation view.

public String getProcGroup(String rfxId) throws Exception {

  Connection con = null;

  String sql = "SELECT GROUP  FROM \"_SYS_BIC\".\"MY_Business_Scenario/SPVR_MY_SCENARIO_CONTEXT\" where DEF =  '" + rfxId + "' ";

  try {
   InitialContext ctx = new InitialContext();
   DataSource dsource = null;

   dsource = (DataSource) ctx.lookup("jdbc/notx/SYS_BICDB");
   con = dsource.getConnection();

   PreparedStatement q = NativeSQLAccess.prepareNativeStatement(con, sql);

   ResultSet rs = q.executeQuery();
   
   
   String procGroup = "";
   while (rs.next()) {

    procGroup = (String) rs.getString(1);
    
   }

   con.close();
   return procGroup;
  } catch (SQLException e) {
   e.printStackTrace();
   logger.errorT("getProcGroupFail for " + rfxId + " : " + e.getMessage());
  } finally {
   if (con != null) {
    con.close();
   }

  }
  return null;
 }