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
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
Jun 4, 2013
What is the real time uses for Associative array, nested table and varray ?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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