SQL & PL/SQL :: How To Create Procedure To Read Data From Database And Export It Into CSV Format
Dec 12, 2012How to create a procedure to read data from database and export it into .csv format without using utilities
View 15 Replies
ADVERTISEMENT
Reports & Discoverer :: Unable To Read Data In Image Format
Feb 5, 2013I have a database in access 2003 with the empno,empname and photo (bounded image). Using queries and an ODBC connection I have inserted the table into Oracle 8i (8.1.7). When I try to read the data using reports I get 'REP-1818 Unable to read data in image format.' error for the photo field.
View 5 Replies View RelatedPL/SQL :: How To Do A Loop On Table And Export Data In Excel Format
Dec 13, 2012i need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.
So, for this i should initialize my java class for example
public class ExcelExporter{
private String fileName;
public ExcelExporter(String fileName){
this.fileName = fileName;
}
}
So, for this issuse i should call the ExcelExporter costructor from my PL/SQL so i can create an object of it and next i use this to populate my Excel.
How its possible to create Java object from PLSQL ? I've seen on the net all procedure call only Java static methods...
Export/Import/SQL Loader :: Oracle Database 10g - Format For Control File
Jan 28, 2013I'm studying abt SQL*Loader. All I've learn it needs to have:
1. One text input file
2.Control file
3.Bad file...
But I'm confused where to put the input file...where to put the control file in which format and in control file what should I write...
My oracle version is:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL & PL/SQL :: How To Export Data To Excel Through Stored Procedure
Aug 1, 2011CREATE OR replace PROCEDURE T_name_column
AS
file_handle utl_file.file_type;
BEGIN
[Code]....
SQL & PL/SQL :: Read Blob Data From Database Into Directory?
Feb 12, 2010I can store my video in to my database.but i cannot read such file.....using this procedure
ORA-29285: file write error
CREATE OR REPLACE PROCEDURE Extract_bfile
(p_id IN NUMBER)
IS
vblob BFILE;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
[code]....
Data Guard :: Database Is Read-only After Switchover On 11g?
Oct 1, 2012we have a created primary and physical standby databases. Before the switchover the application connected to dataguard allows updates on the database. However after the switchover, it allows read-only access and we are unable to update. Any comments prima-facie ?
View 12 Replies View RelatedSQL & PL/SQL :: Export Table Data Into Text File Through Procedure / Package
Oct 8, 2012I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
noted that the data does exist 32k.
Server Utilities :: Export Procedure Views Function And Packages In Database
Sep 29, 2010How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies View RelatedPL/SQL :: Procedure / Batch File To Export Data From Sql To Excel (predefined Path)
Apr 5, 2013I have countries, sites, states tables (total 3) in database (i have user id and password to connect to this database).
every week i need to extract data from these tables into excel files and i need to save those in shared drive for team use.
Currently i am connecting to database every time running sql query and manually exporting that latest data to excel and saving that as excel files in (G: eamcommon) folder with specific name.
output format should be :
excel (.xls)
file names should - countries.xls,sites.xls,states.xls
server name : ap21
output location : G: eamcommon ( G is shared drive).
i heard that we could create batch file to do this task and also we could use oracle procedure to do this task. but not sure which one is the best option.
Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL
Mar 31, 2004ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies View RelatedSQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?
Aug 25, 2011Can we use this format for accesing data from other DB or Schema?
In From clause
database_name.schema_name.table_name
Forms :: How To Create Data Block Using Stored Procedure In 10G
Apr 30, 2010How to create Data Block Using Stored Procedure in Forms10G ?
View 1 Replies View RelatedForms :: Insert DATA In Database In Uppercase Format
Mar 19, 2013I have a datablock based , I used commit_form(); to insert data in my DB .But,I want to insert data into my DB on uppercase format!!
View 3 Replies View RelatedXML DB :: DeleteXml() Causing Data Format Error In Database
Jul 21, 2013I'm using the oracle xml db 11g in my website application. Last week I found that using the deletexml() to delete a node in the xml may cause a terrible problem that the rest data in the database had wrong xml format. I wonder is it a bug when using binary xml storage. My xml data is describe below,
<root> <nodes> <node id="1"> <name>John</name> <age>16</age> <hobby>football</hobby> </node> <node id="2"> <name>Alex</name> <age>22</age> <hobby>table tenisl</hobby> </node> ..... <nodes></root>
I using deletexml() to delete just a node once time, "update projects set object_value=deletexml(object_value, '/root/nodes/node[@id="1"]')" Usually it successes and the rest data is as expected, but sometime, I find that after deleting a node, any query in the database except "select * from projects" gets null. And the rest data has wrong format like below,
<root> <nodes> <node id="1"> <name>John</name> <age>/hobby> </node> <node id="2"> <name>Alex</name> <age>22</age> <hobby>table tenisl</hobby> </node> ..... <nodes></root>
Just one format error causes the whole xml data valueless, but it seems that in most time the deletexml() works well.
Export/Import/SQL Loader :: How To Import Data From Excel File To Table Through Procedure
Jul 2, 2012How to import data from excel(.xls) file to data base table
I have excel sheet(.xls) data details, I neet to upload details to data base table using procedure
excel sheet is not CSV file, so SQL Loader is not using
any alternative solution for this issue
Export/Import/SQL Loader :: Read CSV File And Load Into Oracle Table
Aug 27, 2012I want to read the csv file and load into oracle table.But I am getting file with filename_<today date> for every day. Is it possible to use single External table to read file in dynamic.
or what is the best way to do this? My oracle version 10g in windows OS.
Stored Procedure To Read XML Files?
Apr 23, 2009i have this stored procedures:
create or replace
PROCEDURE INSERTXML2(
p_xml_in XMLType,
p_table IN VARCHAR2 ) AS
v_context DBMS_XMLStore.ctxType;
v_rows NUMBER;
BEGIN
[code]....
that works well in little XML files but in XML files that are bigger the stored procedures to not work because string maximum length in Oracle is 4000.
im working with Oracle SQL Developer and Sap Mii, the XML file is generated in Sap Mii and then i have to pass it in one step to database..
SQL & PL/SQL :: Read PKB File Or Any Conversion Procedure?
Oct 1, 2013Can we read .pkb file or any conversion procedure
View 2 Replies View RelatedSQL & PL/SQL :: Oracle Procedure To Read All The Text
Jun 12, 2013I need to modify the procedure to read all the lines of a text. Actually in my table there is a personnel number column with duplicates and a TEXT column.I need to create a procedure to read all the text per personnel number and insert it into a another table.The below code is the procedure which is reading the two lines of text field. How can I modify it to read all the text per personnel number.
create PROCEDURE hr_load_restict_text_proc
as
CURSOR cur_hr_restrict
IS
SELECT personnel_number, line
FROM hr_restriction_text_temp
[code].......
Export/Import/SQL Loader :: Copy Data From Oracle Database To Another?
Jun 6, 2013I want to Copy a data from One oracle database to another.
I have checked Import/Export Utility but the problem is import utility doesn't support conflicts resolution techniques between rows.
For Example if there's a table in the source database have the same row key in the destination database. if i use 'Ignore' parameter with value = y, the destination table will have a duplicate rows.
I want to ask if there's another way to import data from oracle database to another with some mechanism of detecting the conflicts and resolve them?
PL/SQL :: Grant Read Only Permission On Stored Procedure
Oct 8, 2012I have a requirement like give reaonly access on my stored procedure to another user , not even execute permission on that steored procedure.
let us know the command ?
How To Create Read Only User In Oracle
Nov 16, 20131. To create a read only user who can only query the views as well as db tables and views.
2. How to create such a read-only user. 3. I have taken a full expdp backup of the production and trying to import it on the pre-production.
I use impdp
system/password table_exists_action=replace full = y dumpfile=file_name.dmp remap_schema=SYSTEM:NEW_USER remap_tablespace=SYSTEM:NEW_USER ;
This can replace and import the tables but it can't replace and add the constraints , do I have to add some other parameter in the syntax if so what it should.
Server Utilities :: Export Excel Sheet Data In Database Table?
Jul 17, 2012i want to export excel sheet in database table, so i have converted excel file in .csv file(comma delimated)and made control file, then i started sqlldr by double clicking on it. path is-D:oracleproduct10.2.0client_1BIN
i run this command from cmd-
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:UsersNeetesh>sqlldr scott/tiger@localdb control=c:/users/neetesh/scott_data.
ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:20:33 2012
[code]....
and i attached the .ctl file. and .csv file is stored on same directory as .ctl file, why oracle couldn't find the .ctl file.
Client Tools :: Export Data From Oracle Database To Excel File?
Aug 21, 2012I 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?
Create File With (other) User Having Read Permission When Using Utl_file?
Dec 23, 2012We have a requirement to create a file using UTL_FILE package. so we tried to generate the file in directory at UNIX level,for example "/tmp" which means when we executing the stored procedure which in turn calls UTL_FILE package and create a file say "a.txt". On checking the permission for "a.txt", it shows as follows
-rw-r----- oracle dba a.txt
this means read and write for oracle user, read for dba group and no permission for other user.
our requirement is to have the following privileges at UNIX level.
-rw-r--r-- oracle dba a.txt
Is it possible to do at oracle level since the file is owned by oracle user or at UNIX level (with out logging to oracle user)?
Environment: UNIX AIX 6.1 and Oracle 11.2.0.3
Export/Import/SQL Loader :: Migrating Data From Solid Database To Oracle - Flat Files
Aug 14, 2012I am migrating data from a Solid Database to Oracle, I am using Flat Files to do that.
1.- I download the data to flat files from Solid
2.- I move the files to Oracle server
3.- I upload the data to Oracle
Now, I have done the 90% of the data base, but I have found some tables that has description columns and in this description the users writes enters, so when I try to upload the data to Oracle SQL loader cannot recognize this characters.
Example:
'25','0.','5.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'26','0.','2.','0.','0.','0.','0.','3.','0.','0.','0.','0.','0.','',''
'27','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'28','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'29','0.','38.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'30','0.','13.','0.','0.','0.','0.','0.','6.','0.','6.','0.','0.','|SE RECHAZA B20CS50SNW ^M
^M
SE RECHAZAN CINCO PZAS ^M
DOS MOD. HSC15I41EH,DOS MOD. HSK15I41EH |Agregó: 06/06/2009 12:22:50
|','DEV. A PROV.'
'31','0.','50.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'32','0.','9.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'33','0.','2.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
How can I solve this ?
Export/Import/SQL Loader :: Converting Date Format Using RTRIM
Apr 22, 2013I need loading date formats using sqlldr. I am trying to load some data from another source into our Oracle database, however, the "date" field includes extra characters that i'm trying to deal with. For reference, I'm running Oracle 11.2 on Linux 5.7.
First, the actual date input is:
April 18, 2013 8:50:44 AM EDTI tried to input it a few ways but was unable to load it until I physically removed the "AM EDT" from the input string.
My first question is if there is a way to RTRIM and get rid of those extra characters. Either that, or I need a way to convert the "AM EDT" into our standard date format.
My control file has the following:
LASTCHANGEDAT date "Month dd, YYYY HH MI SS",This will load the data, but only if I strip out the "AM EDT" from the actual data input which is not feasible.
Also, how would I handle date that comes in as: April 18, 2013 3:50:44 PM EDT
Also, I tried the following, but it gives me a different error:
LASTCHANGEDAT "to_date(:LASTCHANGEDAT,'DD-MON-YYYY HH24:MI:SS')",
gives me error:
Record 1: Rejected - Error on table NTWKREP.INTERFACE, column LASTCHANGEDAT.
ORA-01858: a non-numeric character was found where a numeric was expected
SQL & PL/SQL :: Procedure That Will Format SQL
Feb 9, 2011plsql package with a procedure or function that can format sql code. I am looking for two things:
1) I want to pass a sqlid or (address,hash_value,child_number) to a package fuction/procedure and have it return the sql from v$sqltext in a formatted manner.
2) I want to pass a sql statement to a routine and get it back formatted.
I don't need lots of flexibility in how it is formatted, don't really care if commas are leading or trailing, don't care if indentation is 2,3,4 spaces. I just want something readable.
Am willing to take any set of datatypes for parameters. A refcursor going in or coming out, clob in or out, varchar2 in or out. Anything is better than what I got now cause right now I am limited to cut/paste into Toad or a web page. I am looking for a callable plsql routine. An oracle supplied package would be nice but they have never provide this to my knowledge.
Data Guard :: How To Create Standby Database For RAC
Mar 10, 2010I have two node rac running on SAN with ASM , i want to configure a single instance standby database for this.
View 2 Replies View Related