Hi When i try to update a remote

[cyno]

Hi

When i try to update a remote database table inside a cursor using the dynamic sql iam getting invalid column name error.Iam passing the query into a variable.when i use the same in the local database it is working fine.Any idea why it is not working?

ORA-00904: invalid column name

 

[SantaMufasa]

Cyno,

Please copy and paste the screen display of the working version and the error version of the code.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:16 (31Dec03) GMT, 23:16 (30Dec03) Mountain Time)

 

[sbix]

Well Muf, I have something like that while trying to export a DB remotely.
It happens with Oracle 8.1.6 on Win-NT

 

[cyno]

Mufasa

The Error is : ORA-00904: invalid column name
and the version is 8.1.7.4.0.

cyno

 

[SantaMufasa]

Cyno,

Sorry for my not being more clear: I want to see your CODE (your PL/SQL instructions).

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:40 (31Dec03) GMT, 10:40 (31Dec03) Mountain Time)

 

[cyno]

SET SERVEROUTPUT ON

DECLARE HOST_NAM VARCHAR2(40);
LIN_K VARCHAR2(40);
SQL_STMT VARCHAR2(500);
SQL_STMT2 VARCHAR2(4000);
SQL_STMT3 VARCHAR2(4000);
SQL_STMT4 VARCHAR2(4000);
SQL_STMT6 VARCHAR2(500);
SQL_STMT5 VARCHAR2(500);

BEGIN

SELECT HOST_NAME INTO HOST_NAM FROM V$INSTANCE;

IF HOST_NAM ='server1' THEN
LIN_K :='LINK1';

ELSIF HOST_NAM ='server2' THEN

LIN_K :='Link2';

ELSIF HOST_NAM ='server3' THEN
LIN_K :='Link3';

ELSE
DBMS_OUTPUT.PUT_LINE('xxx');

END IF;

SQL_STMT := 'UPDATE bc'||LIN_K ||' SET status=''PROCESSING''WHERE status=''READY'' AND TRANS_types=''685''AND types=''O''';


EXECUTE IMMEDIATE SQL_STMT;

SQL_STMT2 := 'INSERT INTO OBC SELECT * FROM bc'||LIN_K||' WHERE TRANS_types=''685''AND types=''O'' AND status=''PROCESSING''';


EXECUTE IMMEDIATE SQL_STMT2;


SQL_STMT3 := ' INSERT INTO AA SELECT * FROM XX'||LIN_K||' A WHERE EXISTS(select''X'' from bc'||LIN_K||' B WHERE A.id=B.id AND B.TRANS_types=''685'' AND B.status= ''PROCESSING'' AND B.types=''O'')';

EXECUTE IMMEDIATE SQL_STMT3;

SQL_STMT4 := 'INSERT INTO BB SELECT * FROM YY'||LIN_K||' A WHERE EXISTS(SELECT ''X'' from bc'||LIN_K||' B WHERE A.id=B.id AND B.TRANS_types=''685''AND B.status=''PROCESSING'' AND B.types=''O'')';

EXECUTE IMMEDIATE SQL_STMT4;


DECLARE T1_CNT NUMBER :=0;
T2_CNT NUMBER :=0;
HD_CNT NUMBER :=0;
DT_CNT NUMBER :=0;



CURSOR EDI_685_CUR IS SELECT id,T1_CNT,T2_CNT FROM OBC WHERE status='PROCESSING' AND TRANS_types='685' AND types='O';

X_ID EDI_685_CUR%ROWtypes;

BEGIN

OPEN EDI_685_CUR;

LOOP

FETCH EDI_685_CUR INTO X_ID.id,
X_ID.T1_CNT,X_ID.T2_CNT;

EXIT WHEN EDI_685_CUR%NOTFOUND;

SELECT COUNT(*) INTO HD_CNT FROM
AA WHERE id=X_ID.
id;

SELECT COUNT(*) INTO DT_CNT FROM
BB WHERE id=X_ID.
id ;


IF (X_ID.T1_CNT=HD_CNT AND X_ID.T2_CNT=DT_CNT) THEN

BEGIN

SQL_STMT5 := 'UPDATE bc'||LIN_K||' SET status= ''PROCESSED'' WHERE status=''PROCESSING'' AND TRANS_types=''685'' AND types=''O'' AND id=X_ID.id';

