Integration

Hi,

Remove DECLARE.

Regards
Peter

DECLARE sections are only valid in two contexts:

1. Triggers
2. The local declaration of variables within a nested block

It still not working after remove DECLARE.


SQL> show error
Errors for PROCEDURE EC_NRB_SCHEMA.PROC_CHNB_PURGE_ALL_NED:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/9 PLS-00103: Encountered the symbol "PKG_RECORD" when expecting
one of the following:
:= . ( @ % ;

Works for me:

CREATE OR REPLACE PROCEDURE test
(
bld_tran_type_cd IN VARCHAR2 DEFAULT NULL,
v_purge_age IN NUMBER DEFAULT NULL,
row_count IN INTEGER DEFAULT NULL
)
AS

TYPE PKG_RECORD IS RECORD
(
pkg_trkng_nbr VARCHAR2(12) DEFAULT NULL,
pkg_trkng_unique_id VARCHAR2(10) DEFAULT NULL
);

TYPE pkg_record_table IS TABLE OF PKG_RECORD
index by binary_integer;

stage_item_status pkg_record_table;

rec_nbr INTEGER := 0;

BEGIN 
  NULL; --- removed this bit as I don't have your tables etc
END;
/

Procedure created.

You obviously haven't removed the correct bit or something.

It also looks like you're trying to insert into your associative array... you can't do that. They aren't tables and SQL doesn't know about them, so standard DML operations will not work on them. If you do actually have a table called stage_item_status in your schema, why call your associative array by the same name?!

What is it you're ultimately trying to do? Update a table somewhere? Insert data somewhere? Pull data out for a report? What?

I really appreciate your kind help on this.

You notes are correct.
The table or array is not recognized. so i got error below.
Basically, I am trying to convert some proc from Syb to Oracle without touching too much logic change.
The proc tries to get a batch of pkg_nbr/id, then delete all related record in the other tables based on the nbr/id collected. again get another batch do the same thing. (I don't know why previous people do that.) They used to do it in sybase in temp table.They want to keep it.(I don't care.)
I have to create a pl/sql table to do that.

SQL> show error
Errors for PROCEDURE EC_NRB_SCHEMA.PROC_CHNB_PURGE_ALL_NED:

LINE/COL ERROR
-------- -----------------------------------------------------------------
25/6 PL/SQL: SQL Statement ignored
25/18 PL/SQL: ORA-00942: table or view does not exist
61/11 PL/SQL: SQL Statement ignored
64/27 PL/SQL: ORA-00942: table or view does not exist
69/11 PL/SQL: SQL Statement ignored
72/27 PL/SQL: ORA-00942: table or view does not exist
77/11 PL/SQL: SQL Statement ignored
80/27 PL/SQL: ORA-00942: table or view does not exist
85/11 PL/SQL: SQL Statement ignored
88/27 PL/SQL: ORA-00942: table or view does not exist
93/11 PL/SQL: SQL Statement ignored

You might need direct grants (not via a role) from the schema with your tables (sis or whatever) to the schema with your procedure.

So, rather than using an array, you could use a Global Temp Table and effectively mimic what you do in sybase, or you could simply do several deletes:

DELETE FROM tablea
WHERE col1 in (SELECT cola FROM main_table WHERE ...);

DELETE FROM tableb
WHERE col1 in (SELECT cola FROM main_table WHERE ...);

....

DELETE FROM main_table
WHERE ...;

That would be my preferred solution.

Or you could just create a cursor to collect the data that in sybase you would put in the temp table, and then loop through that to delete your data. This is not the preferred method (nor is copying the sybase way of doing things by using the GTT!) as it is row-by-row (aka slow by slow).

I don't know much about Sybase, but based on what you are showing it must be a different beast.

To me it sounds like a really bad idea, to try and do a "one-to-one" mapping of a Sybase approach into Oracle.

Why the use of temp tables (Oracle do have those, you know) or collections for that matters. Not knowing what you are trying to do, just do it. In SQL that is.

And some details:
Don't (Not that it matters) use DISTINCT when using UNION. UNION will make everything distinct itself.

this

TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))

is considered wrong. Sysdate is already of type DATE and DATE - NUMBER is DATE in Oracle. And don't CAST, use TO_NUMBER
So change into

SYSDATE - TO_NUMBER(cd.decode_desc)

Unless, of course, decode_desc is already a NUMBER.


Regards
Peter

The best way to do this on Oracle would depend on a number of factors. Among the most important would be the number of rows returned by the query that gets the pkg_trkng_nbr, pkg_trkng_unique_id to be deleted, and how quickly it executes. If this query is relatively quick to run, then I would be extremely likely to just do a set of straight deltes in the procedure. Something alnog the lines of:

CREATE PROCEDURE my_SCHEMA.proc_purge_all_ned (bld_tran_type_cd IN VARCHAR2 DEFAULT NULL,
                                               v_purge_age IN NUMBER DEFAULT NULL,
                                               row_count IN INTEGER DEFAULT NULL) AS
BEGIN
   DELETE FROM related1
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN 
               (SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
                FROM ec_nrb_schema.staging_item_status sis,
                     ec_nrb_schema.staging_control sc,
                     ea_schema.code cd
                WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
                  AND sis.pkg_trkng_unique_id = sc.pkg_trkng_unique_id
                  AND sc.cmplt_flg = 'Y'
                  AND sis.bld_tran_type_cd = cd.cd_id
                  AND sis.last_updt_dt <= TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))
                UNION
                SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
                FROM ec_nrb_schema.staging_item_status sis,
                     ec_nrb_schema.staging_control sc,
                     ea_schema.code cd
                WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
                  AND ...);

   DELETE FROM related2
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN 
               (SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
                FROM ec_nrb_schema.staging_item_status sis,
                     ec_nrb_schema.staging_control sc,
                     ea_schema.code cd
                WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
                  AND sis.pkg_trkng_unique_id = sc.pkg_trkng_unique_id
                  AND sc.cmplt_flg = 'Y'
                  AND sis.bld_tran_type_cd = cd.cd_id
                  AND sis.last_updt_dt <= TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))
                UNION
                SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
                FROM ec_nrb_schema.staging_item_status sis,
                     ec_nrb_schema.staging_control sc,
                     ea_schema.code cd
                WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
                  AND ...);
END;

If the driving query is relatively expensive that is, takes significant time to run, then I would likely go with Boneist's suggestion and use a global temporary table. You would create the GTT once in sql before creating the procedure, then use it over and over. A GTT will get emptied when when the session exits (with ON COMMIT PRESERVE ROWS), or when the session commits (with ON COMMIT DELETE ROWS which is the default behaviour). So, you procedure would look something like:

-- Do this once before the procedure
CREATE GLOBAL TEMPORARY TABLE stage_item_status (
   pkg_trkng_nbr VARCHAR2(12),
   pkg_trkng_unique_id VARCHAR2(10))
ON COMMIT PRESERVE ROWS;
 
--Then create your procedure like
CREATE PROCEDURE my_SCHEMA.proc_purge_all_ned (bld_tran_type_cd IN VARCHAR2 DEFAULT NULL,
                                               v_purge_age IN NUMBER DEFAULT NULL,
                                               row_count IN INTEGER DEFAULT NULL) AS
BEGIN
   INSERT /*+ APPEND */ INTO stage_item_status
   SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
   FROM ec_nrb_schema.staging_item_status sis,
        ec_nrb_schema.staging_control sc,
        ea_schema.code cd
   WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
     AND sis.pkg_trkng_unique_id = sc.pkg_trkng_unique_id
     AND sc.cmplt_flg = 'Y'
     AND sis.bld_tran_type_cd = cd.cd_id
     AND sis.last_updt_dt <= TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))
   UNION
   SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
   FROM ec_nrb_schema.staging_item_status sis,
        ec_nrb_schema.staging_control sc,
        ea_schema.code cd
   WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
     AND ...
 
   DELETE FROM related1
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN (SELECT pkg_trkng_nbr, pkg_trkng_unique_id
                                                  FROM stage_item_status);

   DELETE FROM related2
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN (SELECT pkg_trkng_nbr, pkg_trkng_unique_id
                                                  FROM stage_item_status);
END;

My third choice would be using pl/sql arrays as you are attmpting here. To do this it would need to look more like:

CREATE PROCEDURE my_SCHEMA.proc_purge_all_ned (bld_tran_type_cd IN VARCHAR2 DEFAULT NULL,
                                               v_purge_age IN NUMBER DEFAULT NULL,
                                               row_count IN INTEGER DEFAULT NULL) AS
   TYPE PKG_RECORD IS RECORD (
      pkg_trkng_nbr VARCHAR2(12),
      pkg_trkng_unique_id VARCHAR2(10));

   TYPE pkg_record_table IS TABLE OF PKG_RECORD index by binary_integer;
   stage_item_status pkg_record_table;
   rec_nbr INTEGER := 0;

   CURSOR del_cur IS
      SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
      FROM ec_nrb_schema.staging_item_status sis,
           ec_nrb_schema.staging_control sc,
           ea_schema.code cd
      WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
        AND sis.pkg_trkng_unique_id = sc.pkg_trkng_unique_id
        AND sc.cmplt_flg = 'Y'
        AND sis.bld_tran_type_cd = cd.cd_id
        AND sis.last_updt_dt <= TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))
      UNION
      SELECT sis.pkg_trkng_nbr, sis.pkg_trkng_unique_id
      FROM ec_nrb_schema.staging_item_status sis,
           ec_nrb_schema.staging_control sc,
           ea_schema.code cd
      WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
        AND ...;
   
BEGIN 
   OPEN del_cur;
   LOOP
      FETCH del_cur BULK COLLECT INTO stage_item_status LIMIT 100
      IF stage_item_status.COUNT > 0 THEN
         FORALL i IN stage_item_status.FIRST .. stage_item_status.LAST
            DELETE FROM related1
            WHERE pkg_trkng_nbr = stage_item_status(i).stage_item_status
              AND pkg_trkng_unique_id = stage_item_status(i).pkg_trkng_unique_id;

         FORALL i IN stage_item_status.FIRST .. stage_item_status.LAST
            DELETE FROM related2
            WHERE pkg_trkng_nbr = stage_item_status(i).stage_item_status
              AND pkg_trkng_unique_id = stage_item_status(i).pkg_trkng_unique_id
      END IF;
      EXIT WHEN del_cur%NOT_FOUND;
   END LOOP;
END;

Note that in all cases, I removed the DISTINCt from your select statement. Using UNION implies a sort distinct of the two queries anyway, so distincting each one individually is an overhead you don't need. I would likely use union all in any case to avoid the sort since there is no real harm in deleting a non-existent row.

HTH
John