Oracle

Oracle Community

NOTE: This forum is for non-product questions and discussions on the Oracle database platform.
 
Please post Toad-family product questions in their respective product forums.  Thanks!

Community Discussion

ora
Follow / 21 Mar 2015 at 1:20pm / Oracle
Latest post by on 24 Mar 2015 at 7:43pm

script that pulls data from a txt file in the where clause

Expand content
I am trying to write an sql script in toad for oracle version 9.5 that when run will allow the user to select data from a txt file from the server where the parameters / variables are entered. The version...

I am trying to write an sql script in toad for oracle version 9.5 that when run will allow the user to select data from a txt file from the server where the parameters / variables are entered. 

The version of oracle is 10g

e.g 

select employee name from employees where employee-name in "C:\names.txt"
254 8 / Create an account to join the discussion.
ora
Follow / 21 Mar 2015 at 1:24pm / Oracle

script that pulls data from a txt file in the where clause

Expand content
I am trying to write an sql script in toad for oracle version 9.5 that when run will allow the user to select data from a txt file from the c drive where the parameters / variables are entered. The version...

I am trying to write an sql script in toad for oracle version 9.5 that when run will allow the user to select data from a txt file from the c drive where the parameters / variables are entered. 

The version of oracle is 10g

e.g 

select employee name from employees where employee-name in "C:\names.txt"
55 0 / Create an account to join the discussion.
amyFB
Follow / 19 Mar 2015 at 5:56pm / Oracle
Latest post by on 19 Mar 2015 at 10:52pm

Discoverer compared to TOAD, for Oracle DB reporting

Expand content
For the role of DBA, we outsource to a qualified 3rd party for maintenance of our Oracle based ERP system. We purchased the Discoverer v.10g BI tool at the same time and setup included creation of some...

For the role of DBA, we outsource to a qualified 3rd party for maintenance of our Oracle based ERP system.

