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 Go to next message
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 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
misragopal wrote on Sat, 09 November 2013 09:34
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.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #600605 is a reply to message #600604] Sat, 09 November 2013 04:38 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Hi lalit,
Could you please suggest on REAL PROBLEM. I am using 'wm_concat' just for display records. Definitly not going to use in real code scenario.
Thanks
Re: validate records in nested table [message #600606 is a reply to message #600605] Sat, 09 November 2013 05:06 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
misragopal wrote on Sat, 09 November 2013 11:38

Could you please suggest on REAL PROBLEM. I am using 'wm_concat' just for display records. Definitly not going to use in real code scenario.

People suggest based on what you post on the forum not based on what is hidden in your mind as they don't have read access to that Smile
Anyway, did you implement the map method?
Re: validate records in nested table [message #600607 is a reply to message #600606] Sat, 09 November 2013 05:17 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

I am Sorry Dariyoosh/Lalit, but definitly "wm_concat/csv loading" is not the ISSUE. we need not to read mind. we just need to see what is written in black&white on screen. Why we are trying to find out things that are not related with solution (atleast in current circumstances). Why we are digging out what is source of CSV-string. That is just a variable contain string, thats it.
Dariyoosh, i am looking into map methods but not able to implement in my case. trying to find out some other alternative which i can implement.

Thanks.
Re: validate records in nested table [message #600608 is a reply to message #600607] Sat, 09 November 2013 05:29 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
misragopal wrote on Sat, 09 November 2013 12:17
Dariyoosh, i am looking into map methods but not able to implement in my case.

Why? What's the problem?

[Updated on: Sat, 09 November 2013 06:30]

Report message to a moderator

Re: validate records in nested table [message #600611 is a reply to message #600608] Sat, 09 November 2013 07:05 Go to previous messageGo to next message
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 #600612 is a reply to message #600611] Sat, 09 November 2013 07:15 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
misragopal wrote on Sat, 09 November 2013 14:05
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.

What makes you believe that you're not supposed to spend time to R&D?

misragopal wrote on Sat, 09 November 2013 14:05

Right now my preference is to make code executable and accurate

So in sum, someone is supposed to provide the so called accurate and executable code, and you just copy/paste it, am I right?
Re: validate records in nested table [message #600613 is a reply to message #600612] Sat, 09 November 2013 07:41 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

No you are not RIGHT. This time you are WRONG Dariyoosh,
I am not dependent Someone's code clip. I just asked HELP if someone ready to give his expert suggestion. Definitly this problem must be faced by many people. I am Very politly asking about their thoughts. I did my "Work", Instead of pointing and deviating POST from original topic. I am just asking expert suggestion from Expert like YOU.
I JUST need suggestions, rest of things i will DO by my own.

Things are perfectly working myEnd and i posted solution as well. If you have something better "suggest us" otherwise spare us. i believe this not a forum to incease number of post.

I know "No one is expert" of all Areana. I am also tring to find a solution and will post on same page.

May be Michel Cadot, Littlefoot or BlackSwan will have some suggestion.

Thanks for your help Dariyoosh.
Re: validate records in nested table [message #600615 is a reply to message #600599] Sat, 09 November 2013 08:03 Go to previous messageGo to next message
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 #600620 is a reply to message #600615] Sat, 09 November 2013 08:17 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

This is really good example, Thanks "Solomon Yakobson". i appreciate your efforts to look into this post.
Re: validate records in nested table [message #600646 is a reply to message #600599] Sun, 10 November 2013 11:58 Go to previous message
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.

domain1,domain2
domain3

compareTo
domain1
domain2
domain3


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

Previous Topic: To get a text after . from a text box
Next Topic: check constraint on column
Goto Forum:
  


Current Time: Thu Mar 28 19:41:48 CDT 2024