Database Links and Stored Procedures 1

[THoey]

I am trying to create a stored procedure to pull data from an external DB nightly. I first wrote the code to query this DB through a database link. Once I got that refined, I converted this to a stored procedure, but when I run that procedure I get an ORA-00911 Invalid Character error. I am thinking it is due to the "@" used in the DB link. I have even tried it in a PL/SQL block and see the same error.

Here is the code:

declare
   sql_msg VARCHAR2(2000) := NULL;
begin
   sql_msg := 'insert into call_history_mw (tn, cust_code, call_date, ao, call_id) ';
   sql_msg := sql_msg || 'select gdataex.wtn, ';
   sql_msg := sql_msg || 'gdataex.cust_code, ';
   sql_msg := sql_msg || 'NEW_TIME(TO_DATE(''01-JAN-1970'',''DD-MON-YYYY'') + (gcdr.sttime / 86400),''GMT'',''PST''), ';
   sql_msg := sql_msg || '''DFLT'', ';
   sql_msg := sql_msg || 'gdataex.connid ';
   sql_msg := sql_msg || 'from gdataex@sfrp.sbc.com, gcdr@sfrp.sbc.com ';
   sql_msg := sql_msg || 'where gdataex.wtn is not null and ';
   sql_msg := sql_msg || 'gdataex.cust_code is not null and ';
   sql_msg := sql_msg || 'gcdr.sttime is not null and ';
   sql_msg := sql_msg || 'gdataex.connid is not null and ';
   sql_msg := sql_msg || 'gdataex.connid = gcdr.connid and ';
   sql_msg := sql_msg || 'trunc(NEW_TIME(TO_DATE(''01-JAN-1970'',''DD-MON-YYYY'') + (gcdr.sttime / 86400),''GMT'',''PST'')) = ';
   sql_msg := sql_msg || 'trunc(sysdate-1);';
   EXECUTE IMMEDIATE sql_msg;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line('ERROR:'||sqlerrm);
END;

ERROR:ORA-00911: invalid character

PL/SQL procedure successfully completed.

Any suggestions?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[BJCooperIT]

Instead of

'trunc(sysdate-1);';

try

'trunc(sysdate-1)';

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[THoey]

Thanks! I think that resolved it.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...