Loading

How to Insert Date Timestamp into Oracle database table

게시 일자: Mar 2, 2024
과업

GOAL

Many times we need to insert Date values into the Oracle database, here below different examples to do that.
단계

  1. Insert Date


    Input parameter:
    {
    "in_DOB": "1985-04-13"
    }

    SQL query text:
    #["INSERT INTO oracledev.DAT (DOB) values (:NEWDATE)"]

    Input parameters in DB connector:
    {
    "NEWDATE": payload.in_DOB as Date {format: "yyyy-MM-dd"}
    }
      
    <flow name="00257833aaFlow" doc:id="0ddb47f3-23a6-45af-8904-d1e99c2f2f7b" >
        <http:listener doc:name="Listener" doc:id="c7c3cf80-4777-46f2-ada9-cdfa547f9dd0" config-ref="HTTP_Listener_config" path="/test"/>
        <set-variable value="#[payload]" doc:name="Set Variable" doc:id="307f3131-b4db-4b5e-a519-3ffb84588ae8" variableName="originalPayload"/>
        <db:insert doc:name="DB Connector" doc:id="d9d77eba-ac4c-4db6-8d98-29e583b66022" config-ref="Database_Config">
            <db:sql><![CDATA[#["INSERT INTO oracledev.DAT (DOB) values (:NEWDATE)"]]]></db:sql>
            <db:input-parameters><![CDATA[#[{
"NEWDATE": vars.originalPayload.DOB as Date {format: "yyyy-MM-dd"}
}]]]></db:input-parameters>
        </db:insert>
        <logger level="INFO" doc:name="Logger" doc:id="610bbc80-570a-490f-92b3-bb88fb596c91" message="succes DB insert"/>
    </flow>

  2. Insert Date and Time


    Input parameter:
    {
      "startDateTime":  "2018-09-05T16:02:32"
    }

    SQL query text:
    #["INSERT INTO oracledev.DAT (HOLIDAYS, PROCESS_START) values (:NEWDATE, :NEWDATE)"]

    Input parameters in DB connector:
    Then in the input parameter of the DB connector, you can use the DateTime object:
    {
    "NEWDATE": payload.startDateTime as DateTime
    }
      
    <flow name="00263976_aaFlow" doc:id="6058e0bf-4093-47eb-90a7-4abade396406" >
		<http:listener doc:name="Listener" doc:id="91a3a186-18e5-4de0-a439-b89613f82b7f" config-ref="HTTP_Listener_config" path="/insert"/>
		<logger level="INFO" doc:name="Logger" doc:id="6167bcc5-02b3-458e-a869-2b00e3d143c1" message="#[payload.startDateTime]"/>
		<db:insert doc:name="Insert" doc:id="8189d40d-4963-4ca8-9d44-12ebd7cf31c9" config-ref="Database_Config">
			<db:sql><![CDATA[#["INSERT INTO oracledev.DAT (HOLIDAYS, PROCESS_START) values (:NEWDATE, :NEWDATE)"]]]></db:sql>
			<db:input-parameters><![CDATA[#[{
	"NEWDATE": payload.startDateTime as DateTime
}]]]></db:input-parameters>
		</db:insert>
		<logger level="INFO" doc:name="Logger" doc:id="37ff9719-2ae1-4e10-834e-bdf5bf3ae861" message="#[payload]"/>
	</flow>

  3. Insert Date/DateTime as null from Stored Procedure


    SQL to call Stored Procedure:
    call INS_NULL_DATE(:HOLIDAY, :PROCESS_STARTED)

    Input parameters in DB connector:
    {
        "PROCESS_STARTED": now() as DateTime,
        "HOLIDAY": null
    } 
    <flow name="TEST_TO_INSERT_NULL_DATE" doc:id="b4f52089-ece0-4158-80cf-d654a1577907" >
		<scheduler doc:name="Scheduler" doc:id="1b1e8e5d-5758-4841-bae4-97a46e30ff0c" >
			<scheduling-strategy >
				<fixed-frequency frequency="5" timeUnit="MINUTES"/>
			</scheduling-strategy>
		</scheduler>
		<db:stored-procedure doc:name="INSERT SP" doc:id="245db2f2-5a59-4260-b526-2c97b954351e" config-ref="Database_Config">
			<db:sql><![CDATA[call INS_NULL_DATE(:HOLIDAY, :PROCESS_STARTED)]]></db:sql>
			<db:input-parameters><![CDATA[#[{
	"PROCESS_STARTED": now() as DateTime,
	"HOLIDAY": null
}]]]></db:input-parameters>
		</db:stored-procedure>
		<logger level="INFO" doc:name="Logger" doc:id="29897c6f-3920-41c9-8c00-fdb422bd7ea4" message="#[payload]"/>
	</flow>

     
Knowledge 기사 번호

001115879

 
로드 중
Salesforce Help | Article