SQL & PL/SQL :: Nested Table - Update Activity For Data Correction?
Mar 17, 2012
I've to do a update activity for data correction. There are about 1 crore records involved in this. I want to keep a log of the activity at least Sales No. so that I can validate my data correction. Main cursor is involved for this. The problem is if I insert each record for sales number in the loop then process will take about double time. So i decided to create a nested table and insert after each session finishes.
I'm talking about session here is. The table I'm updation is partition table. So I can have many sessions and each session updating different table partition. This way there is no lock, better performance. Server has 35 processors so I can at least have 25 sessiions.
I could have used varray for this. But I want to learn nested table also. Problem here is not to take different way to complete this activity but to learn why I'm facing this issue.
CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT
(
SALES_NO VARCHAR2(24)
,sALES_SEQ NUMBER(4)
) ;
I want to update nested table record based on index suppose i want to update 3rd number of hobby for name2 employee
i have written the below query
SCOTT@orcl_11gR2>update Table(select hobby from emp 2 where empno= 2) e 3 set value(e)='new_value' where to_char(value(e)) = (select 4 to_char(tab1_element) 5 from (select rownum rn,
[Code]...
2 rows updated.
but the above query is updating 2 records but my requirement is it should update only third record
How can we update nested table based on index number
I want to check whether language is already there in database or not.
i have written the below query
select * from emp where language_known =nesttype('english','hindi');
i am getting the below error
SQL Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type 22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type" *Cause: Comparison of nested table or VARRAY or LOB attributes of an object type was attempted in the absence of a MAP or ORDER method. *Action: define a MAP or ORDER method for the object type.
I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.
CREATE OR REPLACE type sam as OBJECT ( v1 NUMBER, v2 VARCHAR2(20 CHAR) );
---Nested Table--- create or replace type t_sam as table of sam; --Inserting data---- insert into table(t_sam) values(sam(10,'Dsouza')); Error Message: [code]........
I have some problem when i try to export data from a table which contains a nested table, using toad.
When toad generate the file, in the column corresponding to the nested table, toad just write a (DATASET) instead of the data contained in the nested table
Here is an example: INSERT INTO SSD_REV_S ( REV_ID, REV_TAB, REV_TS, REV_USER, REV_LOG ) VALUES ( TO_Date( '07/30/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), (DATASET), TO_TIMESTAMP('4/3/2009 11:20:51.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'operator', 'Add EVENT');
REV_TAB is my nested table
way to export data from a table which contain a nested table, as a list of insert statement, so i can move the data to a different database schema? I can also use different client tool.
I have a table named employee with 10 records. Empid is the primary key and empsal contains salary of the employees. I created backup of this table and name of backup table is empbackup.
Now empsal was updated to empsal*10 in employee table and 10 more records were also inserted in employee table. Now I want to restore the empsal column for 10 employees from empbackup(as initially there were only 10 employees in employee table when empbackup was created) table how can I do that in PL/SQL or Oracle?
I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.
user wants the following layout:student Advisor ID# name degree major Concentration ID# name The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).
The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.
i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.
how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept
ITEMNUM STORELOC lastyear currentyear AM1324 AM1 need sum(quantity) here need sum(quantity) AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values: GLOBETROTTER: agent has visited five or more different locations in the course of his missions ROVER: agent has visited between one and four locations SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are agents table: agent_id, location_id targets table: target_id, location_id missions_agents table: agent_id, mission_id missions_targets: target_id, mission_id missions table: mission_id, location_id
My code is
DECLARE status AGENTS.TRAVEL_STATUS%TYPE; contact AGENTS.CONTACTS%TYPE; CURSOR loc_cur IS SELECT a.AGENT_ID id,
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
Is Oracle will support Multi value storage ? In what way we can use Nested table? In real time application where we can use nested table . What is the usage of nested table in real time application.
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):
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.
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?
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?
I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.
-- PLS-00642: local collection not allowed in SQL statements
-- PL/SQl: ORA-00902: invalid datatype
The error message are also noted below in the code on the line that fails.
A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.
IMPORTANT: The code is running in an enviroment that does not have permission to create.
need to use Extend() in nested tables in Oracle? What could be the problem if I do not use this method in my code?
I have a nested collection type (TABLE OF VARCHAR2(32)) declared in my package. My stored procedure takes the TABLE type as input and inserts that data into a database table. I see that my code works fine without using EXTEND method.
I am getting compilation error when create nested table type depend on table structure
SQL> CREATE OR REPLACE TYPE typ$nttb$1 IS TABLE OF emp%rowtype 2 ; 3 /
Warning: Type created with compilation errors.
SQL> show error Errors for TYPE TYP$NTTB$1:
LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PL/SQL: Compilation unit analysis terminated 1/29 PLS-00329: schema-level type has illegal reference to SCOTT.EMP SQL>
declare cursor c is select employee_id from employees; type nst_type is table of employees.employee_id%type; emp nst_type; begin open C; loop exit when C%notfound; fetch c bulk collect into emp; end loop; close c; end;
Above is the sample code, and now i want to pass 'emp' as an input parameter to the procedure.
How can I do that, as emp is of nst_type type and I do not know how my procedure will recognize the datatype...
I have a query which returns a nested table as a result of split function. I used any method to unnest the data. But I couldn't. I try it with this query.Note: To run the following query I attached everything needed.
-- This query gives very strange results. SELECT * FROM ( SELECT
I have an issue on selecting data from a nested table owned by other user.
I have given select access on stc_irb_usr.stc_loy_settlebillpay_map_main to anthr user adminofs.
But when I try to query, it says insufficient privileges. adminofs is able to select other columns which are not nested.
I have given "grant execute on STC_IRB_USR.STC_BILLPAY_MAP_TAB_TYPE to adminofs;" but it did not
select * from stc_irb_usr.stc_loy_settlebillpay_map_main where rownum<3;
ERROR: OCI-21700: object does not exist or is marked for delete
SQL> desc stc_irb_usr.stc_loy_settlebillpay_map_main Name Null? Type ----------------------------------------- -------- ---------------------------- BATCH_NUM NOT NULL NUMBER ACCOUNT_NUM NOT NULL VARCHAR2(40) PAY_BILL_SEQ_MAP STC_IRB_USR.STC_BILLPAY_MAP_
[Code]....
Name Null? Type ----------------------------------------- -------- ---------------------------- ALLOCATING_CREDIT_SEQ NUMBER ALLOCATION_ENTITY_SEQ NUMBER ALLOCATING_CREDIT_DATE DATE ALLOCATING_CREDIT_TYPE VARCHAR2(20) ALLOCATION_ENTITY_DATE DATE
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?
I have a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.
CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10)) / create type t_msg_details ia table of t_example /CREATE TABLE table_nested_sample (msg_codes NUMBER(10), language_id NUMBER(10),
[Code]...
How to call this procedure I want to insert data like this
I am trying to exp/imp database schema from one environmet to other in oracle 11g. I have a couple nested tables (tables with types datatype). Here are the errors I am getting:
ORA-31693: Table data object "IPAM"."BACKEND_POLICY_TYPES" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-00600: internal error code, arguments: [kpudpxcs_getCol_ref_4], [Imgver=1 InputStrmVer=9 Name=POLICY], [], [], [], [], [], [], [], [], [], []
ORA-31693: Table data object "IPAM"."LB_POLICIES" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-00600: internal error code, arguments: [kpudpxcs_getCol_ref_4], [Imgver=1 InputStrmVer=9 Name=POLICY], [], [], [], [], [], [], [], [], [], []
