SQLLdr control-files support

Open seychelles111 opened this issue 2 years ago • 1 comments
I am having issues with getting each table and each field
this is my sql, and I am having no idea how to scrap all of the tables. I could only get some of the tables to get scrapped... I am not a developer from it... I am just trying to help someone else, who is also not a developer...
I am just trying to get this into excel...
a json or a CSV would do just fine... using regex, whatever
I am hopeless I am no idea how to solve this
this is Oracle SQLLdr

input_sql = '\nLOAD DATA\nappend\n\nINTO TABLE PO_DISTRIBUTIONS_INTERFACE\nfields terminated by \',\' optionally enclosed by \'"\' trailing nullcols\n(INTERFACE_DISTRIBUTION_ID \texpression "po_distributions_interface_s.nextval"\n,CREATION_DATE                  expression "current_timestamp(1)"\n,LAST_UPDATE_DATE               expression "current_timestamp(1)"\n,CREATED_BY                   \tconstant \'#CREATEDBY#\'\n,LAST_UPDATED_BY               \tconstant \'#LASTUPDATEDBY#\'\n,LAST_UPDATE_LOGIN             \tconstant \'#LASTUPDATELOGIN#\'\n,LOAD_REQUEST_ID        \tconstant \'#LOADREQUESTID#\'\n,OBJECT_VERSION_NUMBER    \tconstant 1\n--,CREATED_BY                     constant 1\n--,LAST_UPDATED_BY                constant 1\n--,LAST_UPDATE_LOGIN              constant 1\n--,LOAD_REQUEST_ID                constant 1\n,INTERFACE_DISTRIBUTION_KEY\n,INTERFACE_LINE_LOCATION_KEY\n,DISTRIBUTION_NUM\n,DELIVER_TO_LOCATION                                                                                                                                                                          \n,DELIVER_TO_PERSON_FULL_NAME    char(2000)            \n,DESTINATION_SUBINVENTORY\n,AMOUNT_ORDERED\t\t\t"fun_load_interface_utils_pkg.replace_decimal_char(:AMOUNT_ORDERED)"\n,SHIPPING_UOM_QUANTITY\t\t"fun_load_interface_utils_pkg.replace_decimal_char(:SHIPPING_UOM_QUANTITY)"\n,CHARGE_ACCOUNT_SEGMENT1                                                                                                                                                                                                                                                                                                                                                        \n,CHARGE_ACCOUNT_SEGMENT2                                                                                                                                                                                                                                                                                                                                                         \n,ATTRIBUTE_TIMESTAMP8\t\t"to_timestamp(:ATTRIBUTE_TIMESTAMP8, \'YYYY/MM/DD HH24:MI:SS:FF\')"\n,ATTRIBUTE_TIMESTAMP9\t\t"to_timestamp(:ATTRIBUTE_TIMESTAMP9, \'YYYY/MM/DD HH24:MI:SS:FF\')"\n,ATTRIBUTE_TIMESTAMP10\t\t"to_timestamp(:ATTRIBUTE_TIMESTAMP10, \'YYYY/MM/DD HH24:MI:SS:FF\')"\n,DELIVER_TO_PERSON_EMAIL_ADDR\n,BUDGET_DATE\t\t\t"to_date(:BUDGET_DATE, \'YYYY/MM/DD\')"\n,PJC_CONTRACT_NUMBER\n,PJC_FUNDING_SOURCE             char(360)\n,GLOBAL_ATTRIBUTE1              char(150)\n)\n-- +=========================================================================+\n-- | $Header: fusionapps/scm/doo/bin/DooOrderChargeTiersInt.ctl /st_fusionapps_pt-v2mib/1 2021/03/02 19:28:40 nveluthe Exp $ |\n-- +=========================================================================+\n-- | Copyright (c) 2014 Oracle Corporation Redwood City, California, USA     |\n-- | All rights reserved.                                                    |\n-- |=========================================================================+\n-- |\n-- | FILENAME\n-- |   DooOrderChargeTiersInt.ctl\n-- |\n-- | DESCRIPTION\n-- |   Uploads CSV file data into DOO_ORDER_CHARGE_TIERS_INT table\n-- |\n-- | Created by\n-- |   akpanigr\n-- |\n-- | History\n-- |   01/20/2021  Initial Version\n-- | \n\nLOAD DATA LENGTH SEMANTICS CHARACTER\n--INFILE \'DooOrderChargeTiersInt.csv\'\n--BADFILE \'DooOrderChargeTiersInt.bad\'\n--DISCARDFILE \'DooOrderChargeTiersInt.dsc\'\nAPPEND\nINTO TABLE DOO_ORDER_CHARGE_TIERS_INT \nFIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' \nTRAILING NULLCOLS\n(\nSOURCE_TRANSACTION_ID           CHAR(50),\nSOURCE_TRANSACTION_SYSTEM       CHAR(30),\nSOURCE_TRANSACTION_LINE_ID      CHAR(50),\nSOURCE_TRANSACTION_SCHEDULE_ID  CHAR(50),\nSOURCE_CHARGE_ID                CHAR(120),\nSOURCE_ORDER_CHARGE_TIER_ID     CHAR(50),\nTIER_SEQUENCE_NUMBER            "fun_load_interface_utils_pkg.replace_decimal_char(:TIER_SEQUENCE_NUMBER)",\nAPPLICATION_METHOD_CODE         CHAR(30),\nAPPLICATION_METHOD              CHAR(80),\nTIER_FROM                       "fun_load_interface_utils_pkg.replace_decimal_char(:TIER_FROM)",\nTIER_TO                         "fun_load_interface_utils_pkg.replace_decimal_char(:TIER_TO)",\nBLOCK_SIZE                      "fun_load_interface_utils_pkg.replace_decimal_char(:BLOCK_SIZE)",\nADJUSTMENT_AMOUNT               "fun_load_interface_utils_pkg.replace_decimal_char(:ADJUSTMENT_AMOUNT)",\nCREATION_DATE                   expression            "systimestamp",\nCREATED_BY                      CONSTANT              \'#CREATEDBY#\',\nLAST_UPDATE_DATE                expression            "systimestamp",\nLAST_UPDATED_BY                 CONSTANT              \'#LASTUPDATEDBY#\',\nLAST_UPDATE_LOGIN               CONSTANT              \'#LASTUPDATELOGIN#\',\nOBJECT_VERSION_NUMBER           CONSTANT              1,\nLOAD_REQUEST_ID                 CONSTANT              \'#LOADREQUESTID#\'\n)\n-- Controller to load data from csv file to CSE_INT_BATCHES_B and TL table.\nOPTIONS(ROWS=1)\nLOAD DATA\nINFILE \'CseAssetBatches.csv\'\nBADFILE \'CseAssetBatches.bad\'\nDISCARDFILE \'CseAssetBatches.dsc\'\n\nAPPEND\nINTO TABLE FUSION.CSE_INT_BATCHES_B\nFIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' TRAILING NULLCOLS\n(\tINTERFACE_BATCH_CODE \n,\tDUMMYCOL1                                  FILLER\n,\tDUMMYCOL2                                  FILLER\n,\tSOURCE_SYSTEM_TYPE                         "DECODE(:SOURCE_SYSTEM_TYPE, null, \'ORA_INTERNAL\', :SOURCE_SYSTEM_TYPE)" \n,\tSOURCE_SYSTEM_CODE                         "DECODE(:SOURCE_SYSTEM_CODE, null, \'MNT\', :SOURCE_SYSTEM_CODE)"\n,   SOURCE_TRANSACTION_DATE                    "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.CONVERT_TIMESTAMP(:SOURCE_TRANSACTION_DATE)"\n,\tATTRIBUTE_CATEGORY \n,\tATTRIBUTE_CHAR1    \n,\tATTRIBUTE_CHAR2    \n,\tATTRIBUTE_CHAR3    \n,\tATTRIBUTE_CHAR4    \n,\tATTRIBUTE_NUMBER8  \n,\tATTRIBUTE_NUMBER9  \n,\tATTRIBUTE_NUMBER10 \n,\tATTRIBUTE_DATE1                           "to_date(:ATTRIBUTE_DATE1, \'YYYY/MM/DD\')"\n,\tATTRIBUTE_DATE2                           "to_date(:ATTRIBUTE_DATE2, \'YYYY/MM/DD\')"\n,\tATTRIBUTE_DATE3                           "to_date(:ATTRIBUTE_DATE3, \'YYYY/MM/DD\')"\n,\tATTRIBUTE_DATE4                           "to_date(:ATTRIBUTE_DATE4, \'YYYY/MM/DD\')"\n,\tATTRIBUTE_DATE5                           "to_date(:ATTRIBUTE_DATE5, \'YYYY/MM/DD\')"\n,\tATTRIBUTE_TIMESTAMP1                      "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.CONVERT_TIMESTAMP(:ATTRIBUTE_TIMESTAMP1)"\n,\tATTRIBUTE_TIMESTAMP2                      "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.CONVERT_TIMESTAMP(:ATTRIBUTE_TIMESTAMP2)"\n,\tATTRIBUTE_TIMESTAMP3                      "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.CONVERT_TIMESTAMP(:ATTRIBUTE_TIMESTAMP3)"\n,\tATTRIBUTE_TIMESTAMP4                      "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.CONVERT_TIMESTAMP(:ATTRIBUTE_TIMESTAMP4)"\n,\tATTRIBUTE_TIMESTAMP5                      "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.CONVERT_TIMESTAMP(:ATTRIBUTE_TIMESTAMP5)"\n,\tCREATED_BY                                 CONSTANT    \'#CREATEDBY#\'\n,\tCREATION_DATE                              EXPRESSION  "SYSTIMESTAMP"\n,\tLAST_UPDATED_BY                            CONSTANT    \'#LASTUPDATEDBY#\'\n,\tLAST_UPDATE_DATE                           EXPRESSION  "SYSTIMESTAMP"\n,\tINTERNAL_BATCH_TYPE                        CONSTANT    \'IB\'\n,\tLOAD_REQUEST_ID                            CONSTANT    \'#LOADREQUESTID#\'\n)\n\nINTO TABLE FUSION.CSE_INT_BATCHES_TL\nFIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' TRAILING NULLCOLS\n(   INTERFACE_BATCH_ID                        POSITION(1) "FUSION.CSE_LOAD_INTERFACE_UTILS_PKG.get_batch_id(:INTERFACE_BATCH_ID)"\n,\tINTERFACE_BATCH_NAME\n,\tINTERFACE_BATCH_DESCRIPTION\n,\tSOURCE_SYSTEM_TYPE                        FILLER\n,\tSOURCE_SYSTEM_CODE                        FILLER\n,\tATTRIBUTE_CATEGORY                        FILLER\n,\tATTRIBUTE_CHAR1                           FILLER\n,\tOBJECT_VERSION_NUMBER                     CONSTANT   1\n,\tCREATED_BY                                CONSTANT   \'#CREATEDBY#\'\n,\tCREATION_DATE                             EXPRESSION "SYSTIMESTAMP"\n,\tLAST_UPDATED_BY                           CONSTANT   \'#LASTUPDATEDBY#\'\n,\tLAST_UPDATE_DATE                          EXPRESSION "SYSTIMESTAMP"\n,\tLAST_UPDATE_LOGIN                         CONSTANT   \'#LASTUPDATELOGIN#\'\n,\tLOAD_REQUEST_ID                           CONSTANT   \'#LOADREQUESTID#\'\n)\n\n'
Apr 17 '23 11:04 seychelles111
Please provide me with help, I am willing to pay 40$ for this simple regex.
I am in deep in for it, Oracle has a very crappy documentation for some of their newer fusion products
Apr 17 '23 18:04 seychelles111