Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Transparent Gateway to SQL Server

Transparent Gateway to SQL Server

From: <shermanej_at_eccic.com>
Date: Wed, 06 May 1998 11:26:20 -0600
Message-ID: <6iq2vc$86s$1@nnrp1.dejanews.com>


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 IN
VARCHAR) is
TYPE rec_typ IS RECORD (
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;


--string_date := to_date(p_DateModified,'YYYYMMDD');

--tmp_string := 'INSERT INTO department
("departmentid","RequestID","webi
d","departmentname",';

        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;

END ins_msql_dept;
/

-----== 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US