EXECUTE IMMEDIATE SQL_STMT5;

UPDATE OBC SET
status='PROCESSED' WHERE id
=X_ID.id;

UPDATE AA SET SO_STATUS='NEW'
WHERE SO_STATUS='READY' AND id=
X_ID.id;

UPDATE BB SET SO_STATUS='NEW'
WHERE SO_STATUS='READY' AND id=
X_ID.id;

END;

ELSE

BEGIN

SQL_STMT6 := 'UPDATE bc'||LIN_K||' SET status= ''ERROR'' WHERE id=X_ID.id AND status=''PROCESSING''AND TRANS_types=''685''';

EXECUTE IMMEDIATE SQL_STMT6;

INSERT INTO AA_ERR SELECT *
FROM AA WHERE SO_STATUS='READY'
AND id=X_ID.id;

INSERT INTO BB_ERR SELECT *
FROM BB WHERE SO_STATUS='READY'
AND id=X_ID.id;

DELETE FROM AA WHERE id=
X_ID.id;

DELETE FROM BB WHERE id=
X_ID.id;

UPDATE OBC SET
status='ERROR'
WHERE id=X_ID.id;


END;

END IF;

END LOOP;

COMMIT;

CLOSE EDI_685_CUR;

END;

EXCEPTION

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The Error is : '|| SQLERRM );
ROLLBACK WORK;
END;
/

 

[carp]

I might suggest the following modification to your code:

IF HOST_NAM ='server1' THEN
LIN_K :='@Link1';

ELSIF HOST_NAM ='server2' THEN

LIN_K :='@Link2';

ELSIF HOST_NAM ='server3' THEN
LIN_K :='@Link3';

ELSE
DBMS_OUTPUT.PUT_LINE('xxx');

END IF;

As currently written, your SQL statements would look like:

"INSERT INTO AA SELECT * FROM XXLink2 ... "
which, of course, makes no sense to the database!

When using dynamic SQL and running into inexplicable errors, I find it useful to have dbms_output write out the statements I'm trying to pass as commands. It's amazing how many times we leave out spaces, "@", etc!

Elbert, CO
1129 MST

 

[SantaMufasa]

We just call Carp, "Quick-Draw" (the fastest gun in the West). Yes, I agree with Carp...If "LIN_K" holds the contents of a database link, then you are missing the "@"; without the "@", it is looking on the current, local database for tables named, "BCLINK1", "XXLINK1", et cetera.

I'm still puzzled, however, why your code actually works when running against the local database. Hmmmm.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:37 (31Dec03) GMT, 11:37 (31Dec03) Mountain Time)

 

[cyno]

Soory,I didnt post it in the above code.I already had @link in my code.

 

[carp]

Cyno -
No, really. When we ask to see your code, we really want to see the code you're having a problem with! Please post the REAL code!

Elbert, CO
1157 MST

 

[cyno]

Hi Elbert

HERE U GO...........

SET SERVEROUTPUT ON

DECLARE HOST_NAM VARCHAR2(40);
LIN_K VARCHAR2(40);
SQL_STMT VARCHAR2(500);
SQL_STMT2 VARCHAR2(4000);
SQL_STMT3 VARCHAR2(4000);
SQL_STMT4 VARCHAR2(4000);
SQL_STMT6 VARCHAR2(500);
SQL_STMT5 VARCHAR2(500);

BEGIN

SELECT HOST_NAME INTO HOST_NAM FROM V$INSTANCE;

IF HOST_NAM ='server1' THEN
LIN_K :='@LINK1';

ELSIF HOST_NAM ='server2' THEN

LIN_K :='@link2';

ELSIF HOST_NAM ='server3' THEN
LIN_K :='@link3';

ELSE
DBMS_OUTPUT.PUT_LINE('xxxxx');

END IF;

SQL_STMT := 'UPDATE BC'||LIN_K ||' SET STATUS=''PROCESSING''WHERE STATUS=''READY'' A
ND TRANS_TYPE=''865''AND IO=''O''';


EXECUTE IMMDDATE SQL_STMT;

SQL_STMT2 := 'INSERT INTO obc SELECT * FROM BC'||LIN_K||' WHERE TRANS_TYPE=
''865''AND IO=''O'' AND STATUS=''PROCESSING''';


