Right Parameter Types Of ODCIIndexInsert In Case Of Creating Index-type WITH ARRAY DML?

Sep 17, 2013

During developing Domain Index for Oracle 11.2.0.1.0 (problem also appears in 12с) i was faced with misunderstanding of parameter types of function ODCIIndexInsert in case of creating indextype WITH ARRAY DML option.According to Oracle documentation [URL}...

In case of WITH ARRAY DML option Oracle will invoke ODCIIndexInsert with following signature

FUNCTION ODCIIndexInsert(
ia ODCIIndexInfo,
ridlist ODCIRidList,
newvallist varray_of_column_type,
env ODCIEnv)
RETURN NUMBER

In my case indexed column has datatype NUMBER so i defined varray_of_column_type as SYS.ODCINumberList STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER

Indextype was created as

CREATE INDEXTYPE test_index_type
FOR
test_eq(number, number)
USING index_methods
WITH ARRAY DML(number, sys.ODCINumberList)

[code]...

(problem occurs in all cases)
CREATE TABLE test_table (id NUMBER (19,0));
CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;

When attempting to insert data in the table insert into test_table values (1);

oracle raise exception

CODEError starting at line 53 in command:
insert into test_table values (1)
Error at Command Line:53 Column:1
Error report:

[code]...

So my question is.Is it normal behavior of oracle (according to documentation)?What is correct signature of ODCIIndexInsert function in case of INDEXTYPE creation with 'WITH ARRAY DML' option and fact that indexed column has NUMBER datatype?

By the way if i define indextype without 'WITH ARRAY DML' option signature is clear, and working. But this approach doesn't satisfies our performance needs.Also if i define index type with option 'WITH ARRAY DML WITHOUT COLUMN DATA' and use signature static function ODCIIndexInsert(ia sys.odciindexinfo, ridlist sys.odciridlist, env sys.ODCIEnv) return number

Everything works too. But this approach doesn't satisfies our business needs.Is it a way to define ODCIIndexInsert parameter types (in case of indexing number column) so that batch inserting works according to documentation ?

FUNCTION ODCIIndexInsert(
ia ODCIIndexInfo,
ridlist ODCIRidList,
newvallist varray_of_column_type,
env ODCIEnv)

I am attaching full sql script to recreate environment and reproduce the problem.Type definition:

CREATE OR REPLACE TYPE index_methods AS OBJECT
(
step number,
STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER,
STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv)

[code]...

View 1 Replies


ADVERTISEMENT

PL/SQL :: CASE Statement With Oracle Table Types And EXISTS?

Aug 14, 2012

I have been trying to use case statements with oracle table type by really not sure how to go about it. I know it might be simple but it been giving me hard time.

Here is my Cursor:

CURSOR c_chk_style IS
      SELECT DISTINCT 1
        FROM TABLE(CAST(I_message.ExtOfXOrderDesc_TBL(1).ExtOfXOrderSkuDesc_TBL AS "RIB_ExtOfXOrderSkuDesc_TBL")) item_diff,
   
[code]...

Now i know that the table type "RIB_ExtOfXOrderSkuDesc_TBL" will be always populated but the table type "RIB_ExtOfXOrderPackDesc_TBL" may not be populate and can be null. So i want to run the exists against the "RIB_ExtOfXOrderPackDesc_TBL" aliased pack_diff only if it is populated. If its null i dont want to run the exists clause.

View 15 Replies View Related

SQL & PL/SQL :: How Many Types Of Index Are There In Oracle

Nov 11, 2011

I read about many indexes as B tree index, function based index,Bitmap Index etc.how many types of Index are there in Oracle and what are they? wat are the categories and sub category? when to use what?

View 6 Replies View Related

SQL & PL/SQL :: Types Of Oracle Index

Sep 26, 2011

Types of oracle index in detail. consider this situation, if a select query without any where clause takes much time to get data,then we will create index for that table.

Now again run the same select query. this time, whether the query takes much time again or will it work fast? whether the index is used for a select query execution or not?

How we can confirm that whether the oracle using the index normally?

View 2 Replies View Related

SQL & PL/SQL :: ARRAY As Parameter In Procedure?

Nov 1, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have this in one of the packages WCL_LIB:
TYPE vc2_255_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

