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

skashka
Follow / 14 May 2015 at 12:00pm / Oracle

how to execute procedure conatining object type as in parameter?

Expand content
create or replace procedure GETINFO(P_IDS in IDS_TABLE) is --- IDS_TABLE is a table of object type conatins id1,id2,id3 cols. begin for I in 1 .. P_IDS.COUNT LOOP DBMS_OUTPUT.PUT_LINE(P_IDS(i).ID1 || '...

create or replace
procedure GETINFO(P_IDS in IDS_TABLE) is   ---  IDS_TABLE is  a table of  object type conatins id1,id2,id3 cols.
begin
for I in 1 .. P_IDS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(P_IDS(i).ID1 || ',' || P_IDS(i).ID2 || ',' || P_IDS(i).ID3);
END LOOP;
end GETINFO;

how to execute this procedure to get the out put?

67 0 / Create an account to join the discussion.
parthipanmurali
Follow / 5 May 2015 at 7:21am / Oracle
Latest post by on 11 May 2015 at 8:41pm

INSERT

Expand content
i need one insert query in oracle but it will insert multiple row in table. Please help me to do.

i need one insert query in oracle but it will insert multiple row in table.

Please help me to do.

Unanswered 183 4 / Create an account to join the discussion.
ss14
Follow / 20 Apr 2015 at 6:33am / Oracle
Latest post by on 3 May 2015 at 11:06am

Toad connection to a Oracle 10.2.0.3

Expand content
Hi, Sorry I am very new to this tool. I would need to connect to a Oracle 10.2.0.3 . What I have done so far download the sqlplus client and the relevant OCI directory all into one directory. I have...

Hi,

     Sorry I am very new to this tool. I would need to connect to a Oracle 10.2.0.3 . What I have done so far download the sqlplus client and the relevant OCI directory all into one directory. I have set the path, ORACLE_HOME and TNS_ADMIN all to this folder where the sqlplus client . There after I tried to run my toad it say no client is found. I am not sure must is my toad the issue ? My toad say 12. 6.0.53 does this correspond to the Oracle version?

Unanswered 148 5 / Create an account to join the discussion.
kaijohnson89
Follow / 1 May 2015 at 10:11pm / Oracle

Loading New PL/SQL Data Table In Toad V12.6.6.53

Expand content
I am using Toad 12.6.6.53. When I try to create a table and import an Excel Sheet with more than 256 columns it gets cut off and the columns become non-custom. It goes back to excel letters instead of...

I am using Toad 12.6.6.53. When I try to create a table and import an Excel Sheet with more than 256 columns it gets cut off and the columns become non-custom. It goes back to excel letters instead of the custom names I have. Does anyone know of a work around for this?

65 0 / Create an account to join the discussion.
Ed D'Agostini
Follow / 11 Jan 2012 at 2:22am / Oracle
Latest post by on 30 Apr 2015 at 8:35pm

How to solve the FK's w/o Matching Indexes (or an unusable one...

Expand content
Hi gurus. Can you please help me understand and fix this issue? FK's w/o Matching Indexes (or an unusable one) Regards, Ed
Hi gurus.
Can you please help me understand and fix this issue?

FK's w/o Matching Indexes (or an unusable one)

Regards,
Ed
782 3 / Create an account to join the discussion.
Guest
Follow / 11 Jan 2012 at 2:22am / Oracle
Latest post by on 30 Apr 2015 at 1:27am

How to solve the FK's w/o Matching Indexes (or an unusable one...

Expand content
Message from: Ed D'Agostini Hi gurus. Can you please help me understand and fix this issue? FK's w/o Matching Indexes (or an unusable one) Regards, Ed _______________________________________...

Message from: Ed D'Agostini

Hi gurus. Can you please help me understand and fix this issue? FK's w/o
Matching Indexes (or an unusable one) Regards, Ed

_______________________________________

Historical Messages

Author: Edson D'Agostini
Date: Tue Jan 10 16:22:56 PST 2012
Hi gurus. Can you please help me understand and fix this issue? FK's w/o
Matching Indexes (or an unusable one) Regards, Ed
__
_______________________________________

842 2 / Create an account to join the discussion.
Guest
Follow / 17 May 2011 at 5:44pm / Oracle
Latest post by on 22 Apr 2015 at 5:27pm

Pass subsitution parameter in Automation Designer

Expand content
Message from: curtisbl_859 I have a need to pass a subsitution parameter during a script run from automation designer. Is there any way to pass the &OWNER parameter to the script without being...

Message from: curtisbl_859

I have a need to pass a subsitution parameter during a script run from
automation designer. Is there any way to pass the &OWNER parameter to the script
without being prompted?

Thanks in advance.
curtisbl

_______________________________________

Historical Messages

Author: curtisbl_859
Date: Tue May 17 07:44:05 PDT 2011
I have a need to pass a subsitution parameter during a script run from
automation designer. Is there any way to pass the &OWNER parameter to the script
without being prompted?

Thanks in advance.
curtisbl

__
_______________________________________

1144 5 / Create an account to join the discussion.
Sandeep Jagtap
Follow / 20 Apr 2015 at 7:04am / Oracle
Latest post by on 20 Apr 2015 at 2:56pm

Installation: Oracle database 11g R2 (64 Bit) via TOAD on my laptop

Expand content
Hi Guys, I would like to install Oracle database 11g R2 (64 Bit) via TOAD on my laptop. Request you people to guide me. Thank You. Regards Sandeep J

Hi Guys,

I would like to install Oracle database 11g R2 (64 Bit) via TOAD on my laptop.

Request you people to guide me.

Thank You.

Regards

Sandeep J

Unanswered 79 1 / Create an account to join the discussion.
joo hoo
Follow / 17 Apr 2015 at 5: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

64 0 / Create an account to join the discussion.
kiran.pamera
Follow / 14 Apr 2015 at 4: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

68 0 / Create an account to join the discussion.
cmatt
Follow / 3 Apr 2015 at 10: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

116 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 2: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

373 2 / Create an account to join the discussion.
gregb555
Follow / 1 Apr 2015 at 1: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 :)

100 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"
498 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"
88 0 / Create an account to join the discussion.