Hi All,
I'm very much familiar and comfortable in using Native Dynamic SQL. I would like to know when to use DBMS_SQL alone, as I can do everything with NDS itself instead of using this.
In the documents, I could see that "We must use the DBMS_SQL package to execute a dynamic SQL statement that has unknown number of input or output variables, also known as Method 4".
Could you please give some examples for this scenario specifically, how we will get unknown number of input and output variables? Do we get the input parameters from a parameter table? Kindly advise and help.
I have come across the below scenario of bind array using DBMS_SQL, this also can be very achievable through the normal Bulk Binding.
DECLARE
stmt VARCHAR2(200);
departid_array DBMS_SQL.NUMBER_TABLE;
deptname_array DBMS_SQL.VARCHAR2_TABLE;
mgrid_array DBMS_SQL.NUMBER_TABLE;
locid_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
BEGIN
departid_array(1):= 280;
departid_array(2):= 290;
departid_array(3):= 300;
stmt := 'INSERT INTO departments VALUES(
:departid_array, :deptname_array, :mgrid_array, :locid_array)';
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_ARRAY(c, ':departid_array', departid_array);
DBMS_SQL.BIND_ARRAY(c, ':deptname_array', deptname_array);
DBMS_SQL.BIND_ARRAY(c, ':mgrid_array', mgrid_array);
DBMS_SQL.BIND_ARRAY(c, ':locid_array', locid_array);
dummy := DBMS_SQL.EXECUTE(c);
DBMS_SQL.CLOSE_CURSOR(c);
END;
Thank you.