Home » SQL & PL/SQL » SQL & PL/SQL » validate records in nested table (Oracle DB 11g Enterprise Release 11.2.0.3.0 - 64bit)
validate records in nested table [message #600599] |
Sat, 09 November 2013 02:34 |
misragopal
Messages: 125 Registered: June 2005 Location: New Delhi, india
|
Senior Member |
|
|
Hi,
I created one table based on oracle single column type. This type's data is comming as csv string. and we need to convert into array then inset into table. This column MAY come as NULL also.
Before insert we need to verify table about that record existence. I created code but this varification point is giving ERROR. I created a sample code to simulate my problem.
Oracle Type creation
CREATE OR REPLACE TYPE REQ_DOMAIN_OBJ AS OBJECT (DOMAIN_NAME Varchar2 (100));
CREATE OR REPLACE TYPE REQ_DOMAIN_TBL IS TABLE OF REQ_DOMAIN_OBJ;
Table Creation
CREATE TABLE TEST_SYNC_REQUESTS
( REQ_ID NUMBER,
REQ_TYPE VARCHAR2(200 CHAR),
DOMAIN_LIST REQ_DOMAIN_TBL,
REQ_STATUS VARCHAR2(200 CHAR),
MAIL_STATUS CHAR(1 CHAR),
CRE_BY VARCHAR2(250 CHAR) NOT NULL,
CRE_DT DATE DEFAULT SYSDATE)
NESTED TABLE DOMAIN_LIST STORE AS DOMAIN_LIST_NT;
Dummy record insertion
INSERT INTO TEST_SYNC_REQUESTS
(REQ_ID, REQ_TYPE,DOMAIN_LIST,
REQ_STATUS, MAIL_STATUS,CRE_BY)
VALUES(1,'JOB-1', REQ_DOMAIN_TBL(REQ_DOMAIN_OBJ('DOMAIN1'),
REQ_DOMAIN_OBJ('DOMAIN2'),
REQ_DOMAIN_OBJ('DOMAIN3')),
'S','N', USER);
INSERT INTO TEST_SYNC_REQUESTS
(REQ_ID, REQ_TYPE,DOMAIN_LIST,
REQ_STATUS, MAIL_STATUS,CRE_BY)
VALUES(2,'JOB-2', REQ_DOMAIN_TBL(REQ_DOMAIN_OBJ('DOMAIN1'),
REQ_DOMAIN_OBJ('DOMAIN2')),
'S','N', USER);
COMMIT;
SQL statement to inserted records verification
SELECT T.*,(SELECT WM_CONCAT(DOMAIN_NAME) FROM TABLE (T.DOMAIN_LIST)) FROM TEST_SYNC_REQUESTS t
Now created a plsql block which will insert records in this table. this will first check records.
IF record already exists THEN
it will give message
ELSE
it will insert record in table.
Very simple logic, but i am stuck in record comparision.
There are 3 case i need to test on this logic where domain csv list is comming as-
# case 1 => 'DOMAIN1,DOMAIN2,DOMAIN3'; -- record already exist : no insert case
# case 2 => 'DOMAIN1,DOMAIN2'; -- no record exist : Insert case
# case 3 => ''; -- no record exist : Insert case
SET SERVEROUTPUT ON;
DECLARE
lv_req_id test_sync_requests.req_id%type;
lv_req_type test_sync_requests.req_type%type:='JOB-1';
lv_domain_list test_sync_requests.domain_list%type;
lv_req_status test_sync_requests.req_status%type:='S';
lv_mail_status test_sync_requests.mail_status%type:='N';
lv_cre_by test_sync_requests.cre_by%type:= USER;
lv_cnt number;
lv_domain_csv_list varchar2(100);
lv_domain_array dbms_utility.lname_array;
tokn BINARY_INTEGER;
BEGIN
--variable assignments
lv_req_type :='JOB-1';
lv_req_status :='S';
lv_mail_status:='N';
lv_domain_csv_list := 'DOMAIN1,DOMAIN2,DOMAIN3';
--case 1 => 'DOMAIN1,DOMAIN2,DOMAIN3';
--case 2 => 'DOMAIN1,DOMAIN2';
--case 3 => '';
-- PK generation (dummy code)
SELECT NVL(MAX(REQ_ID),0)+1
INTO lv_req_id
FROM TEST_SYNC_REQUESTS;
--converting Csv list to array variable
IF (NVL(lv_domain_csv_list,'x')<>'x') THEN
dbms_utility.comma_to_table(list => lv_domain_csv_list,
tablen => tokn,
tab => lv_domain_array);
FOR i IN 1..lv_domain_array.COUNT LOOP
lv_domain_list(i).domain_name:=lv_domain_array(i);
END LOOP;
END IF;
-- validation
SELECT COUNT(*)
INTO lv_cnt
FROM TEST_SYNC_REQUESTS
WHERE req_type = lv_req_type
AND domain_list = lv_domain_list
AND req_status = lv_req_status
AND mail_status = lv_mail_status
AND cre_by = lv_cre_by;
IF (lv_cnt >0) THEN
INSERT INTO TEST_SYNC_REQUESTS
(REQ_ID, REQ_TYPE,DOMAIN_LIST,
REQ_STATUS, MAIL_STATUS,CRE_BY)
VALUES(1,'JOB-1', REQ_DOMAIN_TBL(REQ_DOMAIN_OBJ('DOMAIN1'),
REQ_DOMAIN_OBJ('DOMAIN2'),
REQ_DOMAIN_OBJ('DOMAIN3')),
'S','N', USER);
DBMS_OUTPUT.PUT_LINE('RECORD INSERTED IN TABLE');
ELSE
DBMS_OUTPUT.PUT_LINE('RECORD ALREADY EXISTS IN TABLE');
END IF;
END;
Getting Error
ORA-06550: line 42, column 22:
PL/SQL: ORA-22952: Nested Table equality requires a map method on the element ADT
--ORA-06550: line 38, column 3:
Please Suggest , how can i validate record existance in table (when array is with/ without value).
thanks for considering this.
Gopal
[Updated on: Sat, 09 November 2013 02:36] Report message to a moderator
|
|
|
Re: validate records in nested table [message #600600 is a reply to message #600599] |
Sat, 09 November 2013 03:00 |
|
dariyoosh
Messages: 538 Registered: March 2009 Location: France
|
Senior Member |
|
|
misragopal wrote on Sat, 09 November 2013 09:34I created one table based on oracle single column type. This type's data is comming as csv string. and we need to convert into array then inset into table. This column MAY come as NULL also.
But you don't say where does the CSV string come from, if it is inside a file, then there is no need for PL/SQL, just use SQL*Loader or an external table.
misragopal wrote on Sat, 09 November 2013 09:34
-- PK generation (dummy code)
SELECT NVL(MAX(REQ_ID),0)+1
INTO lv_req_id
FROM TEST_SYNC_REQUESTS;
This method of key generation will not work in a multi user environment. You should use a sequence
[Updated on: Sat, 09 November 2013 03:06] Report message to a moderator
|
|
|
Re: validate records in nested table [message #600601 is a reply to message #600600] |
Sat, 09 November 2013 03:05 |
misragopal
Messages: 125 Registered: June 2005 Location: New Delhi, india
|
Senior Member |
|
|
Yes, I am using sequence for generating pk values. Because this is only simulated code, I don't want to increase number of objects. Its dummy code as I mentioned.
Thanks for suggestion, let me look into this link.
We are getting CSV as parameter. In this sample code consider it in a variable
[Updated on: Sat, 09 November 2013 03:09] Report message to a moderator
|
|
|
Re: validate records in nested table [message #600602 is a reply to message #600601] |
Sat, 09 November 2013 03:11 |
|
dariyoosh
Messages: 538 Registered: March 2009 Location: France
|
Senior Member |
|
|
Quote:
-- validation
SELECT COUNT(*)
INTO lv_cnt
FROM TEST_SYNC_REQUESTS
WHERE req_type = lv_req_type
AND domain_list = lv_domain_list -- Here there is a problem in your code
AND req_status = lv_req_status
AND mail_status = lv_mail_status
AND cre_by = lv_cre_by;
As it seems to me you have not defined any map method to do the equality test for this data type. Have a look at Object comparison
Quote:
We are getting CSV as parameter. In this sample code consider it in a variable
Yes you get is as a parameter but again you don't answer to the question from where? email? file? there should be some original source, it's this original source that I'm asking and if it is a file then as I said earlier there is no need for PL/SQL.
[Updated on: Sat, 09 November 2013 03:18] Report message to a moderator
|
|
|
Re: validate records in nested table [message #600603 is a reply to message #600602] |
Sat, 09 November 2013 03:35 |
misragopal
Messages: 125 Registered: June 2005 Location: New Delhi, india
|
Senior Member |
|
|
Actually,
"CSV string"'s source is a java UI program which will pass this string in procedure's parameter. instead of making "this sample code complex" i created a small plsql block to explain my problem. I am aware that file loading can better handle thru sqlloader or external table but this is not that case. this is just a validation problem.
Thanks for sharing your thoughts i will look object comparision link.
[Updated on: Sat, 09 November 2013 03:38] Report message to a moderator
|
|
|
Re: validate records in nested table [message #600604 is a reply to message #600599] |
Sat, 09 November 2013 04:34 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
misragopal wrote on Sat, 09 November 2013 14:04
(SELECT WM_CONCAT(DOMAIN_NAME) FROM TABLE (T.DOMAIN_LIST)) FROM TEST_SYNC_REQUESTS t
WM_CONCAT is an undocumented feature. You should avoid using it.
|
|
|
|
|
|
|
Re: validate records in nested table [message #600611 is a reply to message #600608] |
Sat, 09 November 2013 07:05 |
misragopal
Messages: 125 Registered: June 2005 Location: New Delhi, india
|
Senior Member |
|
|
I have not much worked on type Member function, but i am sure that we can compare with that. This require time for me to do R&D on this topic. Right now my preference is to make code executable and accurate.
As alternate solution i created a function which will convert type data into Comma seperated string. i used this function to compare both the types (existing table column value and supplied value) and it is working perfectly. I am posting this as alternate solution.
FUNCTION BODY:
FUNCTION get_obj_str(l_dlist in REQ_DOMAIN_TBL)
RETURN VARCHAR2 IS
l_retval varchar2(1000);
BEGIN
IF (l_dlist.COUNT=0) THEN
l_retval:= 'X';
ELSE
FOR I IN 1..l_dlist.COUNT LOOP
IF (l_retval IS NOT NULL) THEN
l_retval := l_retval||',';
END IF;
l_retval:= l_retval||l_dlist(I).domain_list;
END LOOP;
END IF;
RETURN l_retval;
END get_obj_str;
implementation of function in validation-
-- validation
SELECT COUNT(*)
INTO lv_cnt
FROM TEST_SYNC_REQUESTS
WHERE NVL(req_type,'X') = NVL(lv_req_type,'X')
AND get_obj_str(NVL(domain_list,req_domain_tbl())
= get_obj_str(NVL(lv_domain_list,req_domain_tbl()) -- Here is a implementation
AND NVL(req_status,'X') = NVL(lv_req_status,'X')
AND NVL(mail_status,'X') = NVL(lv_mail_status,'X')
AND NVL(cre_by,'X') = NVL(lv_cre_by,'X');
Still I need help from 'Experts', to suggest me how to implement it thru member function.
that will be great learning for me.
Thanks,
Gopal
[Updated on: Sat, 09 November 2013 07:08] Report message to a moderator
|
|
|
|
|
Re: validate records in nested table [message #600615 is a reply to message #600599] |
Sat, 09 November 2013 08:03 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You don't need PL/SQL for that. And you don't need object type, then you would't need map/order method.
CREATE OR REPLACE
TYPE REQ_DOMAIN_TBL
IS TABLE OF VARCHAR2(100)
/
CREATE TABLE TEST_SYNC_REQUESTS(
REQ_ID NUMBER,
REQ_TYPE VARCHAR2(200 CHAR),
DOMAIN_LIST REQ_DOMAIN_TBL,
REQ_STATUS VARCHAR2(200 CHAR),
MAIL_STATUS CHAR(1 CHAR),
CRE_BY VARCHAR2(250 CHAR) NOT NULL,
CRE_DT DATE DEFAULT SYSDATE
)
NESTED TABLE DOMAIN_LIST
STORE AS DOMAIN_LIST_NT
/
INSERT
INTO TEST_SYNC_REQUESTS(
REQ_ID,
REQ_TYPE,
DOMAIN_LIST,
REQ_STATUS,
MAIL_STATUS,
CRE_BY
)
VALUES(
1,
'JOB-1',
REQ_DOMAIN_TBL(
'DOMAIN1',
'DOMAIN2',
'DOMAIN3'
),
'S',
'N',
USER
)
/
INSERT
INTO TEST_SYNC_REQUESTS(
REQ_ID,
REQ_TYPE,
DOMAIN_LIST,
REQ_STATUS,
MAIL_STATUS,
CRE_BY
)
VALUES(
2,
'JOB-2',
REQ_DOMAIN_TBL(
'DOMAIN1',
'DOMAIN2'
),
'S',
'N',
USER
)
/
COMMIT
/
SET LINESIZE 132
COLUMN REQ_ID FORMAT 99999
COLUMN REQ_TYPE FORMAT A8
COLUMN REQ_STATUS FORMAT A10
COLUMN MAIL_STATUS FORMAT A11
COLUMN DOMAIN_LIST FORMAT A47
COLUMN CRE_BY FORMAT A6
SELECT *
FROM TEST_SYNC_REQUESTS
/
REQ_ID REQ_TYPE DOMAIN_LIST REQ_STATUS MAIL_STATUS CRE_BY CRE_DT
------ -------- ----------------------------------------------- ---------- ----------- ------ ---------
1 JOB-1 REQ_DOMAIN_TBL('DOMAIN1', 'DOMAIN2', 'DOMAIN3') S N SCOTT 09-NOV-13
2 JOB-2 REQ_DOMAIN_TBL('DOMAIN1', 'DOMAIN2') S N SCOTT 09-NOV-13
SQL>
Now, since nested table is of Oracle built-in type VARCHAR2 and not a nested table of user defined type object, we don't need MAP/ORDER method to compare. And we don't need PL/SQL since all this can be done using MERGE:
merge
into test_sync_requests d
using (
select 3 req_id,
'JOB-1' req_type,
REQ_DOMAIN_TBL(
'DOMAIN1',
'DOMAIN2',
'DOMAIN3'
) domain_list,
'S' req_status,
'N' mail_status,
USER cre_by
from dual
union all
select 4,
'JOB-1',
REQ_DOMAIN_TBL(
'DOMAIN1',
'DOMAIN2'
),
'S',
'N',
USER
from dual
) s
on (
d.req_type = s.req_type
and
d.req_status = s.req_status
and
d.mail_status = s.mail_status
and (
d.domain_list = s.domain_list
or
(
d.domain_list is null
and
s.domain_list is null
)
)
)
when not matched
then
insert
values(
s.req_id,
s.req_type,
s.domain_list,
s.req_status,
s.mail_status,
s.cre_by,
SYSDATE
)
/
1 row merged.
SQL> SELECT *
2 FROM TEST_SYNC_REQUESTS
3 /
REQ_ID REQ_TYPE DOMAIN_LIST REQ_STATUS MAIL_STATUS CRE_BY CRE_DT
------ -------- ----------------------------------------------- ---------- ----------- ------ ---------
1 JOB-1 REQ_DOMAIN_TBL('DOMAIN1', 'DOMAIN2', 'DOMAIN3') S N SCOTT 09-NOV-13
2 JOB-2 REQ_DOMAIN_TBL('DOMAIN1', 'DOMAIN2') S N SCOTT 09-NOV-13
4 JOB-1 REQ_DOMAIN_TBL('DOMAIN1', 'DOMAIN2') S N SCOTT 09-NOV-13
SQL>
SY.
P.S. You need to explain if nested table elements can be NULL. My code assumes domain list can be null but domain list elements can't.
P.P.S. You can do this without MAP method even if you need table of objects.
|
|
|
|
Re: validate records in nested table [message #600646 is a reply to message #600599] |
Sun, 10 November 2013 11:58 |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi Gopal,
Even I never worked with implementing object comparison, but the problem is because you created type REQ_DOMAIN_OBJ as object type, so you have to have define a comparison method. Because object type can't be compared directly with an '='. Example is:
CREATE OR REPLACE TYPE REQ_DOMAIN_OBJ_CHAR AS OBJECT (DOMAIN_NAME Varchar2 (100));
CREATE OR REPLACE TYPE REQ_DOMAIN_OBJ_NUM AS OBJECT (DOMAIN_NAME NUMBER(100));
Because an object is user defined type, and oracle don't know how to compare, so one should implement comparison method.
Now questions for you:
Why,
CREATE OR REPLACE TYPE REQ_DOMAIN_OBJ AS OBJECT (DOMAIN_NAME Varchar2 (100));
CREATE OR REPLACE TYPE REQ_DOMAIN_TBL IS TABLE OF REQ_DOMAIN_OBJ;
Why not
CREATE OR REPLACE TYPE type_names AS TABLE OF VARCHAR2(100);
Is the object type really necessary?
And you can always compare nested tables as in below link:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#CHDEDHAB
Otherwise, combining in string and then comparing string looks good to me, until you find a case like.
compareTo
And here is the example, how to implement map method:
http://database-geek.com/2005/05/14/oracle-objects-types-and-collections-part-2/
Regards,
Manu
[Updated on: Sun, 10 November 2013 12:07] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Mar 28 19:41:48 CDT 2024
|