You will no longer receive e-mail notifications from this forum.
Log-In to post
These people make Oracle great and have the most points for this month!
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!
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?
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 ?
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) week2FROM (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
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 plsqlDECLARE
temp int;type first is VARRAY(6) of integer;arr first;i integer;j integer;BEGINi:=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;
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'.
CREATE OR REPLACE PACKAGE test_cast ASTYPE 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 ASTYPE t_arr IS TABLE OF NUMBERINDEX 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: 18.104.22.168 Base Edition.
Thanks for any comments or de-bug :)
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
select employee name from employees where employee-name in "C:\names.txt"
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.
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.
Toad 12.6 64-bit
Oracle 22.214.171.124 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
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 t1set t1.played_previous_year = case when t2.cust_id IS NOT NULL then 1 else 0 endfrom JWxsellallprod t1left join (select a.Year_of_Play, a.Year_Aquired, a.cust_idfrom JWxsellallprod ainner join JWxsellallprod bon b.cust_id = a.cust_idwherea.player_days > 0andb.player_days > 0andb.Year_of_Play = a.Year_of_Play - 1anda.Year_Aquired = b.Year_Aquired) t2on t2.cust_id = t1.cust_idand t2.Year_of_Play = t1.Year_of_Playand t2.Year_Acquired = t1.Year_Acquired
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 bya.SERVICE_IDorder bya.SERVICE_ID
Hi everyone, i have a SQL script belowSELECT 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.
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?
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:
1 declare c7 varchar2(9);
3 select trim(to_char(systimestamp(1),'hh24missff')) into c7 from dual;
4 --c7 := trim(to_char(systimestamp(1),'hh24missff')) ;
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Wrote file afiedt.buf
1 declare c7 varchar2(9);
3 --select trim(to_char(systimestamp(1),'hh24missff')) into c7 from dual;
4 c7 := trim(to_char(systimestamp(1),'hh24missff')) ;
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
Can somebody explain why?
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.