We purchased the Discoverer v.10g BI tool at the same time and setup included creation of some Discoverer reports (or views; i'm not always clear which is which)

Not one of our team has advanced beyond basic beginner status with any of the Discoverer capabilities.

One of our team has accepted the report writing role and uses TOAD to create views and port the data over to excel for further manipulation and formatting. This same person claims TOAD far exceeds Discoverer capabilities. My DBA states they are two completely different tools.

Is there anyone here that could provide a simple description of the differences between these two resources?

Do both tools have a valid role for differing purposes, or, do they accomplish the same result by different means?

Thanks in advance for your input.

Amy

117 3 / Create an account to join the discussion.
anselt
Follow / 10 Mar 2015 at 3:04pm / Oracle
Latest post by on 10 Mar 2015 at 5:43pm

No valid Oracle clients found. You need at least one 64-bit client...

Expand content
Toad 12.6 64-bit Oracle 11.2.0.4 64-bit Production with the Partitioning option (shows when signing into SQLPlus) From my support bundle: Environment Variable (PATH) : C:\app\oracle\product\11.2...

Toad 12.6 64-bit

Oracle 11.2.0.4 64-bit Production with the Partitioning option (shows when signing into SQLPlus)

From my support bundle:

Environment Variable (PATH) :
     C:\app\oracle\product\11.2.0\client_1
     C:\app\oracle\product\11.2.0\client_1
     %PATH%
     C:\Windows\system32
     C:\Windows
     C:\Windows\System32\Wbem
     C:\Windows\System32\WindowsPowerShell\v1.0\
     C:\Windows\system32
     C:\Windows
     C:\Windows\System32\Wbem
     C:\Windows\System32\WindowsPowerShell\v1.0\

Environment Variable (TNS_ADMIN) : C:\app\oracle\product\11.2.0\client_1\network\admin

************************************************************************
ORACLE HOMES DATA
************************************************************************
 (Oracle Root)
      = C:\app\oracle\product\11.2.0\client_1

Unanswered 242 2 / Create an account to join the discussion.
jesper.wallman
Follow / 10 Mar 2015 at 4:26pm / Oracle

Can't insert values into a created column

Expand content
So I created a new column through the following script below. I now want to insert values binary values into the created column whether of if cust_id is within (1) or outside (0). I'm have tried to...

So I created a new column through the following script below. I now want to insert values binary values into the created column whether of if cust_id is within (1) or outside (0). I'm have tried to left join to get the table for cust_id that should have 1 and then give the rest 0. When I'm trying to run it I get the following error: ORA-00933: SQL command not properly ended . Does anyone know what I can do to solve this?

ALTER TABLE
JWxsellallprod
ADD
previous_year_flag number;

update JWxsellallprod t1
set t1.played_previous_year = case when t2.cust_id IS NOT NULL then 1 else 0 end
from JWxsellallprod t1
left join
(select a.Year_of_Play, a.Year_Aquired, a.cust_id
from JWxsellallprod a
inner join JWxsellallprod b
on b.cust_id = a.cust_id
where
a.player_days > 0
and
b.player_days > 0
and
b.Year_of_Play = a.Year_of_Play - 1
and
a.Year_Aquired = b.Year_Aquired) t2
on t2.cust_id = t1.cust_id
and t2.Year_of_Play = t1.Year_of_Play
and t2.Year_Acquired = t1.Year_Acquired

Thanks

Jesper

77 0 / Create an account to join the discussion.
jason
Follow / 16 Aug 2013 at 4:49pm / Oracle
Latest post by on 9 Mar 2015 at 7:33pm

ORA-01427 single-row subquery returns more than one row

Expand content
Hello All Have select statement that I can’t get to run. I have a data table and reference table Issue is that my reference table is updated 4x times day along with my data table with it being...

Hello All

Have select statement that I can’t get to run.

I have a data table and reference table

Issue is that my reference table is updated 4x times day along with my data table with it being the same look up data.

When i join on the reference table its inflating my numbers.

Wondering how i can join on my reference table and have a distinct join


a.SERVICE_ID,
count  ( a.Dialout) as "Dials",
count  ( a.DISP_ID) as"Connects",
sum (c.CONTACT)  as "Contacts"

FROM call_data a

JOIN reference_table c

        ON a.DISP_ID = (select distinct c.DISP_ID
                                                 from reference_table c
                                                where UPD_DATE = (select max (UPD_DATE) 
                                                from reference_table))

group by
a.SERVICE_ID
order by
a.SERVICE_ID

Thanks

Unanswered 1810 2 / Create an account to join the discussion.
ajen7118
Follow / 4 Mar 2015 at 9:05pm / Oracle
Latest post by on 6 Mar 2015 at 6:06pm

DATE Format question

Expand content
Hi everyone, i have a SQL script below SELECT ITEM AS "ClassCode", Appdate AS "EstablishedDate", --> output to txt file from toad is for example : 1/1/2008 (without any single or...

Hi everyone,

i have a SQL script below

SELECT ITEM AS "ClassCode",
Appdate AS "EstablishedDate",

--> output to txt file from toad is for example : 1/1/2008 (without any single or double quotes)

However, according to the new format, i convert it to

SELECT ITEM AS "ClassCode"
TO_char(Appdate,'YYYY-MM-DD') AS "EstablishedDate"

--> output to txt file  is for example : 1/1/2008 (without any single or double quotes) is : "2008-01-01" (This is what they want)--> how can i remove those double quote from script? or can i covert it to numeric value? 

in database, there is no quote surrounded.

Thank you

138 1 / Create an account to join the discussion.
emadtoad
Follow / 3 Mar 2015 at 5:56pm / Oracle
Latest post by on 3 Mar 2015 at 9:56pm

Toad tools

Expand content
Is there a feature in Toad that shows all the tables in a specific schema and all the data related to the tables instead of writing a query to pull this data?

Is there a feature in Toad that shows all the tables in a specific schema and all the data related to the tables instead of writing a query to pull this data?

108 2 / Create an account to join the discussion.
CFig
Follow / 28 Jan 2015 at 9:33pm / Oracle
Latest post by on 24 Feb 2015 at 2:01pm

systimestamp in PL/SQL

Expand content
When I select a function of systimestamp(1) into c7, all is well. When I assign c7 := a function of systimestamp(1) , I get an error PLS-000306. See code below: PQRPSB01> l 1 declare c7 varchar2...

When I select a function of systimestamp(1) into c7, all is well.  When I assign c7 := a function of systimestamp(1) , I get  an error PLS-000306.  See code below:

PQRPSB01> l

  1  declare c7 varchar2(9);

  2  begin

  3  select trim(to_char(systimestamp(1),'hh24missff')) into c7 from dual;

  4  --c7 :=  trim(to_char(systimestamp(1),'hh24missff')) ;

  5  dbms_output.put_line(c7);

  6* end;

PQRPSB01> /

1609119

 PL/SQL procedure successfully completed.

 PQRPSB01> /

1609138

PL/SQL procedure successfully completed.

PQRPSB01> edit

Wrote file afiedt.buf

   1  declare c7 varchar2(9);

  2  begin

  3  --select trim(to_char(systimestamp(1),'hh24missff')) into c7 from dual;

  4  c7 :=  trim(to_char(systimestamp(1),'hh24missff')) ;

  5  dbms_output.put_line(c7);

  6* end;

PQRPSB01> /

c7 :=  trim(to_char(systimestamp(1),'hh24missff')) ;

                    *

ERROR at line 4:

ORA-06550: line 4, column 21:

PLS-00306: wrong number or types of arguments in call to 'SYSTIMESTAMP'

ORA-06550: line 4, column 1:

PL/SQL: Statement ignored

PQRPSB01>

 Can somebody explain why?

Thanks,

Carl

238 3 / Create an account to join the discussion.
ecelebi@ciu.edu.tr
Follow / 23 Feb 2015 at 12:14pm / Oracle

Academic Licence

Expand content
Hi, I am an instructor at an university, and I am intended to use Oracle for DBMS II course. So I need Toad for Oracle to be installed on the laboratory computers for the students. How can I download...

Hi,

I am an instructor at an university, and I am intended to use Oracle for DBMS II course. So I need Toad for Oracle to be installed on the laboratory computers for the students. 

How can I download the academic version rather than the freeware.

Best Regards

98 0 / Create an account to join the discussion.
parthipanmurali
Follow / 16 Feb 2015 at 11:02am / Oracle
Latest post by on 16 Feb 2015 at 9:25pm

oracle

Expand content
when i have start oracle 12c database using sql developer but it shows initialization or shutdown in progress . any one help me come over the error??????

when i have start oracle 12c database using sql developer but it shows initialization or shutdown in progress. any one help me come over the error??????

Unanswered 135 1 / Create an account to join the discussion.
mhungu.muridzo
Follow / 16 Feb 2015 at 6:55am / Oracle
Latest post by on 16 Feb 2015 at 5:16pm

Oracle 11g Undo tablespace filling up exponentially

Expand content
I have an oracle database 11g, there is a daily process that I run that is filling up the undo tablespace exponentially to 3.6 GB before the used space comes down to below 10 MB and the tablespace size...

I have an oracle database 11g, there is a daily process that I run that is filling up the undo tablespace exponentially to 3.6 GB before the used space comes down to below 10 MB and the tablespace size will remain at 3.6 GB way above the initial size of 500 MB. I have 3 redo log groups sized at  100 MB each. My question is what leads to that exponential space usage in my undo tablespace? Please assist.

164 1 / Create an account to join the discussion.
Aftab Ahmad
Follow / 21 Nov 2014 at 2:06am / Oracle
Latest post by on 13 Feb 2015 at 5:06am

Database_Creation

Expand content
As we know ,we can create a database by method DBCA,OMF and MANUALLY, I want to know there is any another method to create a database and also I want to know what is advantage

As we know ,we can create a database by method DBCA,OMF and MANUALLY, I want to know there is any another method to create a database and also I want to know what is advantage

227 1 / Create an account to join the discussion.
mistre83
Follow / 12 Feb 2015 at 12:21pm / Oracle

Performance with FOR IN

Expand content
Hi to all,. there are difference in term of performance using a FOR IN (select ....) instead of CURSOR with the same query and using OPEN CURSOR/FETCH ?

Hi to all,.

there are difference in term of performance using a FOR IN (select ....) instead of CURSOR with the same query and using OPEN CURSOR/FETCH ?

112 0 / Create an account to join the discussion.
Crazyforcubes
Follow / 11 Feb 2015 at 8:37pm / Oracle

Can I write MDX queries on Toad for Oracle?

Expand content
Hello, I am new to Toad, Can I write MDX queries on Toad for Oracle Base 11.5? If Yes, any settings to be made? If no then which toad software will allow me to write MDX queries? Thanks for your...

Hello,

I am new to Toad, Can I write MDX queries on Toad for Oracle Base 11.5?

If Yes, any settings to be made? If no then which toad software will allow me to write MDX queries?

Thanks for your help.

-Aakash

96 0 / Create an account to join the discussion.