Solved

Oracle PL Sql

Posted on 2013-12-19
2
348 Views
Last Modified: 2013-12-19
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
Comment
Question by:Extreme66
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39730336
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
 

Author Closing Comment

by:Extreme66
ID: 39730367
Excellent catch, thanks for the quick response, it helped immensely!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question