Arrays, Dynamic SQL, Exceptions & Transactions

Inside a procedure you run a dynamic SELECT with EXECUTE IMMEDIATE (no INTO). You then try to use its rows later in the code. Why can't you?

You build dynamic SQL that selects from a local table variable :lt_tmp. You try to concatenate :lt_tmp into the statement string the way you do for a scalar id. What breaks, and what's the fix?

Before HANA 2.0 SPS 04, a read-only SAP BW transformation marked READS SQL DATA couldn't use dynamic SQL at all. Why, and what changed?

You want an error-log row to persist even if the calling transaction later rolls back. What block do you wrap the INSERT in, and what happens at the block's end?

Inside a BEGIN AUTONOMOUS TRANSACTION block you try to CALL another procedure and assign a table variable. Why does this fail, and what's the deadlock trap?

Can you raise a built-in SQL error like 304 (division by zero)? What codes may SIGNAL use, and what does it populate?

Inside a handler you log the error, then want to keep propagating it unchanged to the caller. Which statement, and how would you instead enrich the message with the failing procedure's name?

You must build dynamic SQL from user input: a column identifier and a string comparison value. Which SQLScript functions harden each part against injection?

Only the WHERE condition of your query is dynamic (the rest is fixed). What's the simpler-than-dynamic-SQL tool, and which one predicate won't it accept?

Procedure A does SIGNAL of a condition named my_error (code 10001) but doesn't handle it. Procedure B calls A and declares a handler FOR my_error. Must both declare a CONDITION, and what actually links them?

You write DECLARE EXIT HANDLER FOR SQLEXCEPTION and expect it to also catch your own SIGNAL my_error. Does it? And how many statements may a handler run?

A GLOBAL TEMPORARY TABLE is created once and exists in every session. If session A inserts 100 rows, what does session B see when it selects from that same global temporary table?

You have a 5-element array and set its last element to NULL to 'shorten' it. Does CARDINALITY now return 4? How do you actually reduce an array's length?

An array :a holds Dog, Cat, Mouse (3 elements). What do these return? ``` CARDINALITY(:a) :a[5] ```

names is an ARRAY column in a DB table, holding 2 elements for this row. What happens? ``` SELECT names[5] FROM array_demo; ```

Predict the output. ``` DECLARE demo VARCHAR(20) ARRAY; demo[3] = 'x'; SELECT :demo[10] FROM dummy; ```

You store tags in an ARRAY column and want rows whose array contains a tag starting with 'urgent'. Why won't MEMBER OF do this, and what CAN it check?

You have two equal-length arrays :animals and :colors, and want a table with an Animal column, a Color column, and a row-number column. Which function and clause?

You have a table variable :lt_tasks and want its assignee column as a single array, in ascending order. Which function, and where does the sort go?

A colleague implements bubble sort in SQLScript (ARRAY_AGG, nested FOR loops swapping elements, UNNEST back). What's the book's verdict on doing this in production?

Procedure A updates the sender's balance, then calls procedure B which updates the receiver's balance. Where should the COMMIT go, and what does one ROLLBACK affect?

A logging procedure ends with a COMMIT so its log row is definitely saved. It's called in the middle of a big multi-step transaction. What just went wrong?

A session variable is created with SET 'MYVAR' = 'x' and read with SESSION_CONTEXT('MYVAR'). What does SESSION_CONTEXT return for a name that was never SET, and why must the name in SET be a literal?