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: