PL/SQL :: Nested For Loop In The Collections

Sep 5, 2013

collection1

============

SELECT o.object_id     
BULK COLLECT INTO l_obj_info       
FROM (SELECT     n.node_id, n.object_id                   
FROM nodes n             
START WITH n.node_id = 100             
CONNECT BY PRIOR n.node_id = n.parent_node_id) n            
INNER JOIN             objects o ON n.object_id = o.object_id      
WHERE o.object_type_id = 285;       

collection2

============

SELECT *     
BULK COLLECT INTO l_tab       
FROM ((SELECT     REGEXP_SUBSTR (i_l_text, '[^,]+', 1, LEVEL)                    
FROM DUAL              
CONNECT BY REGEXP_SUBSTR (i_l_text, '[^,]+', 1, LEVEL) IS NOT NULL));  
END;  

collection3

============ 

SELECT o.object_id              
BULK COLLECT INTO l_fin_tab                
FROM objects o JOIN ATTRIBUTES att ON o.object_id = att.object_id               
WHERE o.object_id = collection1.object_id                 
AND att.VALUE = collection2.val;                 

how to implement for loop in the collection3 to get the values from collection1 and collection2. i have tried in the below way 

CREATE OR REPLACE TYPE LIST_OF_ATTRIBUTES_TYPE AS TABLE OF varchar2(4000);/ 
CREATE OR REPLACE TYPE LIST_OF_OBJECT_IDS_TYPE AS TABLE OF number(9);/  
CREATE OR REPLACE FUNCTION f_get_objects_by_type_id (   i_object_type_id   IN   NUMBER,   i_l_text           IN   VARCHAR2,   i_scope_node_id         NUMBER)  

[Code]....

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Nested Loop Using Record Type?

Jul 29, 2013

I am using a record type to print some column in a same line.

Eg: I want to create index on some composite key columns. But i dont know how many columns are there. So want to use a loop which will count the number of column and then create the index like:

CREATE INDEX PRODUCT.XIF1AGMNT_PROD ON PRODUCT.AGMNT_PROD(LOAN_ID,LOAN_PROD_STRT_DT) TABLESPACE PRODUCT_INDEX;

View 5 Replies View Related

SQL & PL/SQL :: How To Loop Through Nested Cursors Based On Value From 1st Cursor

Jul 21, 2011

I loop through the 1st cursor (account_csr), while in the 1st csr loop, based on some conditions being true, I want to loop through a 2nd cursor (acctper_csr) but I only want to retrieve data/rows in the 2nd cursor where the account_id column in 1st cursor = account_id column in the 2nd cursor. This will enable me to pull all the account_periods for each account I loop through in the first cursor.

I have attempted several different ways and cannot make this work. Thought I could somehow define a variable and store the account_id from 1st cursor and use on the 'where' clause in the 2nd cursor definition. Have not been able to make this work successfully.

Following is the sample of my

--First cursor (accounts)
CURSOR account_csr is
SELECT *
FROM s_dev_xref1.account A
WHERE a.source = 1

[Code]...

View 7 Replies View Related

PL/SQL :: Insert Values Into A Nested Table Or Array With A Loop

Oct 8, 2013

How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below  

If  (you_belong_in_table)  then
variable := store_me_in_varray      /* OR */
variable := array_type(sector.sector_name)
i := i + 1;
end if;

/* Now we output our varray or table */
start loop
output(sector names one by one)

end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well. 

View 7 Replies View Related

Performance Tuning :: Cost Calculation For Nested Loop Join

Mar 27, 2012

Following is the query on TPC-H schema.

explain plan for select
count(*)
from
orders,
lineitem
where
o_orderkey= l_orderkey.

