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


ADVERTISEMENT

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 :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

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

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

Way To Bulk Delete

May 16, 2011

what is the error in this procedure.I am getting error where s.msg_id = Tbl_Repo_Salo (i.msg_id));

Create or replace Procedure Proc_Stg_Clear(P_Err_Code OUT NOCOPY number,
P_err_msg OUT NOCOPY varchar2) as
v_count number;
Type Repo_Salo is table of Tbl_Tml_msg_Salo_Stg%ROWTYPE;
Type Repo_Smb is table of Tbl_Tml_msg_SMB_Stg%ROWTYPE;

[code]...

View 9 Replies View Related

SQL & PL/SQL :: What Is Bulk COllect

Jan 30, 2011

What is Bulk COllect and How it can be use

View 2 Replies View Related

Error During BULK Insert?

Sep 28, 2011

I made two runs for bulk insertion

In first run, 16,36,897 were inserted successfully in around 38 seconds.But in second run, 54,62,952 records had to be inserted, but process failed after 708 seconds with following error :

Error report:
ORA-04030: out of process memory when trying to allocate 980248 bytes (PLS non-lib hp,DARWIN)
ORA-06512: at line 21
04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)"
*Cause: Operating system process private memory has been exhausted
*Action:

Here is my code snippet :
.......
FORALL i in products_tab.first .. products_tab.last
INSERT INTO tab1 VALUES products_tab(i);
COMMIT;
.........

I think that there should not have been any problem in getting it completed successfully.

View 4 Replies View Related

Bulk Update In Oracle

Mar 28, 2012

I have performance issue with the bulk update. I have 2 tables one with 21 millions of data and the other table with 2 millions of data.here the requirement is to update the table which is having 21 millions with the other tables data(which is having 2 million records) based on some matching criteria.

The procedure is taking 9 hours to update the table(which is having 21 millions data). I have created required indexes on table also.But the performance is not good.

Here my procedure:

CREATE OR REPLACE PROCEDURE AHM_GKPR.CLAIMS_WITHOUT_PARTITIONS
IS
TYPE T_PL_CO IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
L_PL_CO T_PL_CO;
TYPE T_PL_CD IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
L_PL_CD T_PL_CD;
TYPE T_PL_NB IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
L_PL_NB T_PL_NB;
TYPE T_SE_CD IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
L_SE_CD T_SE_CD;
TYPE T_BIR_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER;
[code].........

View 5 Replies View Related

SQL & PL/SQL :: BLOB Bulk Upload

May 11, 2011

Is it possible to Bulk Upload file into Oracle table's BLOB column using Pl/Sql code.

The process I know of dose 1 file @ a time as of now.

I have more than 10000+ files to upload and this will be one off process.

View 3 Replies View Related

SQL & PL/SQL :: Bulk Insert Into File

Dec 14, 2012

We have requirement to create INSERT SCRIPT from the table having thousands of records and load that into flat file. if there are any better option other than using UTL_FILE package which process record by record.

View 5 Replies View Related

SQL & PL/SQL :: Bulk Collect Of Procedure

Apr 21, 2011

I am finishing using procedure migration from HP-ux server to Oracle linux server. I am currently testing migration by procedure, and there's time limit, so I like to use bulk collect and other faster way to do that, however I could convert normal procedure to bulk procedure.

here's my script of old table, new table, normal procedure, and my new procedure.which parts can be corrected to use bulk collect or bulk insert?

