page257
344
As with all code, full testing should be conducted and signed off on before scheduled
deployment.
We do not advocate the automated deployment of generated code.
You can now build a JavaScript stored procedure to generate your code passing
through a single parameter called
P_CLIENT_NAME
to generate the client-specific
containers and objects.
CREATE OR REPLACE PROCEDURE sp_create_share ( P_CLIENT_NAME STRING )
RETURNS string
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var sql_stmt = "";
var recset = "";
var err_state = "";
var result = "";
var client_account = "";
var share_grants = "";
result = "/* Create a role to manage shared objects */\n"
result += "USE ROLE accountadmin;\n"
result += "CREATE SHARE IF NOT EXISTS share_" + P_CLIENT_NAME + ";\n\n"
result += "/* Create a schema for shared objects */\n"
result += "USE ROLE sysadmin;\n"
result += "CREATE SCHEMA IF NOT EXISTS tpc.share_owner_" + P_CLIENT_NAME
+ ";\n\n"
result += "/* Entitle new share to access new schema */\n"
result += "GRANT USAGE ON DATABASE tpc
TO SHARE share_" + P_CLIENT_NAME + ";\n"
result += "GRANT REFERENCE_USAGE ON DATABASE tpc
TO SHARE share_" + P_CLIENT_NAME + ";\n"
Chapter 9
Client expeCtations