and i use it in one of the procedures as
PROCEDURE "WCL_EVENTS"
(p_event_id IN NUMBER,
p_event_arr IN Wcl_Lib.vc2_255_arr,
p_model IN VARCHAR2 DEFAULT NULL,
p_model_code IN VARCHAR2 DEFAULT NULL

but the calling procedure doesnt have any array.... Can I declare something like in the procedure to be called
p_event_arr IN Wcl_Lib.vc2_255_arr DEFAULT NULL,

I tried, but doesnt seem to work? so how to call the procedure, which has a array as mandatory, but calling one doesnt have any?

View 5 Replies View Related

PL/SQL :: Procedure With Array IN Parameter

Jul 4, 2012

I have requirement to create a procedure that accepts an Array as IN parameter , Query a table using this array and return the result as refcursor . I tried to get it as below but not working .

CREATE OR REPLACE TYPE ARR_ID AS TABLE OF VARCHAR2(20);
/
CREATE OR REPLACE
PROCEDURE TEST_ARRAY
(P_ARR_ID IN ARR_ID,
P_CUR_OUT OUT SYS_REFCURSOR)

AS

BEGIN

OPEN P_CUR_OUT FOR
SELECT * FROM EMPLOYEE WHERE EMP_ID IN (SELECT * FROM TABLE(P_ARR_ID ));
END;
/

View 3 Replies View Related

PL/SQL :: Get Distinct Value From Array Type Of Variable?

May 14, 2013

i have written one procedure which is giving an error PL/SQL:ORA-00902: invalid data type, how to get the distinct value.

there is one table xyz and this table having so many attributes and file_data one of the attribute which is having CLOB data type. I have to find out distinct value in each record of file_data.

suppose for example

TABLE XYZ

col1 col2 file_data
A     B      <CLOB VALUE>------------------------ this CLOB VALUE containing duplicate record
V     X      <CLOB VALUE>------------------------- this CLOB VALUE also containing duplicate record

create or replace procedure test_dealer_upload
IS
t_out_file UTL_FILE.file_type;
t_buffer VARCHAR2(32767);

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: Index Outside Bounds Of Array?

Aug 27, 2012

i have a problem when i execute this query and it takes more than 32 minutes and after that connection get hanged in toad 9.2.7 and oracle version 11.2.1.0 with this error- index was outside the bounds of array.

SELECT t.ID AS "serial#",
SUM (CASE
WHEN (SELECT MAX (g.test_date)
FROM label1 a LEFT JOIN label2 g ON g.ID = a.ID
) IS NOT NULL

[code]...

i want to know the cause of above error and what would be the solution.

View 7 Replies View Related

SQL & PL/SQL :: Removing Array Index

Sep 10, 2010

TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );

TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy

So if I did something like this;

l_cash_records CashRecordTable;

-- say for example that l_cash_rec/l_cash_rec2 has been defined..

l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2

How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?

View 3 Replies View Related

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

Jul 14, 2011

how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...

View 7 Replies View Related

JDeveloper, Java & XML :: Calling Function With (array Of) Complex Data Type

Oct 20, 2011

I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.

a) basic definitions (work and are ok)

CREATE OR REPLACE TYPE eventtype AS OBJECT
(
relevance NUMBER,
bid1 NUMBER,tid1 NUMBER,prob1 NUMBER,
bid2 NUMBER,tid2 NUMBER,prob2 NUMBER,
bid3 NUMBER,tid3 NUMBER,prob3 NUMBER,
bid4 NUMBER,tid4 NUMBER,prob4 NUMBER,
bid5 NUMBER,tid5 NUMBER,prob5 NUMBER,
bid6 NUMBER,tid6 NUMBER,prob6 NUMBER
);
CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype;
/

b) The following call works (but is not exactly that what I want):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal';
/
SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;

c) The following call doesn't work (but should have the intended semantics):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal';
/
SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;

d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.

The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.

View 1 Replies View Related

Data Type Consistency CASE / Decode?

Apr 6, 2012

Data Type Consistency CASE and Decode

CASE expects data type consistency, DECODE not expecting.

Obviously both functions handling data types are different let it be

SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;

RESULT
---
2
SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;

RESULT
2
SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

know cause of error here????I mean " every time case exp checking data type consistency"

my thought is

I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug!

I want to know how oracle handles here ???? i mean 3rd query.Purely i am testing this function with dual(dummy) table...
obviously, no possibilities for different data type.next one i am not sure about Oracle compares int variables to int variables, char variables to char variables,

I think so .... that's why oracle throws error. am i right ??ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

see this query

SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

// clearly i am stating what's error on 3rd line ?????Even if I refer reference books some concepts are blind to understand.

View 2 Replies View Related

SQL & PL/SQL :: Data Type Consistency CASE And Decode

Apr 5, 2012

Data Type Consistency CASE and Decode...CASE expects data type consistency, DECODE not expecting.Obviously both functions handling data types are different..let it be

SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;
[color=red]RESULT
---
2
SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;
[code]...

I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug! Oracle compares int variables to int variables, char variables to char variables, If any discrepancy between the two data types, then the query will fail but here not different data types.

Even if I refer reference books some concepts are blind to understand.

View 5 Replies View Related

How To Use User Defined Type While Creating New Table

Oct 13, 2009

That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));

After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);