The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as "outer table cost + cardinality of outer table * inner table cost " fails here. I am not able to understand this.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: LINEITEM Alias: LINEITEM
#Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00
Column (#1): L_ORDERKEY(NUMBER)
AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000
[code]....

how the cost has been calculated. This does not follow the traditional nested loop cost formula as mentioned in the book.

View 7 Replies View Related

Performance Tuning :: Query With Nested Loop Takes 6 Hours To Complete

Jun 23, 2011

I'm joinging two tables event_types and tmp_acc tables.

event_types contains 2 Billion records
tmp_acc contains 20,000 records.

Resulting rows are about 300,000 records in event_types table end_t and account_obj_id0 are joined indexed

no indexs in tmp_acc.

When I run below query with nexted loop it takes 6 hrs to complete. But when I run with hash join even after 4 days it was still running. what is wrong with hash join here. Why it takes so long. I'm joining only 20000 rows. So I think there should be a way to get result rows quickly.

show parameters hash_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hash_area_size integer 2097152

explain plan for
select --+ parallel(e,6)
[code]....

View 21 Replies View Related

Performance Tuning :: How Oracle Optimizer Choose Joins (hash / Merge And Nested Loop Join)

Oct 18, 2012

I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writing any query.

View 2 Replies View Related

SQL & PL/SQL :: Use Of Simple_integer With Collections?

Feb 14, 2013

I'm exploring converting our use of pls_integer to simple_integer. My concern is that simple_integer cannot be null. It seems safe to use for count(*), since count(*) cannot return null. But I am not sure about its use in collection methods like i := collection.count or for loops like for i in 1..collection.count

View 5 Replies View Related

SQL & PL/SQL :: Bulk Collections And Current OF

Sep 2, 2011

I have a driver table from which I need to update another table while, at the same time, record the fact that I have processed each record on the driver table.

The driver table will contain around 3.5 million records, therefore I intended to handle this using some bulk collections, with a LIMIT option so that I don't hit any memory problems.

I would also prefer to commit in batches, or at least handle exceptions using the SAVE EXCEPTION clause. The problem is I seem to be running into an error when trying to make the update to the driver table (the commented out code). With this in, I get the error:

ORA-01410: invalid ROWID
ORA-06512: at "CUST_MAIL_UPDATE", line 217
ORA-06512: at line 38

Can the CURRENT OF not work with the FORALL? What is my best approach here? If I use a FOR LOOP I lose my SAVE EXCEPTIONS exception handling.

The Procedure is as follows:

-- declare some object structures to hold the retrieved data
TYPE driver_rec IS RECORD (
account_no ext_driver.account_no%TYPE,
update_action ext_driver.update_action%TYPE,
customers_rowid ext_driver.customers_rowid%TYPE);
TYPE driver_recs_tt IS TABLE OF driver_rec;
-- cursor to get the records from the driver table
[code].......

View 10 Replies View Related

Forms :: PL/SQL Collections In Datablock

Dec 29, 2010

I have one plsql table which is having 10,000 rows, i want to populate this values in a forms datablock, i have used the below query in pre-query of that block

declare
plsql_rec pkg.plsql_tab;
begin
plsql_rec := pkg.func;
set_block_property('blk_name', query_data_source_name, 'select e.ename, e.empno from table(plsql_rec) e');
end;

in the property of the block i set like below

Database Data Block = YES
Query Allowed = YES
Query Data Source Type = FROM clause query
Query Data Source Name = SELECT 1, 2 FROM DUAL

but while executing it says invalid identifier "plsql_rec",

View 1 Replies View Related

PL/SQL :: Remove Duplicate Values In Collections?

Mar 22, 2013

is it possible to remove duplicate values in plsql collections without using multistage operators ?

plsql collections output:
ID       NAME
-----     -------
001      A
001      A
002      B
003      C
004      D
005      E
005      E
005     E

expected output
ID       NAME
-----     -------
001      A
002      B
003      C
004      D
005      E

View 3 Replies View Related

PL/SQL :: Creating Collections Inside Package

Nov 23, 2012

I was trying to create types inside package like:

-create or replace package pack1 is
type udtable is table of integer index by binary_integer;
end;

-create or replace package BODY pack1 is
type udtable is table of integer index by binary_integer;
end;

Although this is not exact query what i have doubt in my mind. is it possible to create object types/collections inside/with packages?

View 6 Replies View Related

SQL & PL/SQL :: Collections - Save Associative Array In Database?

Jan 10, 2011

We can save Associative array in data base, if not why?.

View 3 Replies View Related

SQL & PL/SQL :: Collections Fetch - Pass List As Input Parameters To Procedure

May 11, 2012

I have a record type and table of records as follows

type rec is record
(
empid employee.empid%type,
ename employee.ename%type
);

type tab_rec is table of rec;

Suppose data from employee table is fetched into this collection

Can you pls clarify if we can refer to all the rows of empid in the above collection ?

something like tab_rec.empid without using the subscript for referring to the nth row

My requirement isto pass this list as input parameters to a procedure(PL/SQL).

View 3 Replies View Related

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

Nov 19, 2012

CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;

can i use forall instead of 'for loop ..end loop'

View 10 Replies View Related

SQL & PL/SQL :: Using Nested Table

Jan 9, 2013

a material related to collections with Examples and also how to declare table type values.

View 9 Replies View Related

SQL & PL/SQL :: Getting For Nested Cursor

Mar 1, 2012

DROP TABLE BatchEnrollment;
CREATE TABLE BatchEnrollment (
servicepointid VARCHAR(100),
curtailprogramid VARCHAR(100)
)

[Code]....

------------

Question I am getting error.

View 2 Replies View Related

Insert With Nested Select

Jul 24, 2007

I have here my SQL

-> INSERT into myTable (col1, col2, col3) values(SELECT table_seq.nextval from dual, 'value2', 'value3');

That's not working...

View 8 Replies View Related

Nested Tables By DBLINK

Feb 1, 2011

There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.

I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.

The data should be refreshed every day, the data changes moderately, there are more or less 500 records.

View 2 Replies View Related

SQL & PL/SQL :: Using RECORD As NESTED Into Another Program

Jan 23, 2011

When i am trying to use another RECORD as a NESTED RECORD into another program, it shows the below error: "PLS-00201: identifier 'TIMEREC' must be declared"

Record 1:
---------
declare
type timerec is record ( seconds smallint);
begin
dbms_output.put_line('Hello');
end;

Record 2:
---------
declare
type days is record (day number(5),time timerec);
begin
dbms_output.put_line('Hello');
end;

View 5 Replies View Related

SQL & PL/SQL :: NESTED CASE Statement

Jul 13, 2010

I'm working on a nested case statement, can't seem to get it right.We have a table that has injury_codes. What I'm trying to do is come with a nested case statement that will put the codes in a specific_cat, and based on the specific_Cat, assign a Generic_cat.

Example.
I have codes 11,12,13,14,15, 15, 17, 18, 19, 20, 21, 22
Codes 11, 12, 13 have a specific_cat "Head Injury"
Codes 14, 15, 16 have a specific_cat "Spinal Injury"
Codes 17, 18 have a specific_cat "Burns".

All 3 of these specific_cat come under the generic_cat "Trauma"

Codes 19, 20 have a specific_Cat "High Risk Pregancy"
Codes 21, 22 have a specific_cat "Premature Birth".

All 2 of these specific_cat come under the generic cat "OBGYN"...So my case stement should return :

CODESSPECIFIC_CATGeneric_Cat
11Head InjuryTrauma
12Head InjuryTrauma
13 Head InjuryTrauma
14Spinal InjuryTrauma
15Spinal InjuryTrauma
16Spinal InjuryTrauma
[code]...

just a small sample of codes, specific_Cat and generic_cat. I hundreds of these codes I need to categorize.

View 8 Replies View Related

SQL & PL/SQL :: What Is The Advantage Of Nested Table

May 17, 2011

what is the advantage of Nested table ?

View 1 Replies View Related

SQL & PL/SQL :: Set Operations With Nested Tables?

Aug 20, 2010

In the example below I believe I have created a Nested Table of PL/SQL type and have tried various references to get the SET operation to work, line containing MEMBER OF. Taking the example below from the oracle documentation I have two questions.

1) As I understand it I should be able to use SET operations on Nested tables of PL/SQL types, (I am not using the CREATE OR REPLACE DDL statement prior to the DECLARE block.).
Is this correct?
2) I am assuming that I have to reference the record, can I reference by its type / row instance or can I only retrieve the record like a Cursor Fetch solution, (which would defeat the purpose.).

