FAQ Database Discussion Community


Determining locatation of relevant tnsnames.ora file

oracle,sqlplus,toad
I installed both the 32 and 64-bit Oracle 11g drivers. I search my PC looking for files with the name "tnsnames.ora" and found 3 in the following locations: 1. C:\Oracle\product\11203_32bit\CLIENT_1\NETWORK\ADMIN 2. C:\Oracle\product\11203_64bit\CLIENT_1\NETWORK\ADMIN 3. C:\Windows\TNS The existence of the 3rd location of the tnsnames.ora file surprises me. I have the following...

SQL Server: Issues with data type

sql,sql-server,toad
SELECT pa.[type], (SUM(pa.Actions_Logged)/SUM(pi.Impressions_Served)) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; The above query generates an error: Lookup Error - SQL Server Database Error: Arithmetic overflow error converting expression to data type int. Both pa.Actions_Logged and pi.Impressions_Served are int and the result...

Strange behaviour, results from linked DB

oracle,toad,dblink
Running this query on Toad select * from [email protected] where "CustomerNumber"=1 Gets a single row results, however select * from [email protected] where "CustomerNumber"=(select 1 from dual) Still returns a single row, but almost every column is NULL Any data-conversion functions or links to other tables cause this same effect. Any...

SQL*Loader Control File Custom Date Format

oracle,toad,sql-loader
I need to import from a CSV file in which timestamps are given in the following format 2014-06-14T09:38:29 I tried the following in the control file for SQL*Loader but it doesn't work TIME DATE "YYYY-MM-DDTHH:MI:SS" and TIME DATE "YYYY-MM-DDTHH24:MI:SS" How can I parse this custom date? The error I get...

Unable to install (re-install) Toad DB2

installation,db2,toad
When I try to re-install Toad, i get the following error message: "The Toad for DB2 installer has detected an existing 64-bit DB2 environment on your computer. This installation of Toad for DB2 includes a 32-bit DB2 client. However, DB2 does not allow both 32-bit and 64bit DB2 Environment to...

ORA-01830: date format picture ends before converting entire input string in TOAD

oracle,function,datetime,toad,to-date
I have script in which i am writing functions and procedures and the script works absolutely fine in Oracle Sql developer without any errors or warning. This script i have to provide it to client side customer. The customer user runs the same running script in TOAD and he got...

Excel Import Data from SQL- Date comes in as text value

sql,sql-server,excel-vba,access-vba,toad
I'm importing data from oracle toad database into an Excel sheet using data connection. The table includes a Date column, but this column comes to Excel as text rather than the date. Is there any way I can fix this issue? Sub Show_data() Dim con As ADODB.Connection Dim recset As...

Getting an error for sql query “(full) year must be between -4713 and +9999, and not be 0”

mysql,sql,toad
I am getting an error "(full) year must be between -4713 and +9999, and not be 0" unable to figure why this error is coming. SELECT B.TST_CTR_CDE, C.DESCRIPTION,TO_CHAR(TO_DATE(B.CMPLT_DTE,'yyyymmdd'), 'MONTH') Month, sum(decode(b.pass_sta,'1',1)) as passed, sum(decode(b.pass_sta,'0',1)) as failed, sum(decode(b.pass_sta, '0',1,'1',1)) as tot, extract(month from TO_DATE(B.CMPLT_DTE,'yyyymmdd')) Mon, extract(year from TO_DATE(B.CMPLT_DTE,'yyyymmdd')) year FROM aei052...

select rowid Oracle

oracle,oracle10g,toad,rowid
Im trying to display the rowid pseudocolumn, but something strange happened, This is my query select rowid, rowid||'', rowid r from dual output: ROWID||'' R AAAAECAABAAAAgiAAA AAAAECAABAAAAgiAAA So why cant display rowid without alias or not concatenated with something else ? Any explanation ? I used Toad and Oracle 10g....

Why does TOAD mess up the output order in dbms_output?

oracle,toad
I am using Oracle 10.2 and Toad for Oracle Xpert Version 11.5.0.56 Why does this script print things in the wrong order in the DMBS Output window? BEGIN FOR indx IN 1 .. 70 LOOP dbms_output.put_line('-------------------------------------------------- TEST --------------------------------------------------'); dbms_output.put_line('INDEX: ' || indx); dbms_output.put_line('CREATING: 3424234 - TEST - TEST.TTEST'); dbms_output.put_line('CREATION ORDER:...

In TOAD, is there a way to comment out arbitrary blocks of code (i.e not whole lines)?

keyboard-shortcuts,toad
The shortcut to commend out code in TOAD (11.6.16) is CTRL+B However, the problem is that it does not let you comment out an exact selection within a block of code. So I when I run CTRL+B it simply comments out ALL the lines of the code , so I...

why objects are being created in sys schema?

oracle,toad,oracle11gr2
My problem is that when i create an object it is always stored in sys schema. Normally when you do not specify the schema, the object should be created in the current schema. I'm using toad 12, and the tab current schema is setted correctly. What i did wrong? Any...