SQL & PL/SQL

Hi all,

It will be a long text i think, I will try to be more specific so I'm starting..

I created a map in Oracle Warehouse Builder ( 10g Release 1 10.1.0.1 ), and deployed it on our datawarehouse (Oracle Database 9.2.0.5 on RedHat Linux kernel 2.4.18 with Itanium64 CPUs ).

Map was using Match-Merge Operator : The records in Input table, were matched by a column and with specific rules, they are merged to one record. For merge rules, I wrote custom code for approx all of the columns of matched records. It is a necessity because all columns have different rules to be merged with.

Perhaps you know every merge rules are created as a function by OWB. In this functions, ( custom edit part on Merge tab in MatchMerge operator), I declared a variable which is a type of match records, which is again created by the OWB internal code...

This type consists of the columns of the match record that I choose from the input table. For example :

-- Record definition for match standard type
TYPE "m1_ma_std_recordtype" IS RECORD (
"$$row_num" PLS_INTEGER,
"$$match_id" PLS_INTEGER);

-- Record definition for match type
TYPE "m1_ma_recordtype" IS RECORD (
"$$std_rec" "m1_ma_std_recordtype",
"CUSTOMER_ID" VARCHAR2(20),
"SOURCE_ID" VARCHAR2(20),
"COMPANY_ID" VARCHAR2(20),
............
............
);

So in my custom codes, I used type of "m1_ma_recordtype" as variable declaration. But after a while, I met some problems. When there occurs a change on input table like modification of the length of a column (source_id varchar2(30) ) or a new column, OWB changes the recordtype's name in its spec definition of the map's package. For example "m1_ma_recordtype" becomes "m2_ma_recordtype" . So this yields problems on my merge functions that I have to check nearly all merge rules for a change on the variable declaration.

But somehow i think i can use a synonym for this type. So I can use the synonym name for the declaration of the type and if any change occurs on the recordtype, I can only change the definition of synonym. But this approach hasn't been seen as a solution.. :))

When I try to make the trials simple as coding simple scripts, I saw that this declaration of synonym of this type is not supported or I am making silly mistakes on somewhere in my scripts.

I will be really appreciated, if you can make any suggestions or corrections...

There is some script pieces below to demonstrate the problem, I think it could be useful to address the problem :

CREATE OR REPLACE package testpkg1 AS

TYPE "m1_s_recordtype" IS RECORD (
"$$row_num" PLS_INTEGER,
"$$match_id" PLS_INTEGER);

TYPE "m1_ma_recordtype" IS RECORD (
"$$std_rec" "m1_s_recordtype",
"CUSTOMER_ID" VARCHAR2(20),
"NAME" VARCHAR2(20),
"SURNAME" VARCHAR2(20)
);

END testpkg1;
/

-- This script runs successfully as expected
declare
v_test testpkg1."m1_ma_recordtype";
begin
v_test."CUSTOMER_ID" := 23132;
dbms_output.put_line(v_test."CUSTOMER_ID");
end;

-- make the synonym definition for type in the package
create public synonym testpkg1_ma_recordtype for testpkg1."m1_ma_recordtype";

-- This script gives errors
declare
v_test testpkg1_ma_recordtype;
begin
v_test."CUSTOMER_ID" := 23132;
dbms_output.put_line(v_test."CUSTOMER_ID");
end;

PLS-00201: identifier 'TESTPKG1_MA_RECORDTYPE' must be declared
PLS-00320: the declaration of the type of this expression is
incomplete or malformed


--I also tried "type as type" way
create type testpkg1_t_rectype as testpkg1."m0_ma_recordtype";

--This script also gives some errors
declare
v_test testpkg1_t_rectype;
begin
v_test."CUSTOMER_ID" := 23132;
dbms_output.put_line(v_test."CUSTOMER_ID");
end;

PLS-00905: object MERKEZ.TESTPKG1_T_RECTYPE is invalid (MERKEZ is our schema name)
PLS-00320: the declaration of the type of this expression is incomplete or malformed

Message was edited by:
spulatkan