1 2 Previous Next 16 Replies Latest reply on Mar 20, 2017 8:35 PM by Murray Sobol

    using dynamic SQL in a Procedure

    Murray Sobol

      I am trying to re-write a procedure to use dynamic SQL; I was trying to follow the example in this article: Ask Tom: On Popularity and Natural Selection

       

      Here is my Procedure:

      http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

      CREATE OR REPLACE procedure test
         (as_bin_number              IN  varchar2,
          ad_bill_of_lading_date     IN  date,
          as_bill_of_lading_number   IN  varchar2,
          as_business_type           IN  varchar2,
          as_commodity_brand         IN  varchar2,
          as_commodity_group         IN  varchar2,
          as_commodity_id            IN  varchar2,
          as_commodity_product       IN  varchar2,
          an_contract_id             IN  number,
          as_currency_code           IN  varchar2,
          ac_date_type               IN  char,
          as_delivery_sheet          IN  varchar2,
          as_destination_id          IN  varchar2,
          as_field_barn_id           IN  varchar2,
          ad_from_date               IN  date,
          ac_include_unapplied       IN  char,
          as_last_product_hauled     IN  varchar2,
          as_location_id             IN  varchar2,
          as_lot_no                  IN  varchar2,
          as_name_address_filter     IN  varchar2,
          as_name_address_id         IN  varchar2,
          as_origin_id               IN  varchar2,
          as_origin_destination_city IN  varchar2,
          as_payment_indicator_code  IN  varchar2,
          as_pc_number               IN  varchar2,
          as_pricing_type_code       IN  varchar2,
          as_producer_id             IN  varchar2,
          as_product_no              IN  varchar2,
          as_relationship            IN  varchar2,
          as_seal_number             IN  varchar2,
          as_settlement_no           IN  varchar2,
          as_ship_from_id            IN  varchar2,
          as_ship_mode_code          IN  varchar2,
          as_ship_to_id              IN  varchar2,
          as_smartsoft_user_name     IN  varchar2,
          as_spot_ticket_contract    IN  char,
          an_ticket_number           IN  varchar2,
          an_ticket_no               IN  varchar2,
          as_ticket_owner_id         IN  varchar2,
          as_ticket_reference        IN  varchar2,
          as_ticket_status           IN  varchar2,
          as_ticket_type_code        IN  char,
          ad_to_date                 IN  date,
          as_tow_id                  IN  varchar2,
          as_vehicle_id              IN  varchar2,
          as_miscfield1              IN  varchar2,
          as_miscfield2              IN  varchar2,
          as_miscfield3              IN  varchar2,
          as_miscfield4              IN  varchar2,
          as_miscfield5              IN  varchar2,
          as_miscfield6              IN  varchar2,
          as_miscfield7              IN  varchar2,
          as_miscfield8              IN  varchar2,
          as_miscfield9              IN  varchar2,
          as_miscfield10             IN  varchar2,
          as_miscfield11             IN  varchar2,
          as_miscfield12             IN  varchar2,
          as_miscfield13             IN  varchar2,
          as_miscfield14             IN  varchar2,
          as_miscfield15             IN  varchar2,
          result_set                 IN
                                     OUT PagingUtility.PagedResultType,
          order_by                   IN  long

         )
      AS
         lc_gs_option_epr_flag       char(1);
         lv_name_and_address_id      varchar2(10);
         ld_from_date                date;
         ld_to_date                  date;
         lv_get_naa                  s1_name_and_address.name_and_address_id%TYPE;
         lv_full_name                s1_name_and_address.full_name%TYPE;
         lv_parent_record_id         s1_name_and_address.parent_record_id%TYPE;
         lv_pass                     number;
         naa_result_set              SYS_REFCURSOR;
                                
         l_cursor                    SYS_REFCURSOR;
         l_query                     long;

         CURSOR l_template IS SELECT * FROM gtt_ticket_settle_query_detail;
         l_rec  l_template%rowtype;

      BEGIN

         naa_result_set              := f_get_name_address_list (lv_name_and_address_id);

         WHILE TRUE LOOP

           FETCH naa_result_set INTO lv_get_naa,
                                     lv_full_name,
                                     lv_parent_record_id,
                                     lv_pass;

           EXIT WHEN naa_result_set%NOTFOUND;

           INSERT INTO gtt_get_name_address_list
               VALUES (lv_get_naa,lv_full_name,lv_parent_record_id,lv_pass);

         END LOOP;

         SELECT s1_grainsmart_option.epr_flag
           INTO lc_gs_option_epr_flag
           FROM s1_grainsmart_option
          WHERE s1_grainsmart_option.go_key = 1;

         IF ld_to_date IS NOT NULL THEN
             ld_to_date := ld_to_date + 1;
         END IF;

         l_query := '
         INSERT INTO gtt_ticket_settle_query_detail
         SELECT advance_amount,
                advance_date,
                advance_epr_price,
                advance_nbr,
                advance_quantity,
                advance_serial_no,
                applied_quantity,
                averaged_flag,
                bin_number,
                bol_date,
                bol_number,
                business_type_description,
                buy_sell_decimals,
                checkoff,
                combined_serial_nbr,
                combined_date,
                commodity_brand,
                commodity_group,
                commodity_id,
                commodity_name,
                commodity_weight,
                contract_id,
                contract_no,
                contract_release_no,
                contract_type,
                currency_code,
                currency_description,
                delivery_sheet,
                destination_id,
                destination_full_name,
                destination_short_name,
                direct_ship_flag,
                driver_name,
                elevator_receipt_nbr,
                epr_id,
                epr_id_report,
                epr_name,
                epr_name_report,
                epr_code,
                epr_description,
                farm_id,
                field_barn_id,
                from_uom,
                grade,
                grade_average,
                grade_average_report,
                grade_description,
                grade_description_report,
                grade_discount,
                grade_govern_status,
                grade_value_1,
                grade_value_2,
                grade_value_3,
                grade_value_4,
                grade_value_5,
                grade_value_6,
                grade_value_7,
                grade_value_8,
                grade_value_9,
                grade_value_10,
                grade_value_11,
                grade_value_12,
                grade_value_13,
                grade_value_14,
                grade_value_15,
                grade_value_16,
                grade_value_17,
                grade_value_18,
                grade_value_19,
                grade_value_20,
                grade_value_21,
                grade_value_22,
                grade_value_23,
                grade_value_24,
                grade_value_25,
                grade_value_26,
                grade_value_27,
                grade_value_28,
                grade_value_29,
                grade_value_30,
                grade_value_31,
                grade_value_32,
                grade_value_33,
                grade_value_34,
                grade_value_35,
                grade_value_36,
                grade_value_37,
                grade_value_38,
                grade_value_39,
                grade_value_40,
                lc_gs_option_epr_flag,
                intended_location,
                intended_location_full_name,
                intended_location_short_name,
                io_flag,
                last_product_hauled,
                loadout_date,
                loadout_time,
                lot_number,
                misc_field_01,
                misc_field_02,
                misc_field_03,
                misc_field_04,
                misc_field_05,
                misc_field_06,
                misc_field_07,
                misc_field_08,
                misc_field_09,
                misc_field_10,
                misc_field_11,
                misc_field_12,
                misc_field_13,
                misc_field_14,
                misc_field_15,
                ngr_flag,
                notes,
                origin_id,
                origin_full_name,
                origin_short_name,
                origin_destination_city,
                pay_grade_commodity_id,
                pay_grade_commodity_name,
                pay_indicator_code,
                pay_indicator_description,
                pc_basis_of_initial_price,
                pc_number,
                pricing_type_code,
                pricing_type_description,
                printed_status_code,
                printed_status_description,
                product_description,
                product_no,
                program_type_code,
                program_type_description,
                program_year_code,
                program_year_description,
                print_ticket_flag,
                quantity_entry_nbr,
                receiving_decimals,
                relationship,
                scale_ticket_id,
                seal_number,
                settlement_amount,
                settlement_date,
                settlement_epr_price,
                settlement_no,
                settlement_quantity,
                settlement_reference,
                settlement_serial_nbr,
                settlement_status,
                settlement_uom,
                ship_date,
                ship_from_id,
                ship_from_full_name,
                ship_from_short_name,
                ship_mode,
                ship_mode_code,
                ship_to_id,
                ship_to_full_name,
                ship_to_short_name,
                shrink_quantity,
                split_percentage,
                split_quantity,
                split_weight,
                spot_ticket_desc,
                ticket_application_status,
                ticket_date,
                ticket_time,
                ticket_location_id,
                ticket_location_full_name,
                ticket_location_short_name,
                ticket_reference_code,
                ticket_type,
                to_uom,
                tow_id,
                unapplied_quantity,
                unload_date,
                unload_time,
                unloaded_intransit,
                vehicle_disposition_no,
                vehicle_id,
                vend_customer_no,
                vend_customer_full_name,
                vend_customer_short_name,
                vendor_id,
                vendor_full_name,
                vendor_short_name,
                weight_govern_status,
                ph_unsettled,
                not_advance_quantity
           FROM fw_ticket_settle_applied_v
          WHERE vendor_id IN
                (SELECT name_and_address_id
                   FROM gtt_get_name_address_list
                )
        ';

        IF (as_commodity_id IS NOT NULL)
          THEN
            l_query := l_query ||
            ' AND commodity_id = :as_commodity_id ';
          ELSE
            l_query := l_query ||
            ' and (1=1 OR
                   :as_commodity_id IS NULL
         ) ';
        END IF;

        dbms_output.put_line
        ( l_query );

        OPEN l_cursor
         FOR l_query
        USING as_bin_number,
              ad_bill_of_lading_date,
              as_bill_of_lading_number,
              as_business_type,
              as_commodity_brand,
              as_commodity_group,
              as_commodity_id,
              as_commodity_product,
              an_contract_id,
              as_currency_code,
              ac_date_type,
              as_delivery_sheet,
              as_destination_id,
              as_field_barn_id,
              ad_from_date,
              ac_include_unapplied,
              as_last_product_hauled,
              as_location_id,
              as_lot_no,
              as_name_address_filter,
              as_name_address_id,
              as_origin_id,
              as_origin_destination_city,
              as_payment_indicator_code,
              as_pc_number,
              as_pricing_type_code,
              as_producer_id,
              as_product_no,
              as_relationship,
              as_seal_number,
              as_settlement_no,
              as_ship_from_id,
              as_ship_mode_code,
              as_ship_to_id,
              as_smartsoft_user_name,
              as_spot_ticket_contract,
              an_ticket_number,
              an_ticket_no,
              as_ticket_owner_id,
              as_ticket_reference,
              as_ticket_status,
              as_ticket_type_code,
              ad_to_date,
              as_tow_id,
              as_vehicle_id,
              as_miscfield1,
              as_miscfield2,
              as_miscfield3,
              as_miscfield4,
              as_miscfield5,
              as_miscfield6,
              as_miscfield7,
              as_miscfield8,
              as_miscfield9,
              as_miscfield10,
              as_miscfield11,
              as_miscfield12,
              as_miscfield13,
              as_miscfield14,
              as_miscfield15;

        LOOP
          FETCH l_cursor
           INTO l_rec;
           EXIT WHEN l_cursor%notfound;

          dbms_output.put_line
          ( l_rec.as_bin_number || ' , ' ||
            l_rec.ad_bill_of_lading_date || ' , ' ||
            l_rec.as_bill_of_lading_number
      );

        END LOOP;
        CLOSE l_cursor;

        for x in
        (select plan_table_output
           from table
           (dbms_xplan.display_cursor)
        )
        loop
           dbms_output.put_line
           ( x.PLAN_TABLE_OUTPUT );
        end loop;

      END;
      /

       

      When I try to compile it using SQL Developer I get this error:

      TEST PROCEDURE 2 324 3 PL/SQL: Statement ignored ERROR 0

      TEST PROCEDURE 1 326 9 PLS-00201: identifier 'ADVANCE_AMOUNT' must be declared ERROR 201

       

      I don't understand the error; I thought that I had declared the cursor here:

         CURSOR l_template IS SELECT * FROM gtt_ticket_settle_query_detail;

         l_rec  l_template%rowtype;

       

      Any assistance would be appreciated.

       

      Thanks

      Murray Sobol

        • 1. Re: using dynamic SQL in a Procedure
          James Su

          what are the columns in: fw_ticket_settle_applied_v ?

          • 2. Re: using dynamic SQL in a Procedure
            jaramill

            Please format your code then "Use advanced editor" to syntax highlight it.

            As I did here for you:

             

            CREATE OR REPLACE PROCEDURE test
            (
               -- PARAMETERS
               as_bin_number              IN  VARCHAR2,
               ad_bill_of_lading_date     IN  DATE,
               as_bill_of_lading_number   IN  VARCHAR2,
               as_business_type           IN  VARCHAR2,
               as_commodity_brand         IN  VARCHAR2,
               as_commodity_group         IN  VARCHAR2,
               as_commodity_id            IN  VARCHAR2,
               as_commodity_product       IN  VARCHAR2,
               an_contract_id             IN  NUMBER,
               as_currency_code           IN  VARCHAR2,
               ac_date_type               IN  CHAR,
               as_delivery_sheet          IN  VARCHAR2,
               as_destination_id          IN  VARCHAR2,
               as_field_barn_id           IN  VARCHAR2,
               ad_from_date               IN  DATE,
               ac_include_unapplied       IN  CHAR,
               as_last_product_hauled     IN  VARCHAR2,
               as_location_id             IN  VARCHAR2,
               as_lot_no                  IN  VARCHAR2,
               as_name_address_filter     IN  VARCHAR2,
               as_name_address_id         IN  VARCHAR2,
               as_origin_id               IN  VARCHAR2,
               as_origin_destination_city IN  VARCHAR2,
               as_payment_indicator_code  IN  VARCHAR2,
               as_pc_number               IN  VARCHAR2,
               as_pricing_type_code       IN  VARCHAR2,
               as_producer_id             IN  VARCHAR2,
               as_product_no              IN  VARCHAR2,
               as_relationship            IN  VARCHAR2,
               as_seal_number             IN  VARCHAR2,
               as_settlement_no           IN  VARCHAR2,
               as_ship_from_id            IN  VARCHAR2,
               as_ship_mode_code          IN  VARCHAR2,
               as_ship_to_id              IN  VARCHAR2,
               as_smartsoft_user_name     IN  VARCHAR2,
               as_spot_ticket_contract    IN  CHAR,
               an_ticket_number           IN  VARCHAR2,
               an_ticket_no               IN  VARCHAR2,
               as_ticket_owner_id         IN  VARCHAR2,
               as_ticket_reference        IN  VARCHAR2,
               as_ticket_status           IN  VARCHAR2,
               as_ticket_type_code        IN  CHAR,
               ad_to_date                 IN  DATE,
               as_tow_id                  IN  VARCHAR2,
               as_vehicle_id              IN  VARCHAR2,
               as_miscfield1              IN  VARCHAR2,
               as_miscfield2              IN  VARCHAR2,
               as_miscfield3              IN  VARCHAR2,
               as_miscfield4              IN  VARCHAR2,
               as_miscfield5              IN  VARCHAR2,
               as_miscfield6              IN  VARCHAR2,
               as_miscfield7              IN  VARCHAR2,
               as_miscfield8              IN  VARCHAR2,
               as_miscfield9              IN  VARCHAR2,
               as_miscfield10             IN  VARCHAR2,
               as_miscfield11             IN  VARCHAR2,
               as_miscfield12             IN  VARCHAR2,
               as_miscfield13             IN  VARCHAR2,
               as_miscfield14             IN  VARCHAR2,
               as_miscfield15             IN  VARCHAR2,
               result_set                 IN
                                          OUT PagingUtility.PagedResultType,
               order_by                   IN  LONG
            ) AS
            
               -- DECLARATION SECTION
               -- VARIABLES
               
               lc_gs_option_epr_flag       CHAR(1);
               lv_name_and_address_id      VARCHAR2(10);
               ld_from_date                DATE;
               ld_to_date                  DATE;
               lv_get_naa                  s1_name_and_address.name_and_address_id%TYPE;
               lv_full_name                s1_name_and_address.full_name%TYPE;
               lv_parent_record_id         s1_name_and_address.parent_record_id%TYPE;
               lv_pass                     NUMBER;
               naa_result_set              SYS_REFCURSOR;
                                       
               l_cursor                    SYS_REFCURSOR;
               l_query                     LONG;
            
               CURSOR l_template IS
                  SELECT *
                    FROM gtt_ticket_settle_query_detail;
            
               l_rec l_template%rowtype;
            
            BEGIN
            
               naa_result_set := f_get_name_address_list(lv_name_and_address_id);
            
               WHILE TRUE LOOP
            
                 FETCH naa_result_set
                  INTO lv_get_naa,
                       lv_full_name,
                       lv_parent_record_id,
                       lv_pass;
            
                 EXIT WHEN naa_result_set%NOTFOUND;
            
                 INSERT INTO gtt_get_name_address_list
                      VALUES (lv_get_naa,
                              lv_full_name,
                              lv_parent_record_id,
                              lv_pass);
            
               END LOOP;
            
               SELECT s1_grainsmart_option.epr_flag
                 INTO lc_gs_option_epr_flag
                 FROM s1_grainsmart_option
                WHERE s1_grainsmart_option.go_key = 1;
            
               IF(ld_to_date IS NOT NULL) THEN
                   ld_to_date := ld_to_date + 1;
               END IF;
            
               l_query := '
               INSERT INTO gtt_ticket_settle_query_detail
               SELECT advance_amount,
                      advance_date,
                      advance_epr_price,
                      advance_nbr,
                      advance_quantity,
                      advance_serial_no,
                      applied_quantity,
                      averaged_flag,
                      bin_number,
                      bol_date,
                      bol_number,
                      business_type_description,
                      buy_sell_decimals,
                      checkoff,
                      combined_serial_nbr,
                      combined_date,
                      commodity_brand,
                      commodity_group,
                      commodity_id,
                      commodity_name,
                      commodity_weight,
                      contract_id,
                      contract_no,
                      contract_release_no,
                      contract_type,
                      currency_code,
                      currency_description,
                      delivery_sheet,
                      destination_id,
                      destination_full_name,
                      destination_short_name,
                      direct_ship_flag,
                      driver_name,
                      elevator_receipt_nbr,
                      epr_id,
                      epr_id_report,
                      epr_name,
                      epr_name_report,
                      epr_code,
                      epr_description,
                      farm_id,
                      field_barn_id,
                      from_uom,
                      grade,
                      grade_average,
                      grade_average_report,
                      grade_description,
                      grade_description_report,
                      grade_discount,
                      grade_govern_status,
                      grade_value_1,
                      grade_value_2,
                      grade_value_3,
                      grade_value_4,
                      grade_value_5,
                      grade_value_6,
                      grade_value_7,
                      grade_value_8,
                      grade_value_9,
                      grade_value_10,
                      grade_value_11,
                      grade_value_12,
                      grade_value_13,
                      grade_value_14,
                      grade_value_15,
                      grade_value_16,
                      grade_value_17,
                      grade_value_18,
                      grade_value_19,
                      grade_value_20,
                      grade_value_21,
                      grade_value_22,
                      grade_value_23,
                      grade_value_24,
                      grade_value_25,
                      grade_value_26,
                      grade_value_27,
                      grade_value_28,
                      grade_value_29,
                      grade_value_30,
                      grade_value_31,
                      grade_value_32,
                      grade_value_33,
                      grade_value_34,
                      grade_value_35,
                      grade_value_36,
                      grade_value_37,
                      grade_value_38,
                      grade_value_39,
                      grade_value_40,
                      lc_gs_option_epr_flag,
                      intended_location,
                      intended_location_full_name,
                      intended_location_short_name,
                      io_flag,
                      last_product_hauled,
                      loadout_date,
                      loadout_time,
                      lot_number,
                      misc_field_01,
                      misc_field_02,
                      misc_field_03,
                      misc_field_04,
                      misc_field_05,
                      misc_field_06,
                      misc_field_07,
                      misc_field_08,
                      misc_field_09,
                      misc_field_10,
                      misc_field_11,
                      misc_field_12,
                      misc_field_13,
                      misc_field_14,
                      misc_field_15,
                      ngr_flag,
                      notes,
                      origin_id,
                      origin_full_name,
                      origin_short_name,
                      origin_destination_city,
                      pay_grade_commodity_id,
                      pay_grade_commodity_name,
                      pay_indicator_code,
                      pay_indicator_description,
                      pc_basis_of_initial_price,
                      pc_number,
                      pricing_type_code,
                      pricing_type_description,
                      printed_status_code,
                      printed_status_description,
                      product_description,
                      product_no,
                      program_type_code,
                      program_type_description,
                      program_year_code,
                      program_year_description,
                      print_ticket_flag,
                      quantity_entry_nbr,
                      receiving_decimals,
                      relationship,
                      scale_ticket_id,
                      seal_number,
                      settlement_amount,
                      settlement_date,
                      settlement_epr_price,
                      settlement_no,
                      settlement_quantity,
                      settlement_reference,
                      settlement_serial_nbr,
                      settlement_status,
                      settlement_uom,
                      ship_date,
                      ship_from_id,
                      ship_from_full_name,
                      ship_from_short_name,
                      ship_mode,
                      ship_mode_code,
                      ship_to_id,
                      ship_to_full_name,
                      ship_to_short_name,
                      shrink_quantity,
                      split_percentage,
                      split_quantity,
                      split_weight,
                      spot_ticket_desc,
                      ticket_application_status,
                      ticket_date,
                      ticket_time,
                      ticket_location_id,
                      ticket_location_full_name,
                      ticket_location_short_name,
                      ticket_reference_code,
                      ticket_type,
                      to_uom,
                      tow_id,
                      unapplied_quantity,
                      unload_date,
                      unload_time,
                      unloaded_intransit,
                      vehicle_disposition_no,
                      vehicle_id,
                      vend_customer_no,
                      vend_customer_full_name,
                      vend_customer_short_name,
                      vendor_id,
                      vendor_full_name,
                      vendor_short_name,
                      weight_govern_status,
                      ph_unsettled,
                      not_advance_quantity
                 FROM fw_ticket_settle_applied_v
                WHERE vendor_id IN (SELECT name_and_address_id
                                      FROM gtt_get_name_address_list)';
            
               IF(as_commodity_id IS NOT NULL) THEN
                   l_query := l_query || ' AND commodity_id = :as_commodity_id ';
               ELSE
                   l_query := l_query || ' and (1=1 OR :as_commodity_id IS NULL) ';
               END IF;
            
               DBMS_OUTPUT.PUT_LINE(l_query);
            
               OPEN l_cursor
                FOR l_query
               USING as_bin_number,
                     ad_bill_of_lading_date,
                     as_bill_of_lading_number,
                     as_business_type,
                     as_commodity_brand,
                     as_commodity_group,
                     as_commodity_id,
                     as_commodity_product,
                     an_contract_id,
                     as_currency_code,
                     ac_date_type,
                     as_delivery_sheet,
                     as_destination_id,
                     as_field_barn_id,
                     ad_from_date,
                     ac_include_unapplied,
                     as_last_product_hauled,
                     as_location_id,
                     as_lot_no,
                     as_name_address_filter,
                     as_name_address_id,
                     as_origin_id,
                     as_origin_destination_city,
                     as_payment_indicator_code,
                     as_pc_number,
                     as_pricing_type_code,
                     as_producer_id,
                     as_product_no,
                     as_relationship,
                     as_seal_number,
                     as_settlement_no,
                     as_ship_from_id,
                     as_ship_mode_code,
                     as_ship_to_id,
                     as_smartsoft_user_name,
                     as_spot_ticket_contract,
                     an_ticket_number,
                     an_ticket_no,
                     as_ticket_owner_id,
                     as_ticket_reference,
                     as_ticket_status,
                     as_ticket_type_code,
                     ad_to_date,
                     as_tow_id,
                     as_vehicle_id,
                     as_miscfield1,
                     as_miscfield2,
                     as_miscfield3,
                     as_miscfield4,
                     as_miscfield5,
                     as_miscfield6,
                     as_miscfield7,
                     as_miscfield8,
                     as_miscfield9,
                     as_miscfield10,
                     as_miscfield11,
                     as_miscfield12,
                     as_miscfield13,
                     as_miscfield14,
                     as_miscfield15;
               LOOP
            
                 FETCH l_cursor
                  INTO l_rec;
            
                  EXIT WHEN l_cursor%NOTFOUND;
            
                 DBMS_OUTPUT.PUT_LINE(l_rec.as_bin_number || ' , ' || l_rec.ad_bill_of_lading_date
                                      || ' , ' || l_rec.as_bill_of_lading_number);
            
               END LOOP;
               
               CLOSE l_cursor;
            
               FOR x IN (SELECT plan_table_output
                           FROM TABLE(dbms_xplan.display_cursor)) LOOP
            
                  DBMS_OUTPUT.PUT_LINE(x.PLAN_TABLE_OUTPUT);
            
               END LOOP;
            
            END test;
            
            • 3. Re: using dynamic SQL in a Procedure
              John Stegeman

              Why did you open a new thread when you've been discussing this very issue already? Pros & Cons of using Dynamic SQL in a Procedure

              • 4. Re: using dynamic SQL in a Procedure
                Murray Sobol

                Apologies John... I thought that I was in a different thread.

                 

                Murray

                • 5. Re: using dynamic SQL in a Procedure
                  top.gun

                  So which lines does the errors refer to?

                  • 6. Re: using dynamic SQL in a Procedure
                    Andrew Sayer

                    Thanks to the formatted version provided by jaramill, it was easier to spot problems. I recommend you dbms_ouptut.put_line your dynamic sql before you Attempt to execute it. Then try and execute it manually using that MASSIVE list of bind variables, you may have problems because it looks to me like you've only allowed for one bind variable.

                     

                    Additionally, what on earth are you doing with longs? They are not nice to work with, plsql has a huge varchar2 size limit, use it.

                    • 7. Re: using dynamic SQL in a Procedure
                      James Su

                      long type in pl/sql is different than sql, it's actually a 32k varchar2.

                      • 8. Re: using dynamic SQL in a Procedure
                        rp0428

                        The point was that the LONG datatype is problematic to work with.

                        • 9. Re: using dynamic SQL in a Procedure
                          Andrew Sayer

                          James Su wrote:

                           

                          long type in pl/sql is different than sql, it's actually a 32k varchar2.

                          Could you point me to the doc? all I can find in the 12.1 docs is in Database PL/SQL Language Reference

                          PL/SQL Data Types

                          LONG and LONG RAW Variables

                          Note:

                          Oracle supports the LONG and LONG RAW data types only for backward compatibility with existing applications. For new applications:

                          • Instead of LONG, use VARCHAR2(32760), BLOB, CLOB or NCLOB.
                          • Instead of LONG RAW, use BLOB.

                          You can insert any LONG value into a LONG column. You can insert any LONG RAW value into a LONG RAW column. You cannot retrieve a value longer than 32,760 bytes from a LONG or LONG RAW column into a LONG or LONG RAW variable.

                          You can insert any CHAR or VARCHAR2 value into a LONG column. You cannot retrieve a value longer than 32,767 bytes from a LONG column into a CHAR or VARCHAR2 variable.

                          You can insert any RAW value into a LONGRAW column. You cannot retrieve a value longer than 32,767 bytes from a LONGRAW column into a RAW variable.

                          Even if this was the case, the explicit advice is clearly:

                          Instead of LONG, use VARCHAR2(32760), BLOB, CLOB or NCLOB.

                          • 10. Re: using dynamic SQL in a Procedure
                            jaramill

                            Andrew Sayer wrote:

                             

                            James Su wrote:

                             

                            long type in pl/sql is different than sql, it's actually a 32k varchar2.

                            Could you point me to the doc? all I can find in the 12.1 docs is in Database PL/SQL Language Reference

                            PL/SQL Data Types

                            LONG and LONG RAW Variables

                            Note:

                            Oracle supports the LONG and LONG RAW data types only for backward compatibility with existing applications. For new applications:

                            • Instead of LONG, use VARCHAR2(32760), BLOB, CLOB or NCLOB.
                            • Instead of LONG RAW, use BLOB.

                            You can insert any LONG value into a LONG column. You can insert any LONG RAW value into a LONG RAW column. You cannot retrieve a value longer than 32,760 bytes from a LONG or LONG RAW column into a LONG or LONG RAW variable.

                            You can insert any CHAR or VARCHAR2 value into a LONG column. You cannot retrieve a value longer than 32,767 bytes from a LONG column into a CHAR or VARCHAR2 variable.

                            You can insert any RAW value into a LONGRAW column. You cannot retrieve a value longer than 32,767 bytes from a LONGRAW column into a RAW variable.

                            Even if this was the case, the explicit advice is clearly:

                            Instead of LONG, use VARCHAR2(32760), BLOB, CLOB or NCLOB.

                            Is that a typo in the docs?  "You cannot retrieve a value longer than 32760 form LONG to LONG" but from LONG to CHAR/VARCHAR it's 32767.  Why the 7 byte difference?

                            • 11. Re: using dynamic SQL in a Procedure
                              jeneesh

                              If my memory is correct, the maximum size of a Long Raw variable is 32760 bytes.

                              But, Maximum size of a Long column is  2GB.

                               

                              So I guess, no typo in the document.

                              • 12. Re: using dynamic SQL in a Procedure
                                James Su

                                In the standard package you can see:

                                subtype LONG is VARCHAR2(32760);

                                • 14. Re: using dynamic SQL in a Procedure
                                  jaramill

                                  jeneesh wrote:

                                   

                                  If my memory is correct, the maximum size of a Long Raw variable is 32760 bytes.

                                  But, Maximum size of a Long column is 2GB.

                                   

                                  So I guess, no typo in the document.

                                  Your memory is correct.

                                   

                                  Found it .... PL/SQL Data Types

                                   

                                  LONGFoot 3

                                  32,760 bytes

                                  2 gigabytes (GB) - 1

                                  1 2 Previous Next