Application Express :: Importing CSV Files To Database Backend
Dec 15, 2012
There is a report that is generated everyday in the .csv file format that i would want to load into the database. I want to completely automate this process. I don't want to use the load function available in APEX. I use APEX 3.2 Version.
What i mean by automation is that i will create the CSV file and automatically move to a location from which APEX can access the data. I would then write a procedure to fetch the content directly from CSV file and write to the database. What i need is the location from which APEX can access data directly ?
I have a big problem that came up latly which is importing XML files into oracle database.The point is that I have extracted whole PostgreSQL database into XML files - 236 tables - 1 XML file for every table and now I'm about to import them into Oracle tables. First of all, I would like to point out that I already have the structure of all the tables in oracle database, the files only carry the data (records) that need to be imported into oracle.
I've been trying to make it running and I can't do anything more serious about it for over a week..I will show You all example:
That was one of my attempts to import data from file "ps_sprawozdania.xml" into table "ps_sprawozdnaia" into oracle. Here are 2 records from the XML file to show you Its structure
<NewDataSet> <Cust> <miesiac>7</miesiac> <umowa_rok>2008</umowa_rok> <umowa_nr>051/210412/01/000/08</umowa_nr> <nr_korekty>0</nr_korekty> <nazwa>Sprawozdanie z realizacji umowy nr 051/210412/01/000/08 za miesiąc Lipiec</nazwa> [code]....
Unless I'm not understanding this error it means that it can find the ACTION part that is attached to the WHAT part of this Dynamic Action? The Dynamic Action does work when the application is run (in Development)also, there are 3 others that are similar to this one. The export was created by the export utility in the Application Builder.
If I export only the page and import that into Production the import is successful and the page runs correctly. This is error is happening only when I try to import the entire Application.There are many other changes made which is why I was trying to do an Application export/import instead of individual pages.
I have a rather complicated process to import text files into my DB.I'm given thousands of files every day, separated by "," and with 80 fields each. With a bash script, I take the 45 fields I need and then split each file into x number of files grouping the rows by three fields.Then I use SQL Loader to insert them into de DB.
The problem is that now I must insert on two tables and the "WHEN" clause doesn't allow the use of > and <.
To make things a litle clearer take this text file (already splited and grouped and ready to be inserted): ... 1,1,135,1900,0,12,114,2011/08/25 17:19:00,135,... 1,1,135,1900,0,13,119,2011/08/25 17:19:00,136,... 1,1,135,1900,0,14,117,2011/08/25 17:19:00,137,... 1,1,135,1900,0,15,113,2011/08/25 17:19:00,138,... 1,1,135,1900,0,16,119,2011/08/25 17:19:00,139,... ... When field 6 is higher or equal to 14, it must go to table a.When field 6 is lower than 14, it must go to table b.I can't use external tables as I'm in a different server.
Name some database tool from which I can check the SQL Queries which my application is running.
NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.
we have a very critical application running and the backend database is 10.2.0.3. we are planing on upgrading to 10g to 11.2.0.2 and looking ways to look with minimal downtime off production. steps for upgrade with very minimal downtime of appliction?
i want to know the backend process of Oracle DB.because when ever we are taking about DB , so many things are coming into pictures like Physical,logical structures,instance,schema,servers,storage and all. Actually i know what is oracle and why we are using that .
1) I have 5 Exported Dump files. 2) All of those 5 dump files were taken in different time periods. 3) Many of those Dump files are having the same Partition records.
eg:- Dump 1:- 01-06-2010 to 31-11-2010 Dump 2:- 01-09-2010 to 31-12-2010
4) Now i want to import all those partitioning data into a single table, without having any duplication.
I'm trying to find a way to recover my APEX application that I built during 10 months. In the first time, I tried to recover, repair, restore...etc. my corrupted database 11g1 without success, in the end with nerves, I delete everything and reinstalled 11g2. Before to delete, I copied all db files including the corrupted one (system01.dbf).
I had wrote a script in order to export after any big change in my DB, a full database (using exp tool).
So now, What I have is :
1 - New and clean database. 2 - Last full export from the older one. 3 - All db files of the old database (noarchivelog).
I can import my schema that I used in my APEX app to contain data and objects (tables, sequences, triggers, views, packages, functions and procedures). But, I can't restore the APEX app.
Is there any way to recover my app from the export or from dbfiles ?
We recently updated to version 4.1.1.00.23, and need to do an export of our static files to migrate from our development to test (ApEx run-time) platform. When our DBA attempted to run the export script (we had renamed it from static_file.sql to browser_detect.sql), he received this error:
SQL> @browser_detect.sql Set Credentials... Check Compatibility... Set Application ID... ...static file repository p_notes=> ''); *
ERROR at line 14: ORA-06550: line 14, column 5: PLS-00103: Encountered the symbol "P_NOTES"
when expecting one of the following:
. ( ) , * % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ The symbol "(" was substituted for "P_NOTES" to continue. ORA-06550: line 14, column 18: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset
We took a look at static_file.sql, and noticed this:
declare l_name varchar2(255); l_html_id number := null; begin l_name := 'browser_detect.js'; wwv_flow_html_api.remove_html( [code].......
The comma is missing after the p_flow_id parameter. This appears to be a bug in the export. It appears to be happening pretty consistently (other static files for this application, other static files for other existing applications).
I did a search of the forum to see if this issue reported this and checked listing on OTN and I don't see any reference to this particular issue. We're hesitant to just add the comma right now since we don't know whether there may be any other issues in the file it's generating.
I have two tables, one with the customer address and another one with a note. May I know how to create a separate PDF file (for each customer) that should start with the customer name/address (from customer table) and followed by a note (from the note table)
I'm using apex 4.2.1.00.08 and I cannot figure out how apex manages the static files and cannot find anything in the docs (other that some high level UI description). The application is serving some file and I cannot find which one it is in any easy way.
I have a workspace where there are several files that have the same name, and I cannot understand how apex figures out which one to serve, and also don't understand what is value of associating a file with an application.
There are files associated with application 0, which don't appear to show up in the "shared components", but can be seen as
SELECT * FROM wwv_flow_files WHERE flow_id = 0;
and can apparently only be deleted using "SQL Commands" inside apex. the URL called is something like
so apparently the only parameters that matter are the workspace and the file name. The associated application is irrelevant. apparently files linked to flow_id 0 have precedence over all the other files..
I have been searching the forum (and Google) looking for tips on how to ensure users can only upload files of specific formats (Word, PDF, etc) for specific document types as defined within the application (e.g. Curriculum Vitae (Word), Copy of Transcripts (PDF)).
While I have used this research to start work on a server-side solution, I would like to know if there is an apex-friendly way to "validate" a file-browse item based on mime-type.
Application which I`m creating would work only inside domain. In the report I would like to have a link to files that stored outside from oracle server ( so apex cannot see the file but knows the absolute path to the file ).
And when I click in the report on the link to the file I would like to have this file opened in the application controlled from my OS ( naturally if I have rights to the path specified given from domain level).
I have been trying with column link ( a href, file:// ) and none of them works. I want to avoid APEX from copying files to local path or opening using java or creating directory.
if it is possible to include 'data' files as part of the Application Export process. Up till now I've only been able to include supporting object files which will recreate a table structure but not the data records - I've been creating the latter outside of APEX.
I am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?
Actually I am using Oracle 9i in Windows Operating System. Now I want to move to my entire Oracle Database to Oracle 11g in Linux version. What are all the correct steps to do this. I am not a DBA.
Is it possible to use Database authentication in APEX through database link, and how?Also is it posible to read roles from users through database link?
Process started but after sometime it gave 1200 errors. Is it due to the Different Database name or Is it because i did not create table space in destination database.
When we upgraded the database from 10g to 11g, Apex upgrade failed. Due to the failure, I removed the old schema and did a fresh install of Apex 4.1. It is working but we lost the applications from previous Apex.
What is the best strategy when you need both database and Apex upgrades? We are a Linux workshop, setting up Apex in Oracle Fusion Middleware 11g environment.
I Created a form just for testing purpose [Which is i attached]
My Table is TEST0
Structure of table : --------------------------- NOVARCHAR2(5) NAME VARCHAR2(10) FLG VARCHAR2(1)
My Task is When i added a record, e.g if on form, i add name - AAD But its must save on TEST0 Table like this way EMPAAD...means always concate with 'EMP'||:Block.name while saving on database..
I know how to do with front end application, but i want to achieve this task by using trigger on backend databse...how to create a Trigger on backend....i try a one trigger but it give me error- Unable to insert Record...
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.