Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Write a pl/sql procedure to transfer data from excel to oracle table
HI Team,
I have this requirement for writing plsql scripts that How to write a pl/sql procedure to transfer data from excel to oracle table?
please share the scripts
in excel sheets have columns and location c:\excel\sales.xls
snbr groupid e_date end_date new_val old_val
1234 A23456 2022-02-01 2022-02-01 10 5
1234 A23457 2022-02-01 2022-02-01 5 20
1234 A23458 2022-02-01 2022-02-01 100 50
Table Structure
create table sales_upd (snbr number(38,0),groupid VARCHAR2(50), e_date date, end_date date , new_val number(38,0), old_val number(38,0))
Thanks
Answers
-
Why? Once off load? Save Excel file as CSV and use SQL-Developer or SQL-Loader to load it.
If automation is required, what are the requirements? PL/SQL code cannot hack from inside an Oracle server process and read a client's Excel file on the client's laptop or PC.
Upload the file? What client s/w and protocol? WinSCP? Browser HTTP? .Net using SQL*Net? Java using JDBC?
And how are errors and load results to be presented to the client?
Think things through, establish requirements and goals, BEFORE hitting the keyboard wanting to copy and paste sample code.
-
As per Requirement that changes need to include the existing projects so i need this changes
-
For inserting many rows into some Oracle table, sqlldr is the best. If you wish complex processing while transferring to Oracle only little data, then connect to Oracle via ADO and thus, besides executing SQL statements passed from Excel to Oracle, you may call Oracle stored procedures and get data from Oracle into Excel sheets. That is what I did years ago when I made a framework for obtaining reports in Excel extracting data from Oracle using stored procedures (Excel connected to Oracle as an user that could not see the data tables, but could only execute procedures in a package in order to get data from the database).
-
This statement is devoid of any and all details such as:
- why use PL/SQL?
- where is the Excel file to read and process?
- what is the Excel format version used?
- what need to be done with runtime errors encountered? (Excel error, null cells, existing row in table, etc.)
Just how on earth are you going to write ANY code in PL/SQL without KNOWING these basics!?
-
As per Requirement that changes need to include the existing projects so i need this changes
And we know nothing about "the existing projects".
-
You could try looking at this answer:
