CODEWITHSUNDEEP
sqloracle-database
paul
paul

Reputation: 3

Stored procedure stopped working

I have a problem concerning a stored procedure. The stored procedure i programmed permits a third party provider to insert data into my database. It always worked fin up to 5 days ago when no data gets anymore inserted. I am really a complete rooky with respect to SQL so i do not know where to start investigating what could be the error. Any help, insights and advices are highly appreciated. Thanks in advance. Attached is the code. 

create or replace PROCEDURE               "GET_DROP_COPY"
(
  P_BEGINSTRING IN VARCHAR2
, P_BODYLENGTH IN VARCHAR2
, P_MSGTYPE IN VARCHAR2
, P_MSGSEQNUM IN VARCHAR2
, P_SENDERCOMPID IN VARCHAR2
, P_SENDINGTIME IN VARCHAR2
, P_TARGETCOMPID IN VARCHAR2
, P_TARGETSUBID IN VARCHAR2
, P_AVGPX IN VARCHAR2
, P_CLORDID IN VARCHAR2
, P_CUMQTY IN VARCHAR2
, P_CURRENCY IN CHAR
, P_EXECID IN VARCHAR2
, P_SECURITYIDSOURCE IN NUMBER
, P_LASTPX IN NUMBER
, P_LASTQTY IN NUMBER
, P_ORDERID IN VARCHAR2
, P_ORDERQTY IN NUMBER
, P_ORDSTATUS IN VARCHAR2
, P_PRICE IN NUMBER
, P_ORDTYPE IN VARCHAR2
, P_SECURITYID IN VARCHAR2
, P_SIDE IN VARCHAR2
, P_FIELD55 IN VARCHAR2
, P_TIMEINFORCE IN VARCHAR2
, P_TRANSACTTIME IN VARCHAR2
, P_SETTLTYPE IN VARCHAR2
, P_TRADEDATE IN VARCHAR2
, P_EXDESTINATION IN VARCHAR2
, P_EXECTYPE IN VARCHAR2
, P_LEAVESQTY IN VARCHAR2
, P_SECURITYTYPE IN VARCHAR2
, P_SECONDARYORDERID IN VARCHAR2
, P_SECURITYEXCHANGE IN VARCHAR2
, P_ROUNDLOTBOOK IN NUMBER
, P_COPYMSGINDICATOR IN VARCHAR2
, P_REPEATING_GROUP IN NUMBER
, P_PARTY_ID IN VARCHAR2
, P_PARTYIDSOURCE IN VARCHAR2
, P_PARTYROLE IN NUMBER
, P_PARTYID2 IN VARCHAR2
, P_PARTYIDSOURCE2 IN VARCHAR2
, P_PARTYROLE2 IN NUMBER
, P_PARTYID3 IN VARCHAR2
, P_PARTYIDSOURCE3 IN VARCHAR2
, P_PARTYROLE3 IN NUMBER
, P_CHECKSUM IN NUMBER
) AS
l_count     NUMBER;
l_modpor    NUMBER(1,0);
l_logid     NUMBER;
err_num     NUMBER;
err_msg     VARCHAR2(100);

BEGIN

SELECT NVL(MAX(ID),0) INTO l_logid
FROM LOG_PROCEDURE;

IF l_logid = 0 THEN
  l_logid := 1;
ELSE
  l_logid := l_logid + 1;
END IF;

INSERT INTO LOG_PROCEDURE (ID, ETL, INICIO, TRAZA, TABLA)
VALUES(l_logid, 'GET_DROP_COPY', SYSDATE, 'INSERT RECORD. ',
'DROP_COPY');
COMMIT;

  INSERT INTO DROP_COPY (BEGINSTRING, BODYLENGTH, MSGTYPE, MSGSEQNUM,
SENDERCOMPID, SENDINGTIME, TARGETCOMPID, TARGETSUBID, AVGPX, CLORDID,
CUMQTY, CURRENCY, EXECID, SECURITYIDSOURCE, LASTPX, LASTQTY, ORDERID,
ORDERQTY, ORDSTATUS, PRICE, ORDTYPE, SECURITYID, SIDE, FIELD55,
TIMEINFORCE, TRANSACTTIME, SETTLTYPE, TRADEDATE, EXDESTINATION,
EXECTYPE, LEAVESQTY, SECURITYTYPE, SECONDARYORDERID, SECURITYEXCHANGE,
ROUNDLOTBOOK, COPYMSGINDICATOR, REPEATING_GROUP, PARTY_ID,
PARTYIDSOURCE, PARTYROLE, PARTYID2, PARTYIDSOURCE2, PARTYROLE2,
PARTYID3, PARTYIDSOURCE3, PARTYROLE3, CHECKSUM)
    VALUES(P_BEGINSTRING, P_BODYLENGTH, P_MSGTYPE, P_MSGSEQNUM,
P_SENDERCOMPID, P_SENDINGTIME, P_TARGETCOMPID, P_TARGETSUBID, P_AVGPX,
P_CLORDID, P_CUMQTY, P_CURRENCY, P_EXECID, P_SECURITYIDSOURCE, P_LASTPX,
P_LASTQTY, P_ORDERID, P_ORDERQTY, P_ORDSTATUS, P_PRICE, P_ORDTYPE,
P_SECURITYID, P_SIDE, P_FIELD55, P_TIMEINFORCE, P_TRANSACTTIME,
P_SETTLTYPE, P_TRADEDATE, P_EXDESTINATION, P_EXECTYPE, P_LEAVESQTY,
P_SECURITYTYPE, P_SECONDARYORDERID, P_SECURITYEXCHANGE, P_ROUNDLOTBOOK,
P_COPYMSGINDICATOR, P_REPEATING_GROUP, P_PARTY_ID, P_PARTYIDSOURCE,
P_PARTYROLE, P_PARTYID2, P_PARTYIDSOURCE2, P_PARTYROLE2, P_PARTYID3,
P_PARTYIDSOURCE3, P_PARTYROLE3, P_CHECKSUM);

  l_count := TO_CHAR(SQL%ROWCOUNT);

UPDATE LOG_PROCEDURE
SET FIN = SYSDATE,
        ESTADO = 'OK',
        TRAZA = TRAZA || l_count || ' REGISTRO(s) INSERTADO(s).'
WHERE ID = l_logid;


COMMIT;

IF (P_PARTY_ID IN ('009', '036', '016', '003') OR P_PARTYID2 IN ('009', '036', '016', '003') OR P_PARTYID3 IN ('009', '036', '016', '003')) AND (P_ORDERID NOT LIKE'%SER%') THEN

  IF ( P_EXECTYPE NOT IN ('0', '4') AND ((P_PARTYROLE = 7 AND P_PARTY_ID = '058') OR  (P_PARTYROLE2 = 7 AND P_PARTYID2 = '058') OR (P_PARTYROLE3 = 7 AND P_PARTYID3 = '058'))) THEN
    l_modpor := 1;
  ELSE
    l_modpor := 0;
  END IF;

    INSERT INTO EQUITY_PORTFOLIO_DCMOV
       ( BEGINSTRING, BODYLENGTH, MSGTYPE, MSGSEQNUM, SENDERCOMPID, SENDINGTIME, TARGETCOMPID, TARGETSUBID, AVGPX, CLORDID,
         CUMQTY, CURRENCY, EXECID, SECURITYIDSOURCE, LASTPX, LASTQTY, ORDERID,  ORDERQTY, ORDSTATUS, PRICE, ORDTYPE, SECURITYID, SIDE, FIELD55,
         TIMEINFORCE, TRANSACTTIME, SETTLTYPE, TRADEDATE, EXDESTINATION, EXECTYPE, LEAVESQTY, SECURITYTYPE, SECONDARYORDERID, SECURITYEXCHANGE,
         ROUNDLOTBOOK, COPYMSGINDICATOR, REPEATING_GROUP, PARTY_ID, PARTYIDSOURCE, PARTYROLE, PARTYID2, PARTYIDSOURCE2, PARTYROLE2,
         PARTYID3, PARTYIDSOURCE3, PARTYROLE3, CHECKSUM, ORIGEN, MODPOR
       )
     VALUES( P_BEGINSTRING, P_BODYLENGTH, P_MSGTYPE, P_MSGSEQNUM, P_SENDERCOMPID, TO_TIMESTAMP(P_SENDINGTIME, 'YYYYMMDD-HH24:MI:SS.FF3'), P_TARGETCOMPID, P_TARGETSUBID, P_AVGPX,
             P_CLORDID, P_CUMQTY, TRIM(P_CURRENCY), P_EXECID, P_SECURITYIDSOURCE, P_LASTPX, P_LASTQTY, P_ORDERID, P_ORDERQTY, P_ORDSTATUS, P_PRICE, P_ORDTYPE,
             P_SECURITYID, P_SIDE, P_FIELD55, P_TIMEINFORCE, TO_TIMESTAMP(P_TRANSACTTIME, 'YYYYMMDD-HH24:MI:SS.FF3'), P_SETTLTYPE, P_TRADEDATE, P_EXDESTINATION, P_EXECTYPE, P_LEAVESQTY,
             P_SECURITYTYPE, P_SECONDARYORDERID, TRIM(P_SECURITYEXCHANGE), P_ROUNDLOTBOOK, TRIM(P_COPYMSGINDICATOR), P_REPEATING_GROUP, P_PARTY_ID, P_PARTYIDSOURCE,
             P_PARTYROLE, P_PARTYID2, P_PARTYIDSOURCE2, P_PARTYROLE2, P_PARTYID3, P_PARTYIDSOURCE3, P_PARTYROLE3, P_CHECKSUM, 'LOCAL', l_modpor
           );  

     SELECT COUNT(*) INTO l_count
       FROM EQUITY_PORTFOLIO
         WHERE SECURITYID = P_SECURITYID;

     IF l_count >= 1 AND l_modpor = 1 THEN
       ACTUALIZA_EQUITY_PORTFOLIO (P_ORDERID, P_SECURITYID, P_PRICE, P_ORDERQTY, P_EXECTYPE, P_ORDSTATUS, P_SIDE, TO_TIMESTAMP(P_SENDINGTIME, 'YYYYMMDD-HH24:MI:SS.FF3'), TO_TIMESTAMP(P_TRANSACTTIME, 'YYYYMMDD-HH24:MI:SS.FF3'), P_TRADEDATE, P_LASTPX, P_LASTQTY, l_modpor);
     ELSE
       IF l_count = 0 THEN

         INSERT INTO EQUITY_PORTFOLIO(SECURITYID, SECURITY_ID, ID_ISIN, POSITION, POSITION_INI, AAAAMMDD, CURRENCY)
           SELECT P_SECURITYID, EQDES.BLOOMBERG_TICKER, EQDES.ID_ISIN, 0, 0, P_TRADEDATE, CURRENCY
              FROM EQUITY_DESCRIPTOR EQDES
               WHERE ID_EXCH_SYMBOL = P_SECURITYID;
         COMMIT;

        ACTUALIZA_EQUITY_PORTFOLIO (P_ORDERID, P_SECURITYID, P_PRICE, P_ORDERQTY, P_EXECTYPE, P_ORDSTATUS, P_SIDE, TO_TIMESTAMP(P_SENDINGTIME, 'YYYYMMDD-HH24:MI:SS.FF3'), TO_TIMESTAMP(P_TRANSACTTIME, 'YYYYMMDD-HH24:MI:SS.FF3'), P_TRADEDATE, P_LASTPX, P_LASTQTY, l_modpor);

       END IF;
    END IF;
  END IF;    



EXCEPTION
WHEN OTHERS THEN

err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);

