Skip to Content

Introduction:

I have seen there is redundant work involved in creating Virtual table after setting up connection in HANA. So here is a tool which you can create in your schema to quickly create the tables.

My Scenario:

I have a scenario where I have to move an application from old SP09 landscape to SP11 landscape where most the Calculation views have virtual table.  In my scenario we have both the SCHEMA_NAME and the source is also HDB. So I have 2 scenario to cover to make it more generic.

  1. A. User will pass SOURCE_SCHEMA_NAME and TARGET_SCHEMA_NAME along with the SOURCE_TABLE_NAME
  2. B. User will pass SOURCE_SCHEMA_NAME and the SOURCE_TABLE_NAME

We can also replicate the scenario with minor modification to my case.

Problems faced while moving the Calculation View:

  1. 1. If I would have moved the package directly it would have shown error in activation after import.

So I need to create all the virtual tables in the new system. So manually creating VT is a time consuming and repetitive task. So I have written a HDB Procedure which will create the VT in one click.

The HDB Procedure code is as follows:

I have created the Procedure in my local Schema, as this is one time activity and doesn’t required to be used across landscape or some other user.


PROCEDURE "CHANDAN"."users.CHANDAN_PK::VT_AUTO_CREATE"(
  IN v_schema NVARCHAR(20),
 -- IN v_targ_schema NVARCHAR(20),
  IN v_table NVARCHAR(50),
  OUT v_message NVARCHAR(100)
 )
 LANGUAGE SQLSCRIPT
 SQL SECURITY INVOKER
AS
BEGIN
 DECLARE v_remote_src NVARCHAR(20) := null;
 DECLARE v_remote_src_sch NVARCHAR(20) := null;
 DECLARE v_table_exists NVARCHAR(20) := null;
 DECLARE v_statement NVARCHAR(200) := null;
 SELECT TOP 1
    REMOTE_SOURCE_NAME
  INTO v_remote_src
  FROM "SYS"."REMOTE_SOURCES";
 SELECT TOP 1
    REMOTE_OWNER_NAME
  INTO v_remote_src_sch
  FROM "SYS"."VIRTUAL_TABLES"
  WHERE SCHEMA_NAME = :v_schema;
 SELECT count(*)
  INTO v_table_exists
  FROM "SYS"."VIRTUAL_TABLES"
  WHERE TABLE_NAME = :v_table;
 IF (v_table_exists = 0) THEN
  v_statement := 'CREATE VIRTUAL TABLE ' || :v_schema || '.' ||:v_remote_src||'_'|| :v_table || ' AT ' || :v_remote_src || '."<NULL>".' || :v_remote_src_sch || '.' || :v_table;
 -- v_statement := 'CREATE VIRTUAL TABLE ' || :v_schema || '.' || :v_table || ' AT ' || :v_remote_src || '."<NULL>".' || :v_targ_schema || '.' || :v_table;
  EXEC :v_statement;
  SELECT 'Table Created in Schema ' || :v_schema
   INTO v_message
   FROM dummy;
 ELSE
  SELECT 'Table already Exist in Schema ' || :v_schema
   INTO v_message
   FROM dummy;
 END IF;
END;

Tested the procedure by Invoking the Procedure once in SQL Console. If it executes fine. Copy the call for other Tables in the SQL Console.

/wp-content/uploads/2016/06/1_982538.jpg

/wp-content/uploads/2016/06/2_982539.jpg

Better to use Concatenate function in excel to prepare the call statement when you have all the table ready in one excel. If table list is not ready, you can check the SYS.VIRTUAL_TABLES and copy the table list from there.


/wp-content/uploads/2016/06/3_982540.jpg


You can create this procedure and call this in UI via XSJS services too, which I will try adding in my next blog.

Hope this will help other projects. This blog is based on my personal experience, and any suggestion or correction is most welcome to improve this further.

Chandan Praharaj

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply