Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    16

    Unanswered: Finding the difference

    This extended tables based proc used to work fine but i just wanted to find how much time this insertion take so i add some timestamp stuff i know i lost somewhere in substraction query please help me..
    create or replace pROCEDURE SP_LOADER
    IS
    O_ID VARCHAR2(10);
    O_STATUS VARCHAR2(02);
    stime timestamp;
    etime timestamp;
    end_time timestamp;
    BEGIN

    stime :=systimestamp;

    INSERT INTO Quote(Id,Name,AccountId,ActualShipDate,ContactId,D ueDate,EndDate,ItemProductId,QuoteNumber,Requested ShipDate,Revision,SalesRep,ServiceLineTotal,Servic eTotal,BilltoContactId,BilltoAddressId,ShipToConta ctId,ShipToAddressId,StartDate,Status,StatusOrder, StatusType,Comments)
    (SELECT Id,Name,AccountId,ActualShipDate,ContactId,DueDate ,EndDate,ItemProductId,QuoteNumber,RequestedShipDa te,Revision,SalesRep,ServiceLineTotal,ServiceTotal ,BilltoContactId,BilltoAddressId,ShipToContactId,S hipToAddressId,StartDate,Status,StatusOrder,Status Type,Comments from Quote_load);


    INSERT INTO QuoteItem (Id,LineNumber,PartNumber,ProductId,Quantity,Quant ityRequested,UnitofMeasure)
    (SELECT Id,LineNumber,PartNumber,ProductId,Quantity,Quanti tyRequested,UnitofMeasure from QuoteItem_load);

    sELECT ID,STATUS INTO O_ID,O_STATUS FROM qUOTE ;

    INSERT INTO wbia_jdbc_eventstore (event_id,xid,object_key, object_name,object_function, event_priority, event_status,event_comment) values
    (seq_wbia_jdbc.nextval,O_ID,'N.pkey','MI_SalesOrde rBG','Create', 1,1,case O_STATUS
    when '1' then 'CREATE'
    when '2' then 'UPDATE'
    when '3' then 'DELETE'
    else 'INVALID' end);

    etime :=systimestamp;

    SELECT SUBSTR(stime,1,30) "Time1",

    SUBSTR(etime,1,30) "Time2",

    SUBSTR((etime-stime),1,30) "Time2 - Time1" into end_time

    FROM dual;



    commit;
    end;
    ================================================== ==============
    30/1 PL/SQL: SQL Statement ignored
    36/2 PL/SQL: ORA-00947: not enough values
    ================================================== =================
    "TALK SENSE TO FOOL. HE CALLS U FOOLISH"

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This statement:
    Code:
    SELECT SUBSTR(stime,1,30) "Time1",
    SUBSTR(etime,1,30) "Time2",
    SUBSTR((etime-stime),1,30) "Time2 - Time1" into end_time
    FROM dual;
    selects 3 values and tries to store them into 1 variable; you need 3 variables, or get rid of the first two values from the select.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by andrewst
    you need 3 variables, or get rid of the first two values from the select.
    Or even better, get rid of that SELECT at all and use simple assignment operator (:=).
    Also take care that types of target variable and assigned expression are the same. Now you are trying to put VARCHAR2 expressions into variable with TIMESTAMP type.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •