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 RepliesI 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].......
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",
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]....
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
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?
We can save Associative array in data base, if not why?.
View 3 Replies View RelatedI 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).