Solved

Oracle PL Sql

Posted on 2013-12-19
2
350 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 74

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing parameters to sql script oracle 4 61
Repeat query 13 61
oracle DR - data guard failover. 18 48
subtr returning incorrect value 8 32
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

830 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