INSERT INTO DROP_COPY_ERROR (BEGINSTRING, BODYLENGTH, MSGTYPE, MSGSEQNUM,
SENDERCOMPID, SENDINGTIME, TARGETCOMPID, TARGETSUBID, AVGPX, CLORDID,
CUMQTY, CURRENCY, EXECID, SECURITYIDSOURCE, LASTPX, LASTQTY, ORDERID,
ORDERQTY, ORDSTATUS, PRICE, ORDTYPE, SECURITYID, SIDE, FIELD55,
TIMEINFORCE, TRANSACTTIME, SETTLTYPE, TRADEDATE, EXDESTINATION,
EXECTYPE, LEAVESQTY, SECURITYTYPE, SECONDARYORDERID, SECURITYEXCHANGE,
ROUNDLOTBOOK, COPYMSGINDICATOR, REPEATING_GROUP, PARTY_ID,
PARTYIDSOURCE, PARTYROLE, PARTYID2, PARTYIDSOURCE2, PARTYROLE2,
PARTYID3, PARTYIDSOURCE3, PARTYROLE3, CHECKSUM, LOGID)
    VALUES(P_BEGINSTRING, P_BODYLENGTH, P_MSGTYPE, P_MSGSEQNUM,
P_SENDERCOMPID, P_SENDINGTIME, P_TARGETCOMPID, P_TARGETSUBID, P_AVGPX,
P_CLORDID, P_CUMQTY, P_CURRENCY, P_EXECID, P_SECURITYIDSOURCE, P_LASTPX,
P_LASTQTY, P_ORDERID, P_ORDERQTY, P_ORDSTATUS, P_PRICE, P_ORDTYPE,
P_SECURITYID, P_SIDE, P_FIELD55, P_TIMEINFORCE, P_TRANSACTTIME,
P_SETTLTYPE, P_TRADEDATE, P_EXDESTINATION, P_EXECTYPE, P_LEAVESQTY,
P_SECURITYTYPE, P_SECONDARYORDERID, P_SECURITYEXCHANGE, P_ROUNDLOTBOOK,
P_COPYMSGINDICATOR, P_REPEATING_GROUP, P_PARTY_ID, P_PARTYIDSOURCE,
P_PARTYROLE, P_PARTYID2, P_PARTYIDSOURCE2, P_PARTYROLE2, P_PARTYID3,
P_PARTYIDSOURCE3, P_PARTYROLE3, P_CHECKSUM, l_logid);

UPDATE LOG_PROCEDURE
SET FIN = SYSDATE,
            ESTADO = 'ERROR',
            CODERR = err_num,
            MSGERR = err_msg
WHERE ID = l_logid;

COMMIT;

END GET_DROP_COPY;

This is the output when i run the procedure:

Connecting to the database CLBCB_TRADER. ORA-01400: cannot insert NULL into ("CLBCBTRADER"."DROP_COPY_ERROR"."SENDINGTIME") ORA-06512: at "CLBCBTRADER.GET_DROP_COPY", line 155 ORA-01400: cannot insert NULL into ("CLBCBTRADER"."DROP_COPY"."SENDINGTIME") ORA-06512: at line 98 Process exited. Disconnecting from the database CLBCB_TRADER.

Additionally, I should mention that the "SENDINGTIME" variable always looks like this so i dont see a problem: "20141110-13:30:03.394"

Upvotes: 0

Views: 153

Answers (2)

twoleggedhorse
twoleggedhorse

Reputation: 5048

The error is telling you that you are trying to insert a NULL value into the SENDINGTIME field of GET_DROP_COPY. The field does not allow NULL values.

Either:

  • Insert an actual value
  • Allow nulls in your column.

You should probably look to the first option as there is probably a reason for the column not allowing nulls.

Upvotes: 1

LittleSweetSeas
LittleSweetSeas

Reputation: 7074

Looking at the error message, it seems that parameter P_SENDINGTIME is passed as a NULL value, and this is not a value allowed in your schema definition.

It seems that your third party provider changed something in the last days. If that parameter was just a timestamp, you could set a default value to it running your script to alter the procedure by setting a default value:

create or replace PROCEDURE "GET_DROP_COPY" (
   //...first part definition of script here

   , P_SENDINGTIME IN VARCHAR2 := ''

   //...rest definition of script here
)
   //... body of script here
END GET_DROP_COPY;

Please note that:

  1. you should first of all ask the provider whether they changed anything
  2. it may be a risky approach if you aren't sure of what that value should be
  3. you may use sysdate to get a proper timestamp value - eg:
   ...
    , P_SENDINGTIME IN VARCHAR2 := TO_CHAR(SYSDATE, 'MM.DD.YYYY HH24:MI:SS')
   ...

Upvotes: 1

Related Questions