Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Table Arrays
PLSQL Table Arrays [message #299384] Mon, 11 February 2008 15:40 Go to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Hi,

We have an inhoude plsql mapping tool, which generates plsql on the fly.

The code generate is just a plsql package.

I have made amendments to convert to bulk binds and host table arrays.

However, I would really love to be able to implement the equivalent of primary keys in my PLSQL table arrays.

I know the "hard" way to do this is to have an associative array with the primary key as the combined varchar2 index, and a host value pointing to the binary element of the full PLSQL array, but that is quite an overhead coding wise at this time.

Is there an easier way?

I have though of purely using associative arrays, but I cant bulk insert directly from an associative array can I?

Any help appreciated.
Re: PLSQL Table Arrays [message #299442 is a reply to message #299384] Tue, 12 February 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you posted an example of what you want to do I'm pretty sure more people (including me) will better understand what you want to do.

Regards
Michel
Re: PLSQL Table Arrays [message #299458 is a reply to message #299384] Tue, 12 February 2008 02:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
ignore, added little to the thread

[Updated on: Tue, 12 February 2008 02:20]

Report message to a moderator

Re: PLSQL Table Arrays [message #299463 is a reply to message #299384] Tue, 12 February 2008 02:30 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok, I will write some psuedo-code, as to copy the code in here would be too large.

Package Spec

TYPE tt_WIN_MIN_CLIENT is table of WIN_MIN_CLIENT%ROWTYPE index by binary_integer;
t_WIN_MIN_CLIENT tt_WIN_MIN_CLIENT;



Package Body

  procedure map_this
    
    i_WIN_MIN_CLIENT WIN_MIN_CLIENT%ROWTYPE;
  begin
  
    i_WIN_MIN_CLIENT.col1 := 'Value1';
    i_WIN_MIN_CLIENT.col2 := 'Value2';
    i_WIN_MIN_CLIENT.col3 := NULL; -- This is a NOT_NULL Column, How can I emulate this in the local array?
    
    -- Fill up next table array item
    -- This is where I add column format rules like RPAD, NVL etc.
    
    v_index := t_WIN_MIN_CLIENT.COUNT+1;
    
    t_WIN_MIN_CLIENT(v_index).col1 := RPAD(i_WIN_MIN_CLIENT.col1,' ',6); -- How Can I check for Primary Keys in this array?
    t_WIN_MIN_CLIENT(v_index).col2 := NVL(i_WIN_MIN_CLIENT.col2,'Nulls Here');
    t_WIN_MIN_CLIENT(v_index).col3 := i_WIN_MIN_CLIENT.col3
    
    If Any Errors, I maintain a separate array of errored entries, and rollback all the arrays for this entry.
    
  end;
  
  
  forall i_WIN_MIN_CLIENT
    insert into WIN_MIN_CLIENT;
    
  -- I dont want this INSERT to fail on primary keys, or check constraints, I want to have already done the work in the map_this procedure.

Re: PLSQL Table Arrays [message #299469 is a reply to message #299463] Tue, 12 February 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FORALL i IN INDICES OF t_WIN_MIN_CLIENT
    insert into WIN_MIN_CLIENT;

Maybe this is what you are searching for.

Regards
Michel

Re: PLSQL Table Arrays [message #299478 is a reply to message #299384] Tue, 12 February 2008 03:36 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Gosh, its hard when you know what you are thinking, to portray that to someone else somethimes Smile

Ok.

In my PLSQL table array, I want to "simulate" primary keys and check constraints.

If I can simulate the primary keys, not hulls etc, I can report and rollback the offending parent array items )there may be 80 arrays holding data for this parent).
Re: PLSQL Table Arrays [message #299652 is a reply to message #299478] Tue, 12 February 2008 20:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Here is a partial solution. It handles the not null constraint by declaring it in a pl/sql record type. It handles the primary key constraint by using index by varchar2 and using the primary key values for the index. It saves the offending rows into a rejects table. However, I could not figure out how to get the forall i in indices of ... to work with an index by varchar2. Maybe Michel can figure that out and demonstrate. I have supplied the code and the test run separately.

-- code:

CREATE TABLE source_values
  (col1 VARCHAR2 (10),
   col2 VARCHAR2 (10),
   col3 VARCHAR2 (10))
/
INSERT ALL
INTO source_values VALUES ('a', 'b', 'c')
INTO source_values VALUES ('d', 'e', NULL)
INTO source_values VALUES ('g', 'h', 'i')
INTO source_values VALUES ('a', 'j', 'k')
INTO source_values VALUES ('g', 'l', 'm')
SELECT * FROM DUAL
/
CREATE TABLE win_min_client
  (col1  VARCHAR2 (10) PRIMARY KEY,
   col2  VARCHAR2 (10),
   col3  VARCHAR2 (10) NOT NULL)
/
CREATE TABLE rejects 
  (col1  VARCHAR2 (10),
   col2  VARCHAR2 (10),
   col3  VARCHAR2 (10),
   reason VARCHAR2 (10))
/
CREATE OR REPLACE PACKAGE your_pkg
AS
  TYPE wmc_rec IS RECORD
    (col1  VARCHAR2 (10),
     col2  VARCHAR2 (10),
     col3  VARCHAR2 (10) NOT NULL DEFAULT 'not null');
  TYPE tt_WIN_MIN_CLIENT is table of wmc_rec index by VARCHAR2(10);
  t_WIN_MIN_CLIENT tt_WIN_MIN_CLIENT;
  PROCEDURE map_this;
END your_pkg;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY your_pkg
AS
  PROCEDURE map_this
  IS
    i_WIN_MIN_CLIENT WIN_MIN_CLIENT%ROWTYPE;
    v_index VARCHAR2 (10);
    v_test  VARCHAR2 (10);
  BEGIN
    FOR r IN (SELECT * FROM source_values) LOOP
      i_WIN_MIN_CLIENT.col1 := RPAD(r.col1, 6, '.'); 
      i_WIN_MIN_CLIENT.col2 := NVL(r.col2,'Nulls Here');
      i_WIN_MIN_CLIENT.col3 := r.col3;  
      v_index := r.col1;
      BEGIN
        SELECT  t_win_min_client(v_index).col1 INTO v_test FROM DUAL;
        INSERT INTO rejects (col1, col2, col3, reason) 
        VALUES (i_WIN_MIN_CLIENT.col1, i_WIN_MIN_CLIENT.col2, i_WIN_MIN_CLIENT.col3, 'dup col1');
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
        BEGIN
          t_WIN_MIN_CLIENT(v_index).col1 := i_WIN_MIN_CLIENT.col1; 
          t_WIN_MIN_CLIENT(v_index).col2 := i_WIN_MIN_CLIENT.col2;
          t_WIN_MIN_CLIENT(v_index).col3 := i_WIN_MIN_CLIENT.col3;
        EXCEPTION
          WHEN VALUE_ERROR THEN 
            INSERT INTO rejects (col1, col2, col3, reason) 
            VALUES (i_WIN_MIN_CLIENT.col1, i_WIN_MIN_CLIENT.col2, i_WIN_MIN_CLIENT.col3, 'col3 NULL');
            t_win_min_client.DELETE(v_index);
        END;
      END;
    END LOOP;
    v_index := t_win_min_client.first;
    WHILE v_index IS NOT NULL LOOP
      insert into WIN_MIN_CLIENT (col1, col2, col3) 
      VALUES (t_win_min_client(v_index).col1, 
              t_win_min_client(v_index).col2, 
              t_win_min_client(v_index).col3);  
      v_index := t_win_min_client.NEXT (v_index);
    END LOOP;
  END map_this;
END your_pkg;
/
SHOW ERRORS
EXEC your_pkg.map_this 
SELECT * FROM win_min_client
/
SELECT * FROM rejects
/
-- clean up:
drop package your_pkg
/
DROP TABLE win_min_client
/
DROP TABLE SOURCE_VALUES
/
drop table rejects
/



-- test run:

SCOTT@orcl_11g> CREATE TABLE source_values
  2    (col1 VARCHAR2 (10),
  3  	col2 VARCHAR2 (10),
  4  	col3 VARCHAR2 (10))
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO source_values VALUES ('a', 'b', 'c')
  3  INTO source_values VALUES ('d', 'e', NULL)
  4  INTO source_values VALUES ('g', 'h', 'i')
  5  INTO source_values VALUES ('a', 'j', 'k')
  6  INTO source_values VALUES ('g', 'l', 'm')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> CREATE TABLE win_min_client
  2    (col1  VARCHAR2 (10) PRIMARY KEY,
  3  	col2  VARCHAR2 (10),
  4  	col3  VARCHAR2 (10) NOT NULL)
  5  /

Table created.

SCOTT@orcl_11g> CREATE TABLE rejects
  2    (col1  VARCHAR2 (10),
  3  	col2  VARCHAR2 (10),
  4  	col3  VARCHAR2 (10),
  5  	reason VARCHAR2 (10))
  6  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    TYPE wmc_rec IS RECORD
  4  	 (col1	VARCHAR2 (10),
  5  	  col2	VARCHAR2 (10),
  6  	  col3	VARCHAR2 (10) NOT NULL DEFAULT 'not null');
  7    TYPE tt_WIN_MIN_CLIENT is table of wmc_rec index by VARCHAR2(10);
  8    t_WIN_MIN_CLIENT tt_WIN_MIN_CLIENT;
  9    PROCEDURE map_this;
 10  END your_pkg;
 11  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    PROCEDURE map_this
  4    IS
  5  	 i_WIN_MIN_CLIENT WIN_MIN_CLIENT%ROWTYPE;
  6  	 v_index VARCHAR2 (10);
  7  	 v_test  VARCHAR2 (10);
  8    BEGIN
  9  	 FOR r IN (SELECT * FROM source_values) LOOP
 10  	   i_WIN_MIN_CLIENT.col1 := RPAD(r.col1, 6, '.');
 11  	   i_WIN_MIN_CLIENT.col2 := NVL(r.col2,'Nulls Here');
 12  	   i_WIN_MIN_CLIENT.col3 := r.col3;
 13  	   v_index := r.col1;
 14  	   BEGIN
 15  	     SELECT  t_win_min_client(v_index).col1 INTO v_test FROM DUAL;
 16  	     INSERT INTO rejects (col1, col2, col3, reason)
 17  	     VALUES (i_WIN_MIN_CLIENT.col1, i_WIN_MIN_CLIENT.col2, i_WIN_MIN_CLIENT.col3, 'dup col1');
 18  	   EXCEPTION
 19  	     WHEN NO_DATA_FOUND THEN
 20  	     BEGIN
 21  	       t_WIN_MIN_CLIENT(v_index).col1 := i_WIN_MIN_CLIENT.col1;
 22  	       t_WIN_MIN_CLIENT(v_index).col2 := i_WIN_MIN_CLIENT.col2;
 23  	       t_WIN_MIN_CLIENT(v_index).col3 := i_WIN_MIN_CLIENT.col3;
 24  	     EXCEPTION
 25  	       WHEN VALUE_ERROR THEN
 26  		 INSERT INTO rejects (col1, col2, col3, reason)
 27  		 VALUES (i_WIN_MIN_CLIENT.col1, i_WIN_MIN_CLIENT.col2, i_WIN_MIN_CLIENT.col3, 'col3 NULL');
 28  		 t_win_min_client.DELETE(v_index);
 29  	     END;
 30  	   END;
 31  	 END LOOP;
 32  	 v_index := t_win_min_client.first;
 33  	 WHILE v_index IS NOT NULL LOOP
 34  	   insert into WIN_MIN_CLIENT (col1, col2, col3)
 35  	   VALUES (t_win_min_client(v_index).col1,
 36  		   t_win_min_client(v_index).col2,
 37  		   t_win_min_client(v_index).col3);
 38  	   v_index := t_win_min_client.NEXT (v_index);
 39  	 END LOOP;
 40    END map_this;
 41  END your_pkg;
 42  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC your_pkg.map_this

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM win_min_client
  2  /

COL1       COL2       COL3
---------- ---------- ----------
a.....     b          c
g.....     h          i

SCOTT@orcl_11g> SELECT * FROM rejects
  2  /

COL1       COL2       COL3       REASON
---------- ---------- ---------- ----------
d.....     e                     col3 NULL
a.....     j          k          dup col1
g.....     l          m          dup col1

SCOTT@orcl_11g> 


Re: PLSQL Table Arrays [message #299653 is a reply to message #299478] Tue, 12 February 2008 20:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could use Global Temporary Tables instead of collections.
Re: PLSQL Table Arrays [message #299943 is a reply to message #299384] Wed, 13 February 2008 17:22 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Barbara,

Thanks for your time looking at this.

The record type is interesting. I will try that. That will probably solve my issue with check constraints.

However, there is a limitation with bulk inserts, in that they can only be used with index by binary integer arrays, not varchar2 ones.

I know of a way around it. And that is to maintain 2 arrays. One array to hold the primary key as the index, and its value corresponding tot he binary integer of the main array. Then I can use the associative array to remove items from the main array using the binary integer value. I wass hoping there might have been a feature in collection arrays that would emulate primary keys and still be able to bulk insert with.

However, I have just had a thought while writing this.
I could probably find a way of turning the primary key into an integer value, and populate the plsql table on that basis.

THen I can use "indices of" to write the spare array that I end up with.

Thanks all for you input.

[Updated on: Wed, 13 February 2008 17:23]

Report message to a moderator

Previous Topic: Finding old records
Next Topic: DBMS STATS import issue.
Goto Forum:
  


Current Time: Tue Dec 06 12:15:27 CST 2016

Total time taken to generate the page: 0.14933 seconds