Is this a SQL to PL/SQL <> PL/SQL to SQL problem?

download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm

Example 5-24 Comparing Nested Tables with Set Operator
SET serveroutput ON
DECLARE
answer BOOLEAN;

[code]...

View 1 Replies View Related

PL/SQL :: Multidimensional Nested Table

Sep 10, 2013

I am studing Multidimensional Nested table and have the below code:

 DECLARE  TYPE table_type1 IS TABLE OF INTEGER;  TYPE table_type2 IS TABLE OF TABLE_TYPE1; 
table_tab1 table_type1 := table_type1(); 
table_tab2 table_type2 := table_type2(table_tab1);
BEGIN  FOR i IN 1 .. 2  LOOP    table_tab2.extend;   
table_tab2(i) := table_type1();   

[Code]...

exception  when others then    dbms_output.put_line(sqlerrm);END; This code is working fine as of now.But,If i comment below code(table_tab2 is also extended latter):

table_tab2.extend;     
table_tab2(i) := table_type1();

then it gives me error 'Subscription Beyond count'. I would like to know why i need to extend table_tab2 twice?!

View 7 Replies View Related

Remove Constraints On Nested Table?

Jul 11, 2011

I want to truncate table partition but I'm getting error:

CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys

because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.

View 2 Replies View Related

