4 Replies Latest reply on Aug 16, 2019 6:04 PM by L. Fernigrini

    DBMS_SQL usage alone

    Dev_SQL

      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.

        • 1. Re: DBMS_SQL usage alone
          L. Fernigrini

          Here is one example by Steven Feuerstein, that appears first on the list if you google "DBMS_SQL METHOD 4"

           

          Dynamic SQL Method 4 Example: Get and display data in table

           

          • 2. Re: DBMS_SQL usage alone
            Dev_SQL

            Thank you!!

             

            Steven has posted a nice example, but it is too long to go through and get it fully.

             

            Earlier, we had a restriction to use only 32 KB string with NDS (Execute Immediate), that is relaxed now, so we can use CLOB as a parameter.

             

            In MYHO, we can dynamically frame unknown number of input and output variables as a String and still execute with NDS.

             

            So, I would like to understand and very keen to know, when DBMS_SQL really helps, can someone put in a nutshell and give some example? I'm sorry if I'm asking more and troubling, thank you.

            • 3. Re: DBMS_SQL usage alone
              Dev_SQL

              I do understand fully that when I apply "INTO" or "USING" clause, I need to have the known output and input variables during the development phase itself.

               

              DBMS_SQL helps to overcome this, I fully agree, anything else I need to remember to go with this package, please advise, thank you.

              • 4. Re: DBMS_SQL usage alone
                L. Fernigrini

                That's the main usage of DBMS_SQL, you can find that explained in the documentation:

                 

                https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68177

                Overview

                Oracle lets you write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

                Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic SQL itself has certain limitations:

                • There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)
                • There are some tasks that can only be performed using DBMS_SQL. For tasks that require DBMS_SQL, see Oracle Database PL/SQL Language Reference.

                 

                 

                 

                Please if you have a couple of minutes change your display name to something meaningful rather than a number, making simpler for everyone to followup a conversation:

                Update Your Community Display Name and Avatar!