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

samthegreat_at_gmail.com wrote:
> 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

Is it possible that a long value in your table would be longer than 2000 characters?

Article on Asktom that discusses conversion from Long to Varchar2 (watch for word wrap):
http://asktom.oracle.com/pls/ask/f?p=4950:8:14515883036529155304::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:839298816582

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Oct 26 2006 - 07:07:38 CDT