Embedded SQL Example in AS400 using EXECUTE IMMEDIATE is given below- EVAL SQLSTMT1= 'SELECT COUNT(.) ARR(7) FROM ARR(1)'; C/EXEC SQL; C+ EXECUTE IMMEDIATE:SQLSTMT1; C/END-EXEC www.go4as400.com - A programming guide to learn AS400. Figure 1: Use EXECUTE IMMEDIATE in an RPG program. The only parameter EXECUTE IMMEDIATE expects is a character variable containing an SQL statement (limited to those shown in the list above). In the program snippet in Figure 1, the SQL statement is built dynamically and then processed by EXECUTE IMMEDIATE.
Back to Basics: Execute Immediate and SELECT statement
Posted by decipherinfosys on September 11, 2008
Execute Immediate statement prepares and executes the dynamic statement. For single row queries we can get the data using ‘INTO’ clause and store it in the variable. We can also use bind variable using ‘USING’ clause. Placeholder for the bind variables are defined as text, preceded by colon ( : ). In this blog post, we will show you how can we use Execute Immediate statement to retrieve the data. Following is the small PL/SQL stored procedure that demonstrates the use of Execute Immediate. Connect to the database via SQL*Plus using proper credentials and create the following procedure.
CREATE OR REPLACE PROCEDURE test_proc(p_object_Name IN VARCHAR2)
AS
v_object_Type user_objects.object_type%TYPE;
BEGIN
BEGIN
EXECUTE IMMEDIATE
‘SELECT object_Type ‘
|| ‘ FROM user_objects ‘
|| ‘ WHERE object_name = : obj_name ‘
INTO v_object_Type
USING p_object_Name;
dbms_output.put_line(‘Object Type is ‘ || v_object_Type);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20950, ‘object does not exist: ‘ || p_object_name);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20951, ‘Error retrieving oject name: ‘ || SQLERRM);
END;
END test_proc;
/
Once the procedure is successfully created, let us execute it to see the results.
SQL> exec test_proc(‘TEST_PROC’);
Object Type is PROCEDURE
PL/SQL procedure successfully completed.
Here we have used bind variable to improve the performance. It is used in ‘USING’ clause. During execution : obj_name placeholder value will be replaced with the value from p_object_name.
Couple of things we have to remember when dealing with dynamic sqls are
• ; is not placed at the end of the SELECT statement, but it is placed at the end of the Execute Immediate statement.
• Similar thing is also with INTO clause. INTO is outside of quoted SELECT statement.
As400 Sql Create Table
As demonstrated above, Execute Immediate is the way to deal with dynamic sqls. In next blog, we will demonstrate how can we use multiple rows using BULK COLLECT with dynamic sql.