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

joo hoo
Follow / 17 Apr 2015 at 4:47pm / Oracle

combining db2, oracle and sql server into one product

Expand content
sorry if this is not the correct place to post this question but I don't see any obvious place for this post. we have oracle db2 and sql server databases and are looking for a single product where...

sorry if this is not the correct place to post this question but I don't see any obvious place for this post.  we have oracle db2 and sql server databases and are looking for a single product where we can see all and create tables and stored procedures.  does toad provide this functionality?

 

thanks

14 0 / Create an account to join the discussion.
kiran.pamera
Follow / 14 Apr 2015 at 3:24pm / Oracle

How To pass XML as input to oracle store procedure

Expand content
Hi Team, Can you please help me with the XML input to oracle stored procedure.I can able to use the XML inside stored procedure but I don't know I can pass in stored procedure call ? Thanks...

Hi Team,

Can you please help me with the XML input to oracle stored procedure.I can able to use the XML inside stored procedure but I don't know I can pass in stored procedure call ?

 

Thanks

Kiran

25 0 / Create an account to join the discussion.
cmatt
Follow / 3 Apr 2015 at 9:21pm / Oracle

Analytic Function - SUM

Expand content
Greetings to all I have following query, which is working perfectly fine. But I am trying to calculate the weekly sales( week1 and week2 from below query) using analytical functions. -- SELECT...

Greetings to all

I have following query, which is working perfectly fine.

But I am trying to calculate the weekly sales( week1 and week2 from below query) using analytical functions.

--

SELECT inventory_item_id,
ship_from_org_id,
SUM (ordered_quantity),
MAX (header_id),
MAX (line_id),
SUM (CASE WHEN no_of_days >= 0 AND no_of_days < 7 THEN ordered_quantity ELSE 0 END) week1,
SUM (CASE WHEN no_of_days >= 7 AND no_of_days < 14 THEN ordered_quantity ELSE 0 END) week2
FROM (SELECT
a.inventory_item_id, a.ship_from_org_id, ordered_quantity,
TRUNC (SYSDATE, 'day') - TRUNC (b.ordered_date) no_of_days,
a.header_id, a.line_id
FROM oe_order_lines_all a, oe_order_headers_all b, mtl_system_items_b c
WHERE 1 = 1
AND a.inventory_item_id = c.inventory_item_id
AND a.ship_from_org_id = c.organization_id
AND b.booked_flag = 'Y'
AND b.booked_date IS NOT NULL
AND a.header_id = b.header_id
and (c.organization_id = :xn_org_id or :xn_org_id is null)
and (c.inventory_item_id = :xn_inventory_item_id or :xn_inventory_item_id is null))
GROUP BY inventory_item_id, ship_from_org_id;

---

Is there any equivalent function available, how can I do it.

Thanks in advance

78 0 / Create an account to join the discussion.
jahanzaibniazi001
Follow / 26 Dec 2014 at 12:57am / Oracle
Latest post by on 2 Apr 2015 at 1:10pm

Error in PL/SQL bubble sorting program

Expand content
i am writing a plsql bubble sorting program but it is giving a error "ORA-06532: Subscript outside of limit" kindly help me to resolve it as i am new to plsql DECLARE temp int; type first is...

i am writing a plsql bubble sorting program but it is giving a error "ORA-06532: Subscript outside of limit"     kindly help me to resolve it as i am new to plsql



DECLARE

temp int;
type first is VARRAY(6) of integer;
arr first;
i integer;
j integer;
BEGIN
i:=0;
j:=1;
temp:=0;
arr:=first(3,1,4,2,5,9);
FOR i in 0..4 LOOP
FOR j in 1..5 LOOP
if arr(i)>arr(j) then
temp:=arr(i);
arr(i):=arr(j);
arr(j):=temp;
end if;
END LOOP;
END LOOP;
FOR i in 0..5 LOOP
dbms_output.put_line(arr(i));
END LOOP;

END

328 2 / Create an account to join the discussion.
gregb555
Follow / 1 Apr 2015 at 12:50pm / Oracle

Invalid package compilation with optional array parameter and named...

Expand content
Hi, I'v encountered a problem, possibly a bug in Toad, when package specification has a procedure with optional array parameter and I close the package by typing its name after 'END'. This...

Hi,

I'v encountered a problem, possibly a bug in Toad, when package specification has a procedure with optional array parameter and I close the package by typing its name after 'END'.



This works:

CREATE OR REPLACE PACKAGE test_cast AS
TYPE t_arr IS TABLE OF NUMBER INDEX BY VARCHAR2 (1);
PROCEDURE proc1 (p_arr IN t_arr DEFAULT CAST (NULL AS t_arr));
END;


This does not work:

CREATE OR REPLACE PACKAGE test_cast AS
TYPE t_arr IS TABLE OF NUMBER
INDEX BY VARCHAR2 (1);
PROCEDURE proc1 (p_arr IN t_arr DEFAULT CAST (NULL AS t_arr));
END test_cast;

When compiling with SQL*Plus, it works, of course.

My Toad for Oracle version: 12.6.0.53 Base Edition.

Thanks for any comments or de-bug :)

72 0 / Create an account to join the 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"
364 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"
71 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

145 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 376 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

90 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 1878 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

158 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?

133 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

269 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

113 0 / Create an account to join the discussion.