We have to access oracle exa platform and create tables and load data into oracle 10g, our version has issues of object length which is 30 char.
on Exadata we have created view which tables have more than 30 char.
on 10 G, we are able to select view and see the data. also when i insert it works but it fails with following statement.
DECLARE
start_time NUMBER;
end_time NUMBER;
i NUMBER;
--l_cnt number:=0;
CURSOR cursor1 IS
select rowid rid from "system"."V_CUSN_XXXXX"@dblink;
TYPE collection1 IS TABLE OF cursor1%ROWTYPE INDEX BY PLS_INTEGER;
item1 collection1;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
start_time:=dbms_utility.get_time;
OPEN cursor1;
LOOP
FETCH cursor1 BULK COLLECT INTO item1 limit 1000000;
EXIT WHEN item1.COUNT=0;
FORALL i IN item1.FIRST..item1.LAST save exceptions
insert into T_Rxxx values(item1(i).rid);
--COMMIT;
--l_cnt:=l_cnt+1;
--dbms_output.put_line('commit at '||to_char(sysdate,'ddmmhh24ss');
END LOOP;
CLOSE cursor1;
COMMIT;
end_time:=dbms_utility.get_time;
dbms_output.put_line('FORALL and BULK COLLECT Update: ' || TO_CHAR((end_time - start_time)/100) || ' seconds');
EXCEPTION
WHEN dml_errors THEN
IF cursor1%isopen THEN CLOSE cursor1;
END IF;
FOR i IN 1..sql%bulk_exceptions.COUNT LOOP
dbms_output.put_line(SQLERRM(-(sql%bulk_exceptions(i).error_code)));
dbms_output.put_line(sql%bulk_exceptions(i).error_index);
END LOOP;
COMMIT;
WHEN OTHERS THEN
dbms_output.put_line('Unrecognized error.');
RAISE;
END;
error comes :
ORA-04052: error occurred when looking up remote object MARD_RLSELFSERVICE.V_CUSN_COST_CENTRE_DIMT_F0@REDLAKE_ZEUSPDB.DE.BOSCH.COM
ORA-01948: identifier's name length (31) exceeds maximum (30)
statement works if i do like below :
select rowid rid from "system"."V_CUSN_XXXXX"@dblink;
Insert into temp table select rowid rid from "system"."V_CUSN_XXXXX"@dblink;
any clue?