2 similar Functions, 1 works and 1 does not (converting data from long to varchar2

From: <samthegreat_at_gmail.com>
Date: 25 Oct 2006 14:54:37 -0700
Message-ID: <1161813277.510596.165330@i42g2000cwa.googlegroups.com>

Hi everybody,

I am new here and I hope you will not mind my asking a question. I have not been lurking on the usenet groups, so I do not know the proper etiquete. Please forgive me.

I am struggling with getting data from an oracle 9i "production" database over to an oracle 10g "data warehouse" db. I would like to get this data refreshed nightly. The problem is that the source table contains data type LONG, and so my normal method of creating (source) view and then creating a (destination) materialized view on the destination server fails because of the LONG data type.

The solution I came up with works for one table, but for some reason does not work with a second table.

Here is the solution that *does* work.

This is my source table on db MYDB_PROD

CREATE TABLE S_NOTE_ACT (

  ROW_ID              VARCHAR2 (15)  NOT NULL,
  NOTE                LONG

)

I have created a function on MYDB_PROD that will convert the S_NOTE_ACT  . NOTE field from long to varchar. Here is the function.

FUNCTION long_to_varchar2 (V_ROW_ID VARCHAR2) RETURN VARCHAR2 IS varcharVal VARCHAR2(4000);

    varcharLength NUMBER;

     cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
     fetchIt PLS_INTEGER;

BEGIN
DBMS_SQL.PARSE
    (cur,'SELECT NOTE FROM S_NOTE_ACT_at_MYDB_PROD WHERE ROW_ID = ''' || V_ROW_ID || '''', DBMS_SQL.NATIVE);
     DBMS_SQL.DEFINE_COLUMN_LONG(cur,1);     fetchIt := DBMS_SQL.EXECUTE_AND_FETCH(cur);

     DBMS_SQL.COLUMN_VALUE_LONG(cur,1,4000,0,varcharVal,varcharLength);
     DBMS_SQL.CLOSE_CURSOR(cur);
     RETURN varcharVal;

  END; On my destination / data warehouse db MYDB_DW I can issue an insert or select statement which works just fine, such as:

insert into SIEBEL_NOTE_TEMP
select ROW_ID,

	LONG_TO_VARCHAR2(ROW_ID)
from 	S_NOTE_ACT_at_MYDB_PROD

WHERE [SOMEDATE is recent etc etc]

So far so good. All of this works nice. The problem comes in when I try to replicate this method for a second table on MYDB_PROD.

This is my 2nd source table on db MYDB_PROD

CREATE TABLE S_NOTE_OPTY (

  ROW_ID            VARCHAR2 (15)  NOT NULL,
  NOTE              LONG,

)

Here is the function I created on MYDB_PROD to translate data in this table from LONG to VARCHAR2. It's exactly the same, exact I changed the function name and the table from which I select.

FUNCTION long_to_vchar2_o (V_ROW_ID VARCHAR2) RETURN VARCHAR2 IS varcharVal VARCHAR2(4000);

    varcharLength NUMBER;

     cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
     fetchIt PLS_INTEGER;

BEGIN
DBMS_SQL.PARSE
    (cur,'SELECT NOTE FROM S_NOTE_OPTY_at_MYDB_PROD WHERE ROW_ID = ''' || V_ROW_ID || '''', DBMS_SQL.NATIVE);
     DBMS_SQL.DEFINE_COLUMN_LONG(cur,1);     fetchIt := DBMS_SQL.EXECUTE_AND_FETCH(cur);

     DBMS_SQL.COLUMN_VALUE_LONG(cur,1,4000,0,varcharVal,varcharLength);
     DBMS_SQL.CLOSE_CURSOR(cur);
     RETURN varcharVal;

  END; I am able to use this function successfully on the MYDB_PROD db using a select like

select ROW_ID

   ,LONG_TO_VCHAR2_O(ROW_ID)
from S_NOTE_OPTY_at_MYDB_PROD
WHERE row_id = '1-B49RRX'

However, when I try to run the exact same select statement on MYDB_DW I get ORA-00904 "LONG_TO_VCHAR_O": invalid identifier.

This message seems to indicate that I'm trying to select a column that doesn't exist.

Please send your wisdom in my general direction!

Thanks in advance.

• Sam

Received on Wed Oct 25 2006 - 16:54:37 CDT