EXECUTE IMMDDATE SQL_STMT2;


SQL_STMT3 := ' INSERT INTO 865_PRIMARY SELECT * FROM XXINT_WSH_SOO_TXN_HEADERS_V'||LIN_K||' A WHERE EXISTS(select''X'' f
rom BC'||LIN_K||' B WHERE A.ID=B.ID AND B.TRANS_TYPE=''865'' AND B.STATUS= ''PROCES
SING'' AND B.IO=''O'')';

EXECUTE IMMDDATE SQL_STMT3;

SQL_STMT4 := 'INSERT INTO 865_SECONDARY SELECT * FROM XXINT_WSH_SOO_TXN_LINES_V'||LIN_K||' A WHERE EXISTS(SELECT ''X'' from
BC'||LIN_K||' B WHERE A.ID=B.ID AND B.TRANS_TYPE=''865''AND B.STATUS=''PROCESSING''
AND B.IO=''O'')';

EXECUTE IMMDDATE SQL_STMT4;


DECLARE T1_CNT NUMBER :=0;
T2_CNT NUMBER :=0;
HD_CNT NUMBER :=0;
DT_CNT NUMBER :=0;



CURSOR DD_865_CUR IS SELECT ID,T1_CNT,T2_CNT
FROM obc
WHERE STATUS='PROCESSING'
AND TRANS_TYPE='865' AND IO='O';

BAT_ID DD_865_CUR%ROWTYPE;

BEGIN

OPEN DD_865_CUR;

LOOP

FETCH DD_865_CUR INTO BAT_ID.ID,
BAT_ID.T1_CNT,BAT_ID.T2_CNT;

EXIT WHEN DD_865_CUR%NOTFOUND;

SELECT COUNT(*) INTO HD_CNT FROM
865_PRIMARY WHERE ID=BAT_ID.
ID;

SELECT COUNT(*) INTO DT_CNT FROM
865_SECONDARY WHERE ID=BAT_ID.
ID ;


IF (BAT_ID.T1_CNT=HD_CNT AND BAT_ID.T2_CNT=DT_CNT) THEN

BEGIN

SQL_STMT5 := 'UPDATE BC'||LIN_K||' SET STATUS= ''PROCESSED'' WHERE STATUS=''PROCESSING
'' AND TRANS_TYPE=''865'' AND IO=''O'' AND ID=BAT_ID.ID';

EXECUTE IMMDDATE SQL_STMT5;

UPDATE obc SET
STATUS='PROCESSED' WHERE ID
=BAT_ID.ID;

UPDATE 865_PRIMARY SET SO_STATUS='NEW'
WHERE SO_STATUS='READY' AND ID=
BAT_ID.ID;

UPDATE 865_SECONDARY SET SO_STATUS='NEW'
WHERE SO_STATUS='READY' AND ID=
BAT_ID.ID;

END;

ELSE

BEGIN

