We wrote a query to spool the results in TAB delimited flat file, Results are ok but when copied into excel the leading zeroes are getting wiped off.
Example:
Result: 0000123 and when copied into excel the value is just 123. I need to retain the value as it is 0000123.
Can we control it in the query itself rather than correcting the excel.
I am generating an XML file from plsql using utl_file.There is a problem in number field.The value in the table is 0.18 and it is showing up in xml as .18. The leading zero is not showing up.
COLUMN_NAME is number (15);
<TAG>' || A.COLUMN_NAME || '</TAG>
I have tried using to_char(), but it is still showing up as .18 in XML. I want it as 0.18 in XML as well.
This package is generating excel file which contains cursor result.In excel data is populated like below.Column name is Zip_code .My concern is how to remove that single quote from excel file.
eg:
Zip_
'01234
'12567
'23432
'00234
create or replace
PACKAGE BODY PKG_MONTH_END_AUTOMATION AS
PROCEDURE PROC_ZIP_CODE_MONTHEND (directoryOrPath IN VARCHAR2 default 'LOC_PHASE1_WHOUSE_SALES_ADMIN')
[code]...
-- main body
BEGIN
-- Generating Zip Files
SELECT last_day(add_months(sysdate,-1))
INTO v_last_date
[code]...
I have a specific requirement. Currently in our system a SQL script is automated trough TOAD DATA ANALYST (Contains 50 sql Select statement) and all the output are stored in a single excel file in multiple worksheet. All these sql statements are running sequentially. Now my requirement is
1) All the sql queries will be executed in database in parallel .
2) All the output of select statement will be stored in a single excel.
3) Idea using any Client but Toad is preferred.
I have to export the results of a query to an Excel spreadsheet. Easy enough; however it is that I have to do it for each group. What I want to do is to be able to save it in a file that has the group number.
This is the query Lets say that I have the groups in the valiable RegionID: 11213, 21345 and 6537
@export on;
@export set filename="C:11213IP_claims_11213_.xls"
select * from mytable
where RegionID=11213;
How I can make it that I do not have to manually change the folder name in the direction and the where statement for each RegionID?
Anyway, I do not have much experience with databases (with MS Excel I do) but most of the problems I have been able to resolve by myself.I am using a TNS database.
I want to import data from SAP to the database, but we are having some problems with the connection and so on.Anyway, the only way to get the data into the database is to key them in manually or to import them.I have been using the import function (I had a lot of trouble with date formats) and it worked:I selected the "Import Data", the corresponsing file and then have been able to see an extract of the data. It worked and I have been able to import data from an Excel file to the database.
But an error occured: Some lines have not been copied.So I wanted to reimport them.But from this point of time I have not been able to open any Excel file anymore...When I select the file no extract/preview is given anymore and the file seems to be empty.I cannot select tabs of the Excel file or anything like this.
I restarted the computer, established a new connection to the database and even reinstalled my client.It seems that one setting has changed.I am still able to import the data from *.csv files, but this is not as comfortable that using Excel.
i have windows 7 on machine. When i am trying to connect to oracle client using macros from excel like
OraSession = CreateObject("OracleInProcServer.XOraSession")
its giving me error saying "activex component cant create the object."
I have a question regarding data extraction to Excel.Is there any patch or update that makes it possible to extract to Excel 2007 and not only 2003?
View 6 Replies View RelatedWe have a table partymast. we want to import the data into this table using excel file having extension .csv. I am explaining all the things below:
select *from partymast
partyid partyname accountname aacname
aa aa 10014000023367 Ashish
select * from master
masterid mname
10014000023367 Ashish
aacname column in partymast table is fetching from master table mname column.
We have third table name IMPEXP
Iename iedesc ietype iedef
import party master import party master imp {ImportStart}
[Transid]=party
[ImportFile]=:
[FileType]=excel
PartyID=col_1
PartyCat=col_2
AccountName=col_3
{ImportEnd}
Sir, I am enclosing the Excel file. what will be the next step that we should filllow.
i'm working on sql developer my table contains 40 columns and contains around 4 to 5 lakhs records........
when i'm trying to export the results into excel or text file my sql developer is getting hanged... if the result is less than 2lakh record its copying....
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 RelatedI 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?
when i'm saving the result in excel sheet....results are saving but unable to paste column / header names.
View 9 Replies View RelatedHere is one way to create EXCEL file from oracle sql query and prevent excel displaying large numbers in scientific notation(exponential notation)
set feedback off
set verify off
set heading off
spool c:excel_test.xls
select 'PO_NUMBER'||chr(9)||'VENDOR_NUMBER' from dual
union
select '=PROPER('||po_number||')'||chr(9)||'=PROPER('||vendor_number||')'||chr(9)
from invoices
where rownum < 12
order by 1 desc
Note that PO_NUMBER is 16 characters, VENDOR_NUMBER is 15 characters in invoices table.
How can I connect excel from pl / sql to copy results from the query. I was trying with OLE2 but it is throwing error as "invlid Identifier". Then how to include OLE2 in oracle.
View 10 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.
which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?
Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?
Does it come with Oracle Database license or we have to buy it separately?
I'm looking for 3 simple things.
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.
# Host name is vmwwin7634.na.SAS.com
# SID is sting2
# SERVICE_NAME is sting2.na.sas.com
PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2
THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
I am using SQuirreL Client version 3.4.0
# Here is the tnsnames.ora entry on the server.
STING2 =
(DESCRIPTION =
[code]...
# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.
# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)
# "New Method"
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works)
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors)
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
can we use oracle client version to create a database in that server.
View 3 Replies View RelatedI have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.
So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following
export TNS_ADMIN=/usr/lib/oracle/network/admin
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/bin
export ORACLE_SID=VFIODSD1
when I try
# sqlplus
username :xxxxxxx
password : xxxxxxx
ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.
Can i install Oracle 10g Client 32 bit on our Windows Server 2008 R2 64 bit?
View 1 Replies View RelatedI need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.
View 4 Replies View RelatedI want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.
View 5 Replies View RelatedI usually work in oracle using the different editors like toad, plsql developer etc. I felt command line is good utility because of various supporting points. I am not sure what kind of command line tools is available/used. I am using pretty straight command line
(run->cmd, then enter username and password).
The best command line tools available. What is generally used command line tools.
The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi. for Unix AIX 5
I install the ODBC Driver Manager, Basic Instant Client, ODBC Instant Client packages (libsqora.so, odbc_update_ini.sh).
The error:
[oracle@d4 oracle] $ isql OracleODBC-10g user passtemp -v
[01000][unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_10_2/libsqora.so' : file not found
[ISQL]ERROR: Could not SQLConnect
Configuration
odbcinst.ini
[Oracle10gODBCdriver]
Description = Oracle ODBC driver for Oracle 10g
Driver = /u01/app/oracle/product/10.2.0/db_1/lib/libsqora.so
Setup =
FileUsage =
CPTimeout =
CPReuse =
The documentation explic with exists the file libsqora.so.10.1 but not exists in the ODBC Instant Client packages.
I applied ln -s libsqora.so.10.1 libsqora.so but the connection not function
odbc.ini
[oradns]
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
CloseCursor=T
[code].......
I have Oracle 7 database on Server1. I need to connect this using Oracle client from windows XP.
I have downloaded Oracle client 3.0 and when I am trying to connect it saying "Unable to connect: Unsupported Oracle version".
I would like to know if there is some specific file who identifies the installation of Oracle Client in some machine.
View 4 Replies View RelatedI have an Oracle server on CentOS 5.4, I can visit isqlplus on server, but I can't visit isqlplus on a windows client. ping works between server and client. I remember client doesn't need to install software via isqlplus, but I am not pretty sure.
View 2 Replies View RelatedI receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:
CREATE TABLE leading_0_test
(
col_1 VARCHAR2(10)
)
insert into leading_0_test values('00123')
select trim(leading'0' from COL_1),trim(leading'0' from substr(COL_1,1,1)),COL_1 from leading_0_test
The first trim removes all leading zeros. Therefore I tried to solve it with the substr, but without success.