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
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'
I am having issues with getting
each tableandeach fieldthis 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