SQL_STMT6 := 'UPDATE BC'||LIN_K||' SET STATUS= ''ERROR'' WHERE ID=BAT_ID.ID AND
STATUS=''PROCESSING''AND TRANS_TYPE=''865''';

EXECUTE IMMDDATE SQL_STMT6;

INSERT INTO 865_PRIMARY_ERR SELECT *
FROM 865_PRIMARYWHERE SO_STATUS='READY'
AND ID=BAT_ID.ID;

INSERT INTO 865_SECONDARY_ERR SELECT *
FROM 865_SECONDARY WHERE SO_STATUS='READY'
AND ID=BAT_ID.ID;

DELETE FROM 865_PRIMARY WHERE ID=
BAT_ID.ID;

DELETE FROM 865_SECONDARY WHERE ID=
BAT_ID.ID;

UPDATE obc SET
STATUS='ERROR'
WHERE ID=BAT_ID.ID;


END;

END IF;

END LOOP;

COMMIT;

CLOSE DD_865_CUR;

END;

EXCEPTION

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The Error is : '|| SQLERRM );
ROLLBACK WORK;
END;
/

 

[cyno]

the spelling 'execute immediate' is also correct

 

[SantaMufasa]

Cyno,

(BTW, "Elbert, CO" is the town in Colorado where Carp lives.)

How did you convince Oracle to accept table names such as "865_PRIMARY" and "865_SECONDARY" without using double quotes. Are you sure this code has ever worked? This is all just a bit "squirrelly" to me.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:15 (31Dec03) GMT, 14:15 (31Dec03) Mountain Time)

 

[carp]

Cyno -
I've embedded dbms_output lines in your code so we can see if we can isolate where the problem is. With just a cursory look, I can see a couple of places where you've left out spaces when building your SQL_STMTs. Unfortunately, since your cut/paste doesn't seem to be working, I can't tell if this is actually a problem or just a typo in your post. Also, note that I've only inserted a few statements. I'll leave it to you to correct the typos such as "EXECUTE IMMDDATE".

SET SERVEROUTPUT ON SIZE 1000000

DECLARE HOST_NAM VARCHAR2(40);
LIN_K VARCHAR2(40);
SQL_STMT VARCHAR2(500);
SQL_STMT2 VARCHAR2(4000);
SQL_STMT3 VARCHAR2(4000);
SQL_STMT4 VARCHAR2(4000);
SQL_STMT6 VARCHAR2(500);
SQL_STMT5 VARCHAR2(500);

BEGIN
SELECT HOST_NAME INTO HOST_NAM FROM V$INSTANCE;
IF HOST_NAM ='server1' THEN
LIN_K :='@Link1';
ELSIF HOST_NAM ='server2' THEN
LIN_K :='@Link2';
ELSIF HOST_NAM ='server3' THEN
LIN_K :='@Link3';
ELSE
DBMS_OUTPUT.PUT_LINE('xxxxx');
END IF;
SQL_STMT := 'UPDATE BC'||LIN_K ||' SET STATUS=''PROCESSING''WHERE STATUS=''READY'' A
ND TRANS_TYPE=''865''AND IO=''O''';
dbms_output.put_line('SQL_STMT: '||sql_stmt);
EXECUTE IMMDDATE SQL_STMT;

SQL_STMT2 := 'INSERT INTO obc SELECT * FROM BC'||LIN_K||' WHERE TRANS_TYPE=
''865''AND IO=''O'' AND STATUS=''PROCESSING''';
dbms_output.put_line('SQL_STMT2: '||sql_stmt2);
EXECUTE IMMDDATE SQL_STMT2;

SQL_STMT3 := ' INSERT INTO 865_PRIMARY SELECT * FROM XXINT_WSH_SOO_TXN_HEADERS_V'||LIN_K||' A WHERE EXISTS(select''X'' f
rom BC'||LIN_K||' B WHERE A.ID=B.ID AND B.TRANS_TYPE=''865'' AND B.STATUS= ''PROCES
SING'' AND B.IO=''O'')';
dbms_output.put_line('SQL_STMT3: '||sql_stmt3);
EXECUTE IMMDDATE SQL_STMT3;

SQL_STMT4 := 'INSERT INTO 865_SECONDARY SELECT * FROM XXINT_WSH_SOO_TXN_LINES_V'||LIN_K||' A WHERE EXISTS(SELECT ''X'' from
BC'||LIN_K||' B WHERE A.ID=B.ID AND B.TRANS_TYPE=''865''AND B.STATUS=''PROCESSING''
AND B.IO=''O'')';
dbms_output.put_line('SQL_STMT4: '||sql_stmt4);
EXECUTE IMMDDATE SQL_STMT4;

DECLARE T1_CNT NUMBER :=0;
T2_CNT NUMBER :=0;
HD_CNT NUMBER :=0;
DT_CNT NUMBER :=0;

CURSOR DD_865_CUR IS SELECT ID,T1_CNT,T2_CNT
FROM obc
WHERE STATUS='PROCESSING'
AND TRANS_TYPE='865' AND IO='O';

BAT_ID DD_865_CUR%ROWTYPE;
BEGIN
OPEN DD_865_CUR;
LOOP
FETCH DD_865_CUR INTO BAT_ID.ID,
BAT_ID.T1_CNT,BAT_ID.T2_CNT;
EXIT WHEN DD_865_CUR%NOTFOUND;
SELECT COUNT(*) INTO HD_CNT FROM
865_PRIMARY WHERE ID=BAT_ID.
ID;
SELECT COUNT(*) INTO DT_CNT FROM
865_SECONDARY WHERE ID=BAT_ID.
ID ;
IF (BAT_ID.T1_CNT=HD_CNT AND BAT_ID.T2_CNT=DT_CNT) THEN
BEGIN
SQL_STMT5 := 'UPDATE BC'||LIN_K||' SET STATUS= ''PROCESSED'' WHERE STATUS=''PROCESSING
'' AND TRANS_TYPE=''865'' AND IO=''O'' AND ID=BAT_ID.ID';
dbms_output.put_line('SQL_STMT5: '||sql_stmt5);
EXECUTE IMMDDATE SQL_STMT5;

UPDATE obc SET
STATUS='PROCESSED' WHERE ID
=BAT_ID.ID;

UPDATE 865_PRIMARY SET SO_STATUS='NEW'
WHERE SO_STATUS='READY' AND ID=
BAT_ID.ID;

UPDATE 865_SECONDARY SET SO_STATUS='NEW'
WHERE SO_STATUS='READY' AND ID=
BAT_ID.ID;

END;

ELSE

BEGIN

SQL_STMT6 := 'UPDATE BC'||LIN_K||' SET STATUS= ''ERROR'' WHERE ID=BAT_ID.ID AND
STATUS=''PROCESSING''AND TRANS_TYPE=''865''';
dbms_output.put_line('SQL_STMT6: '||sql_stmt6);
EXECUTE IMMDDATE SQL_STMT6;

