Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Inserts using existing functions
Bulk Inserts using existing functions [message #243905] Sun, 10 June 2007 23:52 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
CREATE OR REPLACE function LTR_PRINT_INSERT
(
/*************************************************
** Declare Parameters                           **
*************************************************/

   pLOCK_TOKEN            IN COMMON_DATATYPE_PKG.COMMON_SMALLINT%TYPE            := NULL,
   pSY_ID               IN COMMON_LP_PRINT_HST.SY_ID%TYPE               := NULL,
   pXR_DEST_ID          IN COMMON_LP_PRINT_HST.XR_DEST_ID%TYPE          := NULL,
   pLT_SEQ_NO           IN COMMON_LP_PRINT_HST.LT_SEQ_NO%TYPE           := NULL,
   pLP_SEQ_NO           IN COMMON_LP_PRINT_HST.LP_SEQ_NO%TYPE           := NULL,
   pLP_REQUEST_DT       IN COMMON_LP_PRINT_HST.LP_REQUEST_DT%TYPE       := NULL,
   pLP_REQUEST_USUS     IN COMMON_LP_PRINT_HST.LP_REQUEST_USUS%TYPE     := NULL,
   pLP_SUBMITTED_DT     IN COMMON_LP_PRINT_HST.LP_SUBMITTED_DT%TYPE     := NULL,
   pLP_PRINTED_DT       IN COMMON_LP_PRINT_HST.LP_PRINTED_DT%TYPE       := NULL,
   pLP_MAILED_DT        IN COMMON_LP_PRINT_HST.LP_MAILED_DT%TYPE        := NULL,
   pLP_RECEIVED_DT      IN COMMON_LP_PRINT_HST.LP_RECEIVED_DT%TYPE      := NULL,
   pLP_LAST_UPDATE_DT   IN COMMON_LP_PRINT_HST.LP_LAST_UPDATE_DT%TYPE   := NULL,
   pLP_LAST_UPDATE_USUS IN COMMON_LP_PRINT_HST.LP_LAST_UPDATE_USUS%TYPE := NULL,
   pLP_DESC             IN COMMON_LP_PRINT_HST.LP_DESC%TYPE             := NULL,
   pLP_ORIG_LT_ID     IN COMMON_LP_PRINT_HST.LP_ORIG_LT_ID%TYPE     := NULL,
   pLP_LOCK_TOKEN       IN COMMON_LP_PRINT_HST.LP_LOCK_TOKEN%TYPE       := NULL,
   pXR_SOURCE_ID        IN COMMON_LP_PRINT_HST.XR_SOURCE_ID%TYPE        := NULL
)
return number
AS

/*************************************************
** Declare Local Variables                      **
*************************************************/

 lnRetCd          COMMON_DATATYPE_PKG.COMMON_INT%TYPE;
 lLP_LOCK_TOKEN COMMON_LP_PRINT_HST.LP_LOCK_TOKEN%TYPE  := pLP_LOCK_TOKEN;

/*************************************************
** Begin Procedure Code                        **
*************************************************/
BEGIN

 lnRetCd := 0;

 /***********************************************
 ** Insert Row into THE BASE TABLE             **
 ***********************************************/

 IF lnRetCd = 0 THEN
    IF pLOCK_TOKEN IS NOT NULL THEN
       lLP_LOCK_TOKEN := pLOCK_TOKEN;
    END IF;

    INSERT INTO
       COMMON_LP_PRINT_HST
    (
       SY_ID,
       XR_DEST_ID,
       LT_SEQ_NO,
       LP_SEQ_NO,
       LP_REQUEST_DT,
       LP_REQUEST_USUS,
       LP_SUBMITTED_DT,
       LP_PRINTED_DT,
       LP_MAILED_DT,
       LP_RECEIVED_DT,
       LP_LAST_UPDATE_DT,
       LP_LAST_UPDATE_USUS,
       LP_DESC,
       LP_ORIG_LT_ID,
       LP_LOCK_TOKEN,
       XR_SOURCE_ID
    )
    VALUES
    (
       nvl(pSY_ID              , ' '),
       nvl(pXR_DEST_ID         , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLT_SEQ_NO          , 0),
       nvl(pLP_SEQ_NO          , 0),
       nvl(pLP_REQUEST_DT      , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLP_REQUEST_USUS    , ' '),
       nvl(pLP_SUBMITTED_DT    , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLP_PRINTED_DT      , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLP_MAILED_DT       , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLP_RECEIVED_DT     , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLP_LAST_UPDATE_DT  , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(pLP_LAST_UPDATE_USUS, ' '),
       nvl(pLP_DESC            , ' '),
       nvl(pLP_ORIG_LT_ID    , to_timestamp('01/01/1753', 'mm/dd/yyyy')),
       nvl(lLP_LOCK_TOKEN      , 1),
       nvl(pXR_SOURCE_ID       , to_timestamp('01/01/1753', 'mm/dd/yyyy'))
    );
 END IF;


 RETURN lnRetCd;

END LTR_PRINT_INSERT;
/



I have to use this function to insert to this table (THIS IS A MUST). Is there any way I can do a Bulk insert ? selecting from other tables.

Is this function compatible in a way bulk insert will work?

Since there are variables for each column, wondering how that would work ?
Re: Bulk Inserts using existing functions [message #243906 is a reply to message #243905] Sun, 10 June 2007 23:54 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
I am new to Oracle. My understanding is even through we do Bulk inserts, oracle internally inserts them record by record (which is why check constraints are recommended in oracle compared to sql server and sybase) ? Is this true ?

Re: Bulk Inserts using existing functions [message #243940 is a reply to message #243905] Mon, 11 June 2007 01:29 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any way I can do a Bulk insert ?

You insert one value, so there is no bulk.
Quote:
selecting from other tables.

Yes, INSERT ... SELECT is mad for that but this not PL/SQL bulk operation, this is SQL.

Regards
Michel
Previous Topic: UPDATE USING CURSOR FLAT FILE PROBLEM!
Next Topic: Incorrect Summation
Goto Forum:
  


Current Time: Thu Dec 08 00:09:14 CST 2016

Total time taken to generate the page: 0.07245 seconds