• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Oracle PL Sql

Hi im trying to run an insert statement only if it is a current trading day, getting a compile error though PLS-00103

create or replace procedure import_sum_trades as
begin
declare c_is_trading_day char(1);

select c.is_trading_day into c_is_trading_day from calendar c where c.date = trunc(sysdate);


if (c_is_trading_day = '0') then
 
    (insert into sum_trades_info (trade_time, trader, trade_desk, firm_id, sponsor, ttl_trd_cnt, ttl_trd_vol
    , ttl_val_money_saved, ttl_vol_mpp_or_better, firm_type, source, route_destination, gateway_id)
    select trade_time, trader, trade_desk, firm_id, sponsor, ttl_trd_cnt, ttl_trd_vol, ttl_val_money_saved, ttl_vol_mpp_or_better, firm_type, source, route_destination, originating_gateway
    from stats.import_sum_trades_info);
    commit;

end if;
end;
0
Extreme66
Asked:
Extreme66
1 Solution
 
sdstuberCommented:
get rid of declare, move the begin, remove extra parentheses around the insert

CREATE OR REPLACE PROCEDURE import_sum_trades
AS
    c_is_trading_day CHAR(1);
BEGIN
    SELECT c.is_trading_day
      INTO c_is_trading_day
      FROM calendar c
     WHERE c.date = TRUNC(SYSDATE);

    IF (c_is_trading_day = '0')
    THEN
        INSERT INTO sum_trades_info(
                        trade_time,
                        trader,
                        trade_desk,
                        firm_id,
                        sponsor,
                        ttl_trd_cnt,
                        ttl_trd_vol,
                        ttl_val_money_saved,
                        ttl_vol_mpp_or_better,
                        firm_type,
                        source,
                        route_destination,
                        gateway_id
                    )
            SELECT trade_time,
                   trader,
                   trade_desk,
                   firm_id,
                   sponsor,
                   ttl_trd_cnt,
                   ttl_trd_vol,
                   ttl_val_money_saved,
                   ttl_vol_mpp_or_better,
                   firm_type,
                   source,
                   route_destination,
                   originating_gateway
              FROM stats.import_sum_trades_info;

        COMMIT;
    END IF;
END;
0
 
Extreme66Author Commented:
Excellent catch, thanks for the quick response, it helped immensely!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now