Tech

Insert into select to remote database using DB_link

admin December 11, 2021

I am trying to update the remote database table using DB_LINKS, where I get error

SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Case Scenario –

From local database – connect as sysdba
```
sqlplus / as sysdba
```

Update remote database with values fetched from local database

INSERT INTO username.D_BackupStatus@db_link_name
(SELECT    ''''
     (SELECT DECODE (db_unique_name, NULL, name, db_unique_name)
          FROM v$database)
     ''','''
     TO_CHAR (START_TIME, 'dd-mon-rr hh24:mi:ss')
     ''','''
     TO_CHAR (END_TIME, 'dd-mon-rr hh24:mi:ss')
     ''','''
     time_taken_display
     ''','''
     TRIM (STATUS)
     ''','''
     INPUT_TYPE
     ''','''
     DECODE (TO_CHAR (start_time, 'd'),
               1, 'Sunday',
               2, 'Monday',
               3, 'Tuesday',
               4, 'Wednesday',
               5, 'Thursday',
               6, 'Friday',
               7, 'Saturday')
     ''','''
     (SELECT host_name FROM v$instance)
     ''''
FROM V$RMAN_BACKUP_JOB_DETAILS
WHERE     TO_DATE (TO_CHAR (START_TIME, 'dd-mon-rr'), 'dd-mon-rr') >=
        TO_DATE (TO_CHAR (SYSDATE - 6, 'dd-mon-rr'), 'dd-mon-rr')
    AND input_type IN
           ('DB FULL', 'DB INCR')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
           order      by   START_TIME);

Output

Error starting at line : 1 in command -
insert into username.D_BackupStatus@db_link_name 
(select '''' 
(select decode(db_unique_name,NULL,name,db_unique_name) from v$database)''','''
to_char(START_TIME,'dd-mon-rr hh24:mi:ss')  ''','''to_char(END_TIME,'dd-mon-rr hh24:mi:ss') 
 ''','''  time_taken_display ''',''' trim(STATUS)  ''','''  INPUT_TYPE''','''
decode(to_char(start_time, 'd'), 1, 'Sunday', 2, 'Monday',3, 'Tuesday', 4, 'Wednesday',5, 'Thursday', 6,
'Friday',7, 'Saturday')''','''(select host_name from v$instance) ''''
from V$RMAN_BACKUP_JOB_DETAILS
where to_date(to_char(START_TIME,'dd-mon-rr'),'dd-mon-rr')>=to_date(to_char(sysdate-6,'dd-mon-rr'),'dd-mon-rr')
and input_type in ('DB FULL','DB INCR')
order by START_TIME)

Error at Command Line : 11 Column : 1
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action: