I am loading a Excel file into oracle database using a ORACLE form 6i and i am getting an ORA-302000. This Form run and load database file into database in many pc.
But One of the PC COuld not loading a Excel file into oracle database. Also i can't Create Excel file Database Through form 6i.
I am loading a text file into oracle database using a form and am getting an ORA-302000.
How do I rectify it?
We have requirement such that whenever stored procedure is executed, their resultant records has to be stored in excel file ( Just like an reports ).No third party tool or reporting tools are used.
is there any option in oracle (Stored procedure or built in packages ) which can create excel file with the resultant records.
I am using SQLTools 1.5 for writing Oracle SQL scripts.
I have to import data from excel file to oracle database. How can I do it?
Also how can I export data from Oracle database to Excel file?
I am having a problem loading the date value into database using sqlldr.
The nls_date_format is 'DD-MM-RR' from nls_database_parameters and no date format for nls_instance_parameters.
I am getting ORA-01722: invalid number, the format that i used in ctl file is column_name date 'DD/MM/YYYY'
I am loading data from XML file into Oracle table.This program is working fine for small XML files. If I try to load large XML file with multiple pages, only first ten records are loaded. Here is the procedure.
PROCEDURE Test_xml_read(p_tag varchar2,p_xml_file varchar2,p_path varchar2)
AS
BEGIN
INSERT INTO stg_xml_table(
Productid,productname,price)
select y1.productid,y1.productname,y1.price
y2.categoryid,y2.categoryname,y2.categorypath
FROM xmltable('ProductFeed/Products/Product'
passing xmltype(bfilename('TEST_DIR1', 'sample.xml' ), nls_charset_id('CHAR_CS'))
[code]...
what changes to be done to load multiple pages of data pages table.
i have a problem when i try to login to oracle...i found this message error.
ora-01033 oracle initialization or shutdown in progressafter i logged on with / as sysdba...i did the following
SQL> shutdown immediate;
ORA-01507: database not mountedORACLE instance shut down.
SQL> startup mount;
[code]....
I have a bunch of data in 50 excel files. I need to load all these 50 files into 50 different tables. I would like to do this in one script. I went through the forum to get this information, people suggested create a shell script etc or list the sqlldr command multiple times etc.
provide some clarity on this as to what's the best approach.If it is through shell scripting provide the shell script and instructions to execute it. Iam new to shell scripting.
want to load data from an excel file to a database table in Oracle. I am using Oracle 11 and the excel file has 3 columns as compared to 5 columns in the destination table. I want to generate sequential nos also for the table.
View 7 Replies View Relatedcode to put files such doc ,xls , jpg
into db using lob
i am developing application where user will be browsing the file from local pc which then will be loaded into oracle db not At OS DIRECTORY .
I need to generate a single excel file which each department details in seperate sheets of the excel file, each sheet should ne named with department no (ex :Dept_10, Dept_20,Dept_30) and the main excel should be named as Emp_details.
Header row and total row should be in bold.I have created 3 individual excels thru toad and moved them to single excel and renamed the sheets.
Can we programatically do this thru SQL or PL/SQL, does Oracle provide any build packages for excel.As excel files cannot be uploaded,I am unable to upload the excel file I generated manually.
select to_char(empno) empno,ename,job,mgr,hiredate,
nvl(sal,0) sal,nvl(comm,0) comm,nvl(sal,0) + nvl(comm,0) "Total Income" ,deptno
from emp
where deptno = 10
union all
select 'Total',null,null,null,null,sum(nvl(sal,0)),sum(nvl(comm,0)),sum(nvl(sal,0))+sum(nvl(comm,0)),null
from emp
where deptno = 10;
How can i automatically export oracle query result into the excel file in oracle?
View 2 Replies View RelatedI would like to connect to oracle database and would like to execute series of SQLs and write those SQL results into Excel file (sheet1).
my requirement is simple :
------------------------
db_user_id scott
db_user_pwd tiger
db_sid orcl
select count(*) from emp;
select count(*) from dept;
print the same SQLs in A column and result should be in B column in excel file.
column A column B
select count(*) from emp; 14
select count(*) from dept; 4
thats it.
send me the procedure for loading the data in an oracle table into an excel file.
View 5 Replies View RelatedI need to email an excel file as an attachment in the mail through oracle procedure. I am using UTL_TCP option.
The remote_host i use in test environment is the 'name of the test server'. This test server is a oracle10g database on AIX server.In the test environment the mail works fine.
utl_tcp.open_connection(remote_host => 'test server',
remote_port => 25,
tx_timeout => null);
The same thing if i use in production environment, it does not work. The production server is a 9i database on a unix server.
utl_tcp.open_connection(remote_host => 'prod server',
remote_port => 25,
tx_timeout => null);
Does the 10g has option to host SMTP service as apposed to 9i?
I have an application where I generate a excel using data in a table and write excel file in oracle directory.Now my requirement is to open that file from local machine .How can i achieve it.I am using Apex 4.2,Oracle 10g .
View 1 Replies View RelatedAt the moment, we were loading the file in our system serially. This is a very old and established system.We would like to incorporate parallel loading for our loaders to load data into the database.
Most of the issues would be due to multiple inserts happening due to the files being loaded in parallel. For some reasons, we cannot give regular commits untill the entire batch of items is processed in case the process needs to rollback. A file can contain different set of batch of items clubbed together for loading.
The issue here is untill the first file finishes loading and commits, the second file would just hang. In fact, mulitiple files might hang for the first file to finish. what can I do to overcome this?I tried to used "lock table t1 in SHARE ROW EXCLUSIVE mode nowait". When the leading process is doing inserts, the failing process will fail with a resource busy and acquire with NOWAIT specified. We would catch this exception and redirect that batch to an error file to be reloaded at a later date.
I want to export the oracle data into an excel sheet. I have written the code by using UTL_FILE package. but i am getting the output as shown in the screen shot(without formatting the column size as the width of the data it has). But I want the output column width to be set according to the size of the data automatically.
View 5 Replies View Relatedis there any way you can get the name of the file loading the table in a control file? i have a table with a column called source_file, and need to populate it during the load.
View 1 Replies View Relatedi want to ask that is there any way to use the excel formulas on oracle forms, reports and database level. for example if we want to use IRR formula of excel to use in oracle forms, database and report.
View 3 Replies View RelatedI would like to ask you if you know which built-in can I use for transferring a excel file from our Unix box to a table in oracle database, right now we are using webutil_file_transfer.Client_To_DB_with_progress using forms developer, but I need to run as an automatic process uploading form unix into oracle directly without using forms.
View 22 Replies View RelatedIn fact I have around 50 excelbooks that are connect with oracle via ODBC/or any other way. My task is to build the login screen on form 10g. This form will validate the user via .dll after successfully validation, Form will connect to specific oracle user. This Login Screen will call the new form that will execute the query for the list of excel sheet [table that will keep the name, description and path of the excelbook]. User will select the one excelbook to open. As user will select Excelbook should be open. Now this excel book should be connected with specific oracle user.
Question 1: How to open the excelbook, that excelbook should be connected via form 10g login screen. After connection excel has its own micros to run he query from oracle database.
Question 2: How I can create the mirco into form 10g to run into excelbook/excelsheet.
Question 3: How I can pass the query or function/procedure to the excelbook/excelsheet.
we are using ERP System based on Oracle DB. I have an oracle client 10.2.0 installed on my machine (Win 7/64-Bit) and trying to create Excel(2010) VBA-Macro to get datas out of the Oracle DB using a SQL query:
On my old machine (Win XP/Excel 2007) following worked fine:
strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.Recordset")
oConOracle.Open strConOracle
Set oRsOracle = oConOracle.Execute(strSQL)
All the datas in the variables coming from cells or userforms - I am 100% sure all this is correct, as as said, it works on any Win XP Machine with Excel 2007
But trying same with Win 7/64-Bit/Excel 2010 gives me following error: MS ODBC Driver for Oracle: ora-01019: unable to allocate memory in the user side.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8
When i am trying to load an XSD document its failing with Error-15. When i removed the below line from the xsd doc
<xs:pattern value="[ ]*[!-~А-я][ -~А-я]*"> (& #x0410;-& #x044F;)
It went through fine. The issue is its not supporting ;А-я NCS of Cyrillic alphabet(Russian) . Guess its some kind of character set issue. But i cant identify. The same doc is working fine in 10g. issue is in 11gr2.
Loading of xsd happens from a product interface. header details
<?xml version="1.0" encoding="Windows-1251"?>
<!--Öåíòðàëüíûé Áàíê Ðîññèéñêîé Ôåäåðàöèè.
Óíèôèöèðîâàííûå ôîðìàòû ýëåêòðîííûõ áàíêîâñêèõ ñîîáùåíèé.
Ñõåìû ñ îïèñàíèåì áàçîâûõ òèïîâ.
Èñïîëüçóþòñÿ äëÿ ñîçäàíèÿ ïðèêëàäíûõ òèïîâ.
äåêàáðü 2008 ã.-->
<!--e-mail: wlad@e-burg.cbr.ru-->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:bt="urn:cbr-ru:ed:basetypes:v2.0" targetNamespace="urn:cbr-ru
:ed:basetypes:v2.0" elementFormDefault="qualified" attributeFormDefault="unqualified" version="2.4.0">
Iam using Oracle client 11g(32 bit) and Oracle database 11g on Unix server.
I have the created the following procedure to load XML file located in TEST_XML oracle directory( which is mapped to
DB/bulk01/REFLIB/REFDV/LOAD_XML) on the server.
Create or replace procedure TEST_LOAD_XML IS
dest_clob clob;
src_clob BFILE := BFILENAME('TEST_XML', 'arenas.xml');
dst_offset NUMBER := 1;
src_offset NUMBER := 1;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning NUMBER;
[Code]....
when I execute the above procedure I get ORA-22288 file or LOB operation FILEOPEN failed No such file or directory error.
Looks like it did not find my oracle directory 'TEST_XML'
do I have to do anything like mapping? if so since my database is on unix server how do I map?
In my database server i am not able to login to the database via sqlplus and exp/imp also having error as below. But i am having permission to the this executable. I am falling under other user catagary
export ORACLE_SID=TEST
export ORACLE_HOME=/dboracle/orabase/product/10.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin
Error:sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
Permission details.
testuser@sgh3001:/dboracle/orabase/product/10.2.0.3/bin> ls -lrt sqlplus
-rwxr-x--x 1 oracle dba 12924 2007-03-27 10:21 sqlplus
I am using an Oracle 8i database.We intend creating a dbms_job that would run every 5 minutes. The job executes a script that would use utl_file to read two text files (a header file and detail file) from a particular directory and load the data into temporary tables. The naming convention of the files are Parts_Master_DD/MM/YY HH:MI:SS and Parts_Stats_DD/MM/YY HH:MI:SS. Both files to be stored in the same directory.
If the file name was constant ie. Parts_Master, Parts_Stats I would not have a problem. However with each file name now going to be unique, how do I handle this using utl_file. To add to my dilemma, within an hour more than one set of files could be piped to the directory to be read into the database.
if there is any package provided with DB that would allow me to import XML file and then use XPath to read the data? I need to insert it in proper tables then. I could see packages to create XML files, but I could not see any one to load it from the file. I could see it could be done in Java using XMLDOM, but is there any way of doing this using pure PL/SQL?
View 4 Replies View RelatedIs it possible to trim the file name while loading into OWB through external tables?
Like suppose I am trying to read a file which has a timestamp value appended in its name. In that case loading into external file would give an error.
I am loading data from a .csv file to table. I tried to load by using EXTERNAL TABLES
Is there a way to specify null in external tables loaded if specific column has no data in the external file(CSV) being loaded ?