I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Procedure Name With Record Type I/p Parameter?

Jun 22, 2012

I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..

CREATE OR REPLACE PACKAGE med_order_pkg AS
TYPE deid_med_order_typ IS RECORD(....)
L_deid_med_order_typ deid_med_order_typ;
PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ);
END;

[code]....

From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?

View 3 Replies View Related

SQL & PL/SQL :: Index Of Table Type Object

Apr 4, 2012

I have a table type object loaded (tto) with data and am using it as :

SELECT tto.B, tto.C
FROM TABLE(tto)
WHERE tto.A = <some value>;

This is working fine. BUT, can I also select the index of each element too along with other fields of each element ?

View 16 Replies View Related

SQL & PL/SQL :: Dynamic Calling Function With Type Record Parameter?

Jul 23, 2010

I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.

My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),

[code].....

View 5 Replies View Related

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

May 9, 2012

I am fairly new to oracle, here's what I am doing.

Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));

[Code]....

I need to test the procedure how do I pass the inputs for the type object csc_info?

View 2 Replies View Related

JDeveloper, Java & XML :: XML Type Parameter Contains Multiple Records

Jun 22, 2012

I need an example of oracle stored procedure, which should xmltype input parameter. xml type parameter contains multiple records and I need to update them in the database.

View 2 Replies View Related

PL/SQL :: Input Parameter Of Table Type And Null Assigned To It

Jul 17, 2012

I have few questions regarding PL/SQL tables

1) If I don't specify the "INDEX BY" clause, it is indexed by PLS_INTEGER by default, right?

2) Consider this package specification

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);


END Testxyz;So I created a table (I hope it is defaultly indexed by pls_integer data type) and I am passing it as a parameter to a procedure. Because I want this parameter to be optional I am assigning null into it.

Now I change the definition of the table to:

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
[code]....

3) Because I need this parameter to be optional, I use the first declaration:

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;

Now I create an anonymous block and want to assign something into the table

DECLARE
   vtab_Numbers TESTXYZ.tab_Numbers;
BEGIN
vtab_Numbers(1) := 5;
END;
/When trying to run this, I got:
06531. 00000 - "Reference to uninitialized collection"

Is it possible to have input parameter of PL/SQL table type and have defaultly null assigned to it?

View 5 Replies View Related

Creating Index On Hashed Column

Sep 23, 2010

I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.

Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?

View 4 Replies View Related

Creating A Multi Column Index

Jan 24, 2012

I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.

the way to create multi_column indexes on the table?

What are the rule to define the best-position of a column in an index?

index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?

View 4 Replies View Related

SQL & PL/SQL :: Create Primary Key Without Creating Index?

Jul 12, 2012

how to create a primary key with out creating an index?

View 10 Replies View Related

PL/SQL :: Compute Statistics After Creating Index

May 15, 2013

I have to create some indexes in a production database. Do I need to Compute Statistics after creating indexes? Or when I create they automatically are computed?

The version I'm using is:

Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE     10.2.0.5.0     Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

View 2 Replies View Related

SQL & PL/SQL :: Creating PLSQL Collection Table Type With Dynamic Query?

Jul 12, 2013

How can I create PLSQL table type dynamically?

I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.

View 2 Replies View Related

Client Tools :: Execute A Procedure With Parameter Of Object Type?

Mar 29, 2010

I am trying to execute the procedure from toad, where the type is like an object.How can I do that?

CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype;
CREATE OR REPLACE TYPE list_objtype AS OBJECT
( Emp_ID NUMBER(5),
EMp_Name VARCHAR2(32 BYTE)
);

In the following procedure I am calling list_t, but how would i pass the values to it?

PROCEDURE modify_settings
( p_List in list_t,
p_info out varchar2)
DECLARE
p_List TABLE;

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index

Jan 10, 2013

I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column

like '%abc%'
like 'abc%'
like '&abc'

Will be the corresponding index will be for those cases?

View 3 Replies View Related

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

Performance Tuning :: Creating Index On Base Tables Of A View?

Dec 9, 2010

I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?

The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.

View 1 Replies View Related

Text :: Creating Single Context Index On One-to-many And Lookup Table?

Apr 16, 2013

I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to search columns across both of these tables. Sample code below, more of my chattering after the code block:

CREATE TABLE SUBMISSION
( SUBMISSION_ID             NUMBER(10)          NOT NULL,
  SUBMISSION_NAME           VARCHAR2(100)       NOT NULL
);
CREATE TABLE ADVISOR_TYPE

[code]...

I've looked at DETAIL_DATASTORE and USER_DATASTORE, but the examples in Oracle Docs for DETAIL_DATASTORE leave me a little bit perplexed. It seems like this should be pretty straightforward. I'm trying to avoid creating new columns, and keeping the trigger adjustments to a minimum.

View 3 Replies View Related







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