Materialized View On Nested Table?

Dec 2, 2010

I try to do this:

CREATE MATERIALIZED VIEW MV_NESTED_DATA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH COMPLETE
ON DEMAND
START WITH ROUND(SYSDATE)
NEXT ROUND(SYSDATE) + 1
WITH ROWID
AS
select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;

where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE

And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint

Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?

View 4 Replies View Related

Using Nested-query To Achieve Info

Apr 19, 2010

For each keeper, show the number of cages cleaned by the keeper, show the average number of animals in the cages cleaned by the keeper and the total cost of the cages cleaned by the keeper."

The data table is shown on the picture.i75.photobucket. com /albums/i297/lovebipbo/SIT103.jpg.. I can learn and do some similar task myself

View 4 Replies View Related

Tuning Select With Nested Table?

Feb 18, 2011

I have created table with nested table:

CODECREATE OR REPLACE TYPE ADDR_T AS OBJECT (
ADDR1 VARCHAR2 (50),
ADDR2 VARCHAR2 (50)
);
CREATE OR REPLACE TYPE t_ADDr AS TABLE OF ADDR_T;

[code]....

I have added some records and created index on ID column. I want to get result of CODEselect id, p.addr1,p.addr2 from nested_table n,table(n.COL1) p where id=1

Explain plan for that is:

CODESELECT STATEMENT  ALL_ROWSCost: 8  Bytes: 231  Cardinality: 3              
4 HASH JOIN  Cost: 8  Bytes: 231  Cardinality: 3          
2 TABLE ACCESS BY INDEX ROWID TABLE SYS.NESTED_TABLE Cost: 2  Bytes: 13  Cardinality: 1      
1 INDEX RANGE SCAN INDEX SYS.FDSFAS Cost: 1  Cardinality: 1  
3 TABLE ACCESS FULL TABLE (NESTED) SYS.COL1_TAB Cost: 5  Bytes: 163,840  Cardinality: 2,560

How to avoid full table scan on nested table? Cardinality is sum of all records in nested column in all rows in main table, why?

View 2 Replies View Related

SQL & PL/SQL :: Comparing Nested Tables For Equality?

Jul 1, 2013

SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator. Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.

SCOTT@ORCL> create table empcopy3 as select * from emp;

Table created.

declare
type emp_ntt is table of emp%rowtype;
emp_nt1 emp_ntt;

[Code]....

But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.

View 3 Replies View Related

SQL & PL/SQL :: Hash Join And Nested Loops

Mar 16, 2011

What is the difference between Hash join and Nested Loops in pl / sql?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved