Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Transparent Gateway to SQL Server
We are using Oracle's open server gateway for Microsoft's SQL Server.
We are able to create database links, select data across database links,
and insert explicit values across the database link.
Problem:
We have an Oracle stored procedure that inserts using variables into a SQL Server table.
The procedure call is:
begin
ins_msql_dept(998,2,3,'bogus','bogus','bogus',to_date('19980430','YYYYMMDD'),
'sdw'); end;
The error is the following:
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYSTEM.INS_MSQL_DEPT", line 40 ORA-06512: at line 1
The procedure source is:
create or replace procedure ins_msql_dept (p_departmentid IN NUMBER,p_RequestID IN NUMBER,
p_webid IN NUMBER, p_departmentname IN VARCHAR,p_description IN VARCHAR, p_DeptComment IN VARCHAR, p_DateModified IN DATE,p_ByWhom INVARCHAR) is
departmentid NUMBER(10), RequestID NUMBER(10), webid NUMBER(10), departmentname CHAR(25), description CHAR(255), DeptComment CHAR(255), DateModified DATE, ByWhom CHAR(50));
rec rec_typ;
string_date CHAR(10); tmp_string CHAR(200); tmp_string2 CHAR(200); tmp_string3 CHAR(200); p_count NUMBER(3); p_start_time CHAR(14); p_end_time CHAR(14); BEGIN SELECT TO_CHAR(sysdate,'MMDDYYYYHH24MISS') INTO p_start_time FROM DUAL;("departmentid","RequestID","webi
--string_date := to_date(p_DateModified,'YYYYMMDD');
--tmp_string := 'INSERT INTO department
tmp_string := 'INSERT INTO department'; tmp_string3 := tmp_string + '(departmentid,RequestID,webid,departmentname,'; tmp_string := tmp_string3;
tmp_string2 := 'description, Comment, DateModified, ByWhom) VALUES ('; tmp_string3 := tmp_string + tmp_string2; tmp_string := tmp_string3 + p_departmentid + ','; tmp_string3 := tmp_string + p_RequestID + ','; tmp_string := tmp_string3 + p_webid + ','; tmp_string3 := tmp_string + ''' + p_departmentname + '','; tmp_string := tmp_string3 + ''' + p_description + '','; tmp_string3 := tmp_string + ''' + p_DeptComment + '','; tmp_string := tmp_string3 + p_DateModified + ','; tmp_string3 := tmp_string + ''' + p_ByWhom + '')'; tmp_string := tmp_string3;
--INSERT INTO proc_timer VALUES ('SCHPROC',p_start_time,null);
GTW_SQL.GTWPASS_at_MSQL_LINK(tmp_string,'MSSQL6'); SELECT TO_CHAR(sysdate,'MMDDYYYYHH24MISS') INTO p_end_time FROM DUAL;
--UPDATE proc_timer
--SET end_time = p_end_time
--WHERE proc_name = 'SCHPROC';
COMMIT;
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed May 06 1998 - 12:26:20 CDT