Calling a Stored PL/SQL or Java Subprogram To call a stored subprogram from your host program, you can use either an anonymous PL/SQL block, or the CALL embedded SQL statement.Anonymous PL/SQL Block In the following example, you call a standalone procedure named raise_salary:EXEC SQL EXECUTE BEGIN raise_salary(:emp_id, :increase); END; END-EXEC; Notice that stored subprograms can take parameters. In this example, the actual parameters emp_id and increase are C host variables.In the next example, the procedure raise_salary is stored in a package named emp_actions, so you must use dot notation to fully qualify the procedure call:EXEC SQL EXECUTE BEGIN emp_actions.raise_salary(:emp_id, :increase); END; END-EXEC; An actual IN parameter can be a literal, scalar host variable, host array, PL/SQL constant or variable, PL/SQL table, PL/SQL user-defined record, procedure call, or expression. However, an actual OUT parameter cannot be a literal, procedure call, or expression.You must use precompiler option SQLCHECK=SEMANTICS with an embedded PL/SQL block.In the following example, three of the formal parameters are PL/SQL tables, and the corresponding actual parameters are host arrays. The program calls the stored procedure get_employees repeatedly, displaying each batch of employee data, until no more data is found. This program is available on-line in the demo directory, in the file sample9.pc. A SQL script to create the CALLDEMO stored package is available in the file calldemo.sql.
To call a stored subprogram from your host program, you can use either an anonymous PL/SQL block, or the CALL embedded SQL statement.Anonymous PL/SQL Block
In the following example, you call a standalone procedure named raise_salary:EXEC SQL EXECUTE
BEGIN
raise_salary(:emp_id, :increase);
END;
END-EXEC;
Notice that stored subprograms can take parameters. In this example, the actual parameters emp_id and increase are C host variables.In the next example, the procedure raise_salary is stored in a package named emp_actions, so you must use dot notation to fully qualify the procedure call:EXEC SQL EXECUTE
BEGIN
emp_actions.raise_salary(:emp_id, :increase);
END;
END-EXEC;
An actual IN parameter can be a literal, scalar host variable, host array, PL/SQL constant or variable, PL/SQL table, PL/SQL user-defined record, procedure call, or expression. However, an actual OUT parameter cannot be a literal, procedure call, or expression.You must use precompiler option SQLCHECK=SEMANTICS with an embedded PL/SQL block.In the following example, three of the formal parameters are PL/SQL tables, and the corresponding actual parameters are host arrays. The program calls the stored procedure get_employees repeatedly, displaying each batch of employee data, until no more data is found. This program is available on-line in the demo directory, in the file sample9.pc. A SQL script to create the CALLDEMO stored package is available in the file calldemo.sql.