EXECUTE IMMEDIATE COMMAND

Question

I am getting an error ORA-00900: invalid SQL statement ORA-06512: at line 125 00900. 00000 - "invalid SQL statement" when I execute this SQL:

    set serveroutput on;
DECLARE smartsoftVersionItem   varchar2(20);
        maxScriptLevel         number;
        sessionNo              number;
        SQLCommand             varchar2(8000);

BEGIN

   SELECT MAX(software_version)
     INTO smartsoftVersionItem
     FROM s1_grainsmart_dbupdate;

   SELECT MAX(script)
     INTO maxScriptLevel
     FROM s1_grainsmart_dbupdate;

   SELECT MAX(session_no)
     INTO sessionNo
     FROM ssc_dbupdate;

   INSERT INTO ssc_dbupdate
       VALUES (seq_ssc_dbupdate.NEXTVAL, sessionNo, sysdate, smartsoftVersionItem, maxScriptLevel, Null, '17.00.01', 'ssc_ivc_transaction_detail_v', 'V');

   SQLCommand := 
      'CREATE VIEW ssc_ivc_transaction_detail_v
       AS
       SELECT ivc_transaction_detail.ivc_transaction_nbr,
              ivc_transaction_detail.plc_id,
              ivc_transaction_detail.commodity_id,
              ivc_transaction_detail.opening_date,
              ivc_transaction_detail.group_nbr,
              ivc_transaction_detail.sort_order,
              ivc_transaction_detail.transaction_source,
              ivc_transaction_detail.transaction_time,
              ivc_transaction_detail.quantity_lb,
              ROUND(ivc_transaction_detail.quantity_lb * quantity_uom.conv_factor / 10000, quantity_uom.to_nbr_of_decimals)
                                               AS quantity,
              ivc_transaction_detail.dollar_value,
              ivc_transaction_detail.add_by,
              ivc_transaction_detail.orderlog_plc_id,
              ivc_transaction_detail.orderlog_nbr,
              ivc_transaction_detail.quantity_entry_nbr,
              ivc_transaction_detail.contract_nbr,
              ivc_transaction_detail.vendor_customer_id,
              ivc_transaction_detail.transaction_note,
              ivc_transaction_detail.ivc_lot_nbr,
              ivc_transaction_detail.vehicle_disposition_nbr,
              ivc_transaction_detail.receipt_nbr,
              ivc_transaction_detail.adj_type_nbr,
              ivc_transaction_detail.inv_adj_nbr,
              ivc_transaction_detail.adj_quantity,
              ivc_transaction_detail.production_nbr,
              ivc_transaction_detail.bin_transaction_nbr,
              ivc_transaction_detail.prd_to_prd_processing_nbr,
              ivc_transaction_detail.oe_plc_id,
              ivc_transaction_detail.oe_order_nbr,
              ivc_transaction_detail.oe_line_item_nbr,
              ivc_transaction_detail.po_plc_id,
              ivc_transaction_detail.po_order_nbr,
              ivc_transaction_detail.po_line_item_nbr,
              ivc_transaction_detail.mps_contract_nbr,
              ivc_transaction_detail.production_oh_dollar_value,
              ivc_transaction_detail.transfer_in_product_cost,
              ivc_transaction_detail.ae_plc_id,
              ivc_transaction_detail.ae_accrual_nbr,
              ivc_transaction_detail.transfer_plc_id,
              ivc_transaction_detail.transfer_order_nbr,
              ivc_transaction_detail.transfer_sort_order,
              ivc_transaction_detail.exchange_rate,
              ivc_transaction_detail.from_currency_code,
      ';

       IF maxScriptLevel >= 1700000117 THEN        -- WO-1702
              SQLCommand := SQLCommand ||
              'ivc_transaction_detail.theoretical_variance_value,
               ivc_transaction_detail.dollar_value_basis,
               ivc_transaction_detail.dollar_value_futures,
               ivc_transaction_detail.dollar_value_discount,
               ivc_transaction_detail.dollar_value_other,
               ivc_transaction_detail.adj_futures_by,
               ivc_transaction_detail.iv_option_month,
               ivc_transaction_detail.after_adj_iv_option_month,
               ivc_transaction_detail.iv_roll_option_month_nbr,
               ivc_transaction_detail.market_futures_subs_flag,
               ivc_transaction_detail.zero_futures_subs_flag,
               ivc_transaction_detail.market_basis_subs_flag,
               ivc_transaction_detail.zero_basis_subs_flag,
              ';
       END IF;

       IF maxScriptLevel > 1700030087 THEN        -- WO-1840
              SQLCommand := SQLCommand ||
              'ivc_transaction_detail.dollar_value_custom1,
               ivc_transaction_detail.dollar_value_custom2,
              ';
       END IF;

       IF maxScriptLevel > 1800000091 THEN        -- WO-2404
              SQLCommand := SQLCommand ||
              'ivc_transaction_detail.transfer_receiving_nbr,
              ';
       END IF;

       IF maxScriptLevel > 1800020121 THEN           -- WO-3066
              SQLCommand := SQLCommand ||
              'ROUND(ivc_transaction_detail.cost_summary_quantity_lb * quantity_uom.conv_factor / 10000, quantity_uom.to_nbr_of_decimals)
                                               AS cost_summary_quantity,
              ivc_transaction_detail.cost_summary_dollar_value
              ';
       END IF;

       SQLCommand := SQLCommand ||
          ' FROM ivc_transaction_detail
               LEFT OUTER JOIN
                    (SELECT ssc_plc_commodity_uom_v.plc_id,
                            ssc_plc_commodity_uom_v.commodity_id,
                            ssc_plc_commodity_uom_v.conv_factor,
                            ssc_plc_commodity_uom_v.to_nbr_of_decimals
                       FROM ssc_plc_commodity_uom_v
                    )                          quantity_uom
                 ON quantity_uom.plc_id = ivc_transaction_detail.plc_id
                AND quantity_uom.commodity_id = ivc_transaction_detail.commodity_id
          ';

   DBMS_OUTPUT.PUT_LINE ('SQL Command = ' || SQLCommand );
   EXECUTE IMMEDIATE 'SQLCommand';

END;

The SQL is meant to be dynamic; i.e.: if I have reached a predetermined script level I want to include additional columns otherwise not.

Yet, If I take the output SQL from my SQLCommand variable and run it creates the view. I'm missing something obvious - I just don't see it now.

Any assistance would be appreciated. Murray

Remove the quotes around SQLCommand: EXECUTE IMMEDIATE SQLCommand;. Otherwise, you are trying to literally execute the SQL string SQLCommand :) — sstan
– sstan, Commented Jun 1, 2016 at 20:12
Predicting your next bug. If maxScriptLevel <= 1800020121 your generated script with contain a rogue comma before the from clause, so it will fail, — APC
– APC, Commented Jun 2, 2016 at 6:19

sstan · Accepted Answer · 2016-06-01 20:15:09Z

3

Change

EXECUTE IMMEDIATE 'SQLCommand';

to

EXECUTE IMMEDIATE SQLCommand; -- no single quotes

Collectives™ on Stack Overflow

EXECUTE IMMEDIATE COMMAND

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related