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


ADVERTISEMENT

Insert Values Into A Nested Table?

May 15, 2013

Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:

CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT
(
PONo NUMBER,
CUST_ref REF Customer_objtyp,
OrderDate DATE,
ShipDate DATE,

[code]...

The LineItemList_ntab is a nested table.

Here is the create code for LineItemList_ntabtyp:

CREATE TYPE LineItem_objtyp AS OBJECT (
LineItemNo NUMBER,
Stock_ref REF StockItem_objtyp,
Quantity NUMBER,
Discount NUMBER
)

[code]...

In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.

Here is some of the code I have tried to insert values with:

INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004,
NULL

[code]...

The first insert statement above produces the following error:

SQL Error: ORA-00933: SQL command not properly ended

The second insert statement above produces the following error:

SQL Error: ORA-00936: missing expression

successfully insert values into the LineItemList_ntab nested table?

View 6 Replies View Related

PL/SQL :: Uses And Advantages - Associative Array / Nested Table And Varray?

Jun 4, 2013

What is the real time uses for Associative array, nested table and varray ?

View 2 Replies View Related

PL/SQL :: How To Insert Null Record (some Column) In Table Using Loop

Jul 5, 2012

How to insert null record (for some columns) in table using loop.

sample data of x_tab

order_id order_name

231 xxx
123
345
111 vvvv

View 5 Replies View Related

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 View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

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

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

PL/SQL :: Insert Values Into EMP Table

Jul 26, 2013

I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP

(user_seq.nextval,(select empno from employees where empno in(....(empnumbers),  (select org_id from organizations where  org_name=' XXXXXXXXXX'),  (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') )) 

provide me the query for the above requirement.

View 40 Replies View Related

INSERT INTO Table (dateadded) VALUES (NOW())?

Apr 24, 2007

In MySQL I can insert the current timestamp by using MySQL's proprietary NOW() function.

Does Oracle have a similar function or method for adding a current timestamp with an INSERT?

View 1 Replies View Related

Forms :: Insert Values In A Table Through Ref

Aug 26, 2011

Table Structure:
Name of Table: info
Column: ID, Name, Cell,Cell1,Ref

i want to insert these values in a table through ref (key next item).
-----------------------------------------------------------
Key next Item Triger
DECLARE
A NUMBER;
B NUMBER;
BEGIN
SELECT :CELL,:CELL1 INTO A,B FROM DUAL;
[code].......
-------------------------------------------------------
but when i press enter(ref Key_next_item) it will return error:

FRM-40735: KEY_NEXT_ITEM RAISED UNHANLEDED EXCEPTION ERROR: ORA 40102

View 12 Replies View Related

SQL & PL/SQL :: Select Column Values Into Array

Sep 10, 2013

Is there any way in PL/SQL to select the values from all columns of a table record into an array?

For example:

C1|C2|C3
0 |1 |2

v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2

or

v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2

But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;

View 10 Replies View Related

PL/SQL :: Hold Values In A Associative Array?

May 6, 2013

I am providing the complete code and my exact requirement.

CREATE OR REPLACE PACKAGE INTERNAL_SCORING_RAM
IS
PROCEDURE TrendScoring_ram(pBUID       IN STAGING_ORDER_DATA.BUID%TYPE,
                         OrderNum    IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
                         ReturnValue OUT VARCHAR2);

[code]...

/In my code the procedure "trendscoring_ram" is calling "inserttrend_ram" procedure 70 times for different variable values. Instead of calling the "inserttrend_ram" procedure 70 times.

want to hold the values in a associative array , defining it in package and call that procedure only once.As below.

Inserttrend_ram(pBUID, OrderNum, Associativearray, Associativearray, Associativearray,Associativearray);

For that I have tried the following but it's not working.IN the package I have declared the associative array like this.

TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;

I have modified the inserttrend_ram as below.

PROCEDURE InsertTrend_ram(
                            P_TYPE_OTS_REC IN type_ots
                           )
        IS
        BEGIN
     
[code]...

View 1 Replies View Related

SQL & PL/SQL :: Insert Into Oracle Table Of UNIX Script Values

Dec 24, 2012

I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.

E.g. : insert into tbl_daily_status values(STAT,ENDTIME);

#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done

View 2 Replies View Related

Using Array Values In IN Clause Of Select Query?

Sep 9, 2008

I have some issues in passing array values to IN clause.

I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query

cust_array is the Array
search_id VARCHAR2(1000);
search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;

trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'

and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)

but the query does not returns any result

When I try to hardcode the values in the query as below, its returing 4 rows

select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')

How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause

View 13 Replies View Related

SQL & PL/SQL :: How To Hold Multiple Rows Values Using Array

Jun 1, 2010

How to hold the multiple rows values using array? And I have to pass this values to some other procedure.

Ex: SQL> select ename from emp;

ENAME
----------------------
Vetrivel
Dr.Venkat
Vinoth
Sudhakar
Sivaganesh
Senthil

View 7 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

Forms :: Hierarchical Tree - Retrieve Values And Insert Into Table

Apr 19, 2010

My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.

I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.

View 2 Replies View Related

SQL & PL/SQL :: Insert Values Fetched From Object Type Into Empty Table?

May 4, 2012

I need to insert values fetched from an object type into an empty table.

-- >> table creation:

create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));

-- >> created an object_type

SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created

--- >> create the package

SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /

Package created

SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,

[code]....

-- >> a block to execute the above package:

SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /

But I am getting the below error when executing the package:

ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:

[code]....

View 6 Replies View Related

Precompilers, OCI & OCCI :: Insert Multiple Rows Using Array Bind Feature?

Aug 18, 2010

I am trying to insert multiple rows using array bind feature.

The following is working fine & inserting rows as expected.

char values[3][2] = { "1", "2", "3" };
ub2 lenp[3];
lenp[0] = lenp[1] = lenp[2] = 2;

[Code]...

OCIStmtExecute() is failing with the following error

Error - ORA-01480: trailing null missing from STR bind value

View 1 Replies View Related

SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation

Feb 22, 2010

I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :

CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER

I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......

I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........

The code is given below:

CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Search In Nested Tables And Insert Result Into New?

Mar 15, 2011

How can I search in Nested Tables ex: (pr_travel_date_range,pr_bo_arr) using the SQL below and insert the result into a new Nested Table: ex:g_splited_range_arr.

Here are the DDL and DML SQLs;*Don't worry about the NUMBER( 8 )*

CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) );
CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE;
DECLARE
g_splited_range_arr DATE_RANGE_ARR := DATE_RANGE_ARR( );

