How can we call stored procedures inside store procedures?
- 1 Answers
- 914 Views
- I also Faced
- E-Mail Answers
Answer Posted / Rahul Tyagi
Calling a stored procedure within another stored procedure is possible in most database systems. The syntax for doing so varies between different database management systems (DBMS). In Oracle, for example, you can use the DBMS_SQL package to dynamically execute a stored procedure from within another stored procedure. Here's an example:
```sql
DECLARE
procedure_result NUMBER;
procedure_name VARCHAR2(100) := 'PROCEDURE_TO_CALL';
parameter1 VARCHAR2(50);
parameter2 NUMBER;
BEGIN
DBMS_SQL.PARSE(sql_handle => procedure_handle,
sql_statement => 'BEGIN ' || procedure_name || '(p1, p2); END;',
bind_array => NULL,
limit => 500,
warn_flag => DBMS_SQL.WARNINGS_ON);
DBMS_SQL.CALL_PROCEDURE(procedure_handle => procedure_handle,
arg1 => parameter1,
arg2 => parameter2);
DBMS_SQL.CLOSE(procedure_handle);
END;
```
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