CREATE TABLE ORAASFS.NATELIST
(
MINNO VARCHAR2(12 BYTE),
PHONENO VARCHAR2(12 BYTE)
)
CREATE TABLE ORAASFS.TM_SFS_USR_NATE_LST_01
(
[code]......

View 1 Replies View Related

SQL & PL/SQL :: Bulk And Reference Cursor

Dec 24, 2010

I simulated a sample procedure for my requirement.When i try to compile procedure it throws error 'cannot mix single and multiple rows ( bulk) into'...I have to pass a table as dynamic in a cursor ,collect the data and process it using and forall.

create table dynamic (emp_name varchar2(20),emp_id varchar2(20), tel_no varchar2(20);
create table dynamic_1 (emp_name_1 varchar2(20),emp_id_1 varchar2(20), tel_no_1 varchar2(20);

insert into dynamic values ('Mike','1','123456');
insert into dynamic values ('Nike','2','1234567');

create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
[code]....

View 21 Replies View Related

SQL & PL/SQL :: Bulk Collect Into Varray

Mar 7, 2011

I'm getting error message

PLS-00386: type mismatch found at 'RECORD_VARRAY' between FETCH cursor and INTO variables

while executing the below code.

PROCEDURE MAIN_BULK_COLLECT(P_STARTDATE IN TIMESTAMP DEFAULT NULL,
P_ENDDATE IN TIMESTAMP DEFAULT NULL,
P_ROW_COUNT IN NUMBER DEFAULT 1000,
O_RECORD_VARRAY OUT NOCOPY SSAM_VARRAY_TYPE,
P_ERROR OUT VARCHAR2) AS
[code]....

I'm able to run the program successfully using FOR LOOP instead of BULK COLLECT but wish to run using bulk collect.

View 6 Replies View Related

SQL & PL/SQL :: Query On Bulk Collect

Feb 24, 2011

Okay I am not asking for detailed solution here but I have quick query.

One of the procedure have this cursor query returning say 10 columns and have declared an collection of that cursor type.

Now the cursor is bulk fetched in the collection.

By any means it is possible to happen that bulk collection will scramble column values ?

The cursor seem to return expected output. But when table insert happens using the collection there I see values are mismatched.

I was wondering if bulk collect can be an issue ?

View 4 Replies View Related

SQL & PL/SQL :: Bulk Select And Insert

Jan 7, 2011

We are doing a bulk select and insert (10,000 rows processed in each transaction). If one record fails, the entire transaction is rolled out. We need to fix this and re-run. the process is repeated unless all errors are fixed.

How to capture all errors in a single run ?

View 3 Replies View Related

SQL & PL/SQL :: Complex Bulk Collection?

Mar 25, 2011

I have the following questions you can create a bulk collect in which he has 3 fields and one of them is a type collect failing index or other bulk collect?.If so, how would the procedure for when to insert the first record sub ​​fill the bulk collect. being for example something like this:

-----------------------------------------------------------------
index | codigo | nombre | telefono
| | |-----------------------
| index | telefono
-----------------------------------------------------------------
| | | |

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Bulk Collect In Load

Mar 5, 2012

How to resolve this issue?

CREATE OR REPLACE PROCEDURE fast_proc
IS
TYPE ARRAY IS TABLE OF mkt_total_lvl_indx_dly_stg%ROWTYPE;
l_data ARRAY;
cursor C IS
SELECT *
[code]..........

show error

Error code

PROCEDURE fast_proc compiled
Warning: execution completed with warning
17/47 PL/SQL: ORA-03001: unimplemented feature
17/5 PL/SQL: SQL Statement ignored

View 13 Replies View Related

Bulk Collect With DBLink?

Feb 23, 2012

create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT
IS
/*
Criteria to be followed to Order Archival

* Order Status should be 'Cancelled' or 'Complete'
* Order Closed date should be 6 months before
*
-- main Cursor to spool the Orders to be archived based on criteria

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Bulk Update - Two Tables

Dec 5, 2011

I am trying to write an update using two tables, one table contains the list of updates to be applied, and the second contains the old data. I have a working solution but I was wondering if there might be some other better way to accomplish this task.

CREATE TABLE foo
(
foo_id NUMBER,
foo_val NUMBER
);
CREATE TABLE foo_change
(
foo_id NUMBER,
foo_val NUMBER
[code]...

The update works, but I thought there may be a better way I just don't know about.

View 2 Replies View Related

SQL & PL/SQL :: Procedure Bulk Collect

Nov 26, 2010

identify what type of error is present in below procedure?

create or replace procedure test_bulk_load_type
as
type c1_owner is table of bulk_load_all_object.owner%type
index by binary_interger;
v_owner c1_owner;

[Code]....

View 7 Replies View Related

Type Objects And Bulk Loads

Apr 19, 2013

I am trying for a solution to automate a process of bulk inserts. I have attached the script file.

Attached File(s)

blk_test_script.txt ( 1.45K )
Number of downloads: 6

View 7 Replies View Related

SQL & PL/SQL :: Bulk Insert In Index Table

Jul 8, 2013

why bulk insert is not possible in a table which has index?

View 9 Replies View Related

Insert Using Append / Bulk Correct?

Aug 3, 2011

I want to know which is the most efficient insert method among the followiing

1)using the hint append
or
2)bulk collect. Preferably I'd like to know which method of inserting for say 2-5 millions rows is the best.

View 6 Replies View Related

SQL & PL/SQL :: Bulk Delete With Total Count

Feb 24, 2009

I have a table A which has million records, and one of the column is a date. All records prior to 01-FEB-2009 should be deleted.

I cannot run the below code considering the amount of records or rows in the table A.

Begin
Delete from A where trunc(date_column) < '01-FEB-2009';
commit;
End;

where we can delete the rows prior to 01-FEB-2009 and also have a total count of how many were deleted?

View 14 Replies View Related







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