[code]...

Or can I create a VIEW with parameters of Nested Tables in it so I can simply call

SELECT *
BULK COLLECT INTO g_splited_range_arr
FROM view_split_date(g_travel_range,g_bo_arr);

View 7 Replies View Related

SQL & PL/SQL :: Using Correlated Values In Nested Subquery

Sep 1, 2010

select rtrim(xmlagg(xmlelement(e, table_name||',')).extract('//text()'),',')
from (
select distinct table_name from tr_products tr join all_instruments v
on tr.PROD_COA_ID = v.PROD_COA_ID
where tr.LEVEL_06_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_05_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_04_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_03_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_02_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_01_VALUE like t1.COLUMN_VALUE||'%')

This is an excerpt of a code of a huge query, I didn't want to write it all here because there is no point in doing that since this is the part of the query that I'm actually having problem with, and I think you will be able to understand my question just by looking at this piece of code.

Anyway, this here is actually a subquery in a select-clause of a main query, and apparently it has a (nested) subquery of its own (as seen in the code). I want to reach some values ("COLUMN_VALUE") that are correlated to the parent query (t1 is alias of a table in the from-clause of the main query), and as you can see, I'm trying to reach it from this level 2 subquery. As I have recently found out, Oracle does not allow this, it only tollerates correlation down to level 1.

Here is a scheme of my query:

select something1,
(select something2 from
(select something3 from some_table2 t2
where t1.value = t2.value))
from some_table t1

can this be overridden somehow, is there a workaround for this limitation?

View 3 Replies View Related

SQL & PL/SQL :: 2 Insert Statements For 1 For Loop

Oct 30, 2013

Can we have 2 insert statements for 1 for loop? how can we have 2 insert statements into 1 for loop!

FORALL j IN stu.FIRST .. stu.LAST
insert into CHASSISM_test
(make, stu, invoiceno, gross_tot, discount)
values
(make(j), stu(j), INVOICEno(j), GrossTot(j), discount(j));

[Code]...

View 10 Replies View Related

Forms :: Loop Insert Empty Row?

Jul 3, 2010

there this is my insert loop but the problem is there is row added more than the normal count ..

DECLARE
var_record_count NUMBER;
var_p_request NUMBER;

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Replace Values Within Loop

Jun 26, 2012

I have one emp table, in which i have sal, ename,empno etc columns. Now i am trying to replace each digit of salary with '*'. like if salary is 10000(it has five digit) then it will be replace by *****, but unable to do so. I don't how to achieve this, can we use translate or replace function in this.

Well i have achieved this by using case statement but where hardcoding is done...see the case below:-

select substr(ename,1,8),case when length(sal)=2
then '**'
when length(sal)=3
then '***'
when length(sal) = 4
then'****'
when length(sal)=5 then

[code]....

View 27 Replies View Related

SQL & PL/SQL :: How To Use Ref Cursor Values In For Loop

Nov 19, 2011

I am having a scenario where i have a ref cursor opening and fetching though dynamic sql query. And those values which i get from ref cursor i want to use them for other parameter cursor in for loop.

for example

PROCEDURE script (
p_account_no IN VARCHAR2,
p_from_date IN DATE,
p_to_date IN DATE,
p_subledger_code IN VARCHAR2,
p_posted IN VARCHAR2,
v_alloc_unalloc IN OUT alloc_unalloc, -- ref cursor declared in package specification.
[code]..........

View 12 Replies View Related







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