INSERT INTO 865_PRIMARY_ERR SELECT *
FROM 865_PRIMARYWHERE SO_STATUS='READY'
AND ID=BAT_ID.ID;

INSERT INTO 865_SECONDARY_ERR SELECT *
FROM 865_SECONDARY WHERE SO_STATUS='READY'
AND ID=BAT_ID.ID;

DELETE FROM 865_PRIMARY WHERE ID=
BAT_ID.ID;

DELETE FROM 865_SECONDARY WHERE ID=
BAT_ID.ID;

UPDATE obc SET
STATUS='ERROR'
WHERE ID=BAT_ID.ID;


END;

END IF;

END LOOP;

COMMIT;

CLOSE DD_865_CUR;

END;

EXCEPTION

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The Error is : '|| SQLERRM );
ROLLBACK WORK;
END;
/

Please insert these dbms_output lines in your code, run it, and let us know what the results are.

Thanks!

Elbert, CO
1418 MST

 

[carp]

Excellent catch on the table names, Sandy!

Cyno - if you defined the table names with double quotes, then you need to embed the table names in double quotes within your other SQL as well.

 

[cyno]

Elbert

when i use dbms_output, iam getting the following error.
I tried using substring function to print 250 charcaters at one time.Even then its not working.

ORU-10028: Line length overflow, limit of 255 bytes per line

 

[carp]

Did you try something like:

dbms_output.put_line(substr('SQL_STMT2: '||sql_stmt2, 1,250));

?

If you did and its still didn't work, try a smaller number (e.g., 225). At least it will identify which statement blew up and give you a pretty good fragment to work with. In a pinch, you could even break it up into chunks:

dbms_output.put_line(substr('SQL_STMT2: '||sql_stmt2, 1,200));
dbms_output.put_line(substr('SQL_STMT2: '||sql_stmt2, 201,200));

 

[cyno]

Hi carp

The code is not working for the following statement.


SQL_STMT5 :=' UPDATE XXINT_PROCESS_BATCH_CONTROL'||LIN_K||' SET BATCH_STATUS= ''PROCESSED'' WHERE BATCH_ID=BAT_ID.BATCH_ID';

DBMS_OUTPUT.PUT_LINE(SQL_STMT5);

EXECUTE IMMEDIATE SQL_STMT5;

The Error is : ORA-00904: invalid column name

I think its not concatenating the LIN_K(variable) with table name.If i use

dbms_output.put_line(LIN_K)

after this EXECUTE IMMEDIATE SQL_STMT5; statement its dispalying the string in the variable ie(@link).

I still didnt understand where the problem is .....

 

[carp]

Please post the actual output (not your synopsis, not an approximate facsimile - the actual output).
Thank you.

 

[cyno]

host_nam varchar2(40);
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-00904: invalid column name
ORA-06512: at line 114


Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-02067: transaction or savepoint rollback required