SQLServer to Oracle under TD5.2

General discussion forum about all databases other than SqlBase.
arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

SQLServer to Oracle under TD5.2

Post by arbrenes » 05 Aug 2011, 16:48

We are migrating our backend from SQLServer to Oracle and need to know the code changes needed to call and handle Stored Procedures …

With SQL Server were using ODBSAL32.DLL functions like OdrExecuteProc to execute SPs. We now have TD5.2 connecting to Oracle 10g via ODBC.
Some sample code will be great. Any help is highly appreciated. Thanks !

Jeff Luther

Re: SQLServer to Oracle under TD5.2

Post by Jeff Luther » 05 Aug 2011, 23:34

NOTE, we have a native router for Oracle and I recall hearing in the past that it better to use the native router if Unify has that, like for Oracle, Informix, etc. At a minimum, I'd test both ways. See the ccod.pdf book for details and SQL.INI section [oragtwy].

beyond that, MS SS --> Oracle or any other DB brand is a matter, perhaps, of rewriting your SQL, especially if you've used MS SS-specific functions in the queries. You'll want to read the ccod.pdf Oracle section of details, including Stored Procedures.

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: SQLServer to Oracle under TD5.2

Post by arbrenes » 08 Aug 2011, 18:44

Thanks. For now we are using ODBC but could eventually move to the Oracle driver. We are still evaluating. From the connectivity standpoint the ccod.pdf document was helpful, but I am more concern about using odbsal32.apl funcions within TD.
Will funtions like OdrExecuteProc, OdrGetNextResults etc work the same as with MSSQL ? Any known issue ?

Jeff Luther

Re: SQLServer to Oracle under TD5.2

Post by Jeff Luther » 09 Aug 2011, 19:34

Odr... "Executes a stored procedure on Microsoft SQL Server." Those are MS SS specific and will not work with Oracle. Different DBs, different SQL add-ons, etc. Depending on what MS SS-specific features - macros, SPs, etc. -- you have been using you will need to recode those for use with Oracle. That will include DB table column type definitions, etc. Many in MS SS are different than in Oracle.

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: SQLServer to Oracle under TD5.2

Post by arbrenes » 23 Aug 2011, 16:09

Thanks. I am in the conversion process but hit a situation:

Having the following Stored Procedure in Oracle:
CREATE OR REPLACE PROCEDURE usp_GetSysParmValue
( v_parmDesc IN VARCHAR2 DEFAULT NULL ,
v_ParmType IN CHAR DEFAULT NULL ,
v_ParmValue OUT CHAR,
cv_1 IN OUT SYS_REFCURSOR )
AS
BEGIN
OPEN cv_1 FOR
SELECT ParmValue
FROM TSYSPARMLIST
WHERE ParmDesc = v_parmDesc
AND ParmType = v_ParmType;
END;
/
Then from my code, calling a function:

String: sParmDesc
String: sParmType
Receive String: sParmValue

If SqlOraPLSQLPrepare(hSql,'BEGIN usp_GetSysParmValue(:sParmDesc, :sParmType, :sParmValue); END; ')
Call SqlOraPLSQLExecute( hSql )

I got the following error:
----------------------------
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USP_GETSYSPARMVALUE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am not sure if it has to do with the SYS_REFCURSOR which I don't know how to handle in SqlOraPLSQLPrepare
Please help !

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: SQLServer to Oracle under TD5.2

Post by arbrenes » 24 Aug 2011, 18:20

Hi
anybody home ? I really need a hand ..

Jeff Luther

Re: SQLServer to Oracle under TD5.2

Post by Jeff Luther » 24 Aug 2011, 18:45

I don't have a ready-made solution for you, but if it were me I'd be sure to 'crack a few books' on SPs in Oracle, and in the case of the example you showed I *always* start simple and work up to the more complex.

In this case, start with an Oracle SP with 1 param and try that. Works? Try 2 params, etc. If it doesn't work, then research the problem/issue with that one param. I have actually seen people try to code and debug (aka 'figure out') how to do <something> new by incorporating it in their actual application. That amazes me! Why not write a small test case, I ask myself, get it understood and working, then incorporate it into that multi-meg app?

But that's how I code and how I'd attack your problem, especially because of this error:
PLS-00306: wrong number or types of arguments in call to 'USP_GETSYSPARMVALUE'
ORA-06550: line 1, column 7
I don't even see 7 columns!

Also, see TD Help for SqlOraPLSQLPrepare. Try that sample and see if you can get it to work.

Zephy

Re: SQLServer to Oracle under TD5.2

Post by Zephy » 25 Aug 2011, 08:35

You can not have "SYS_REFCURSOR" as parameter in procedure.
When select into procedure return only one row,
change your procedure like this:

Code: Select all

CREATE OR REPLACE PROCEDURE usp_GetSysParmValue
( v_parmDesc IN VARCHAR2 DEFAULT NULL ,
v_ParmType IN CHAR DEFAULT NULL ,
v_ParmValue OUT CHAR )
IS
BEGIN
 SELECT ParmValue
 into v_ParmValue
 FROM TSYSPARMLIST
 WHERE ParmDesc = v_parmDesc
 AND ParmType = v_ParmType;
END;
/

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: SQLServer to Oracle under TD5.2

Post by arbrenes » 26 Aug 2011, 04:14

Your suggestion works fine Zephy, in that particular situation. So the question now is how to read back the values from REFCURSOR in case of a more complex result set ? That is, using TD functions ?

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: SQLServer to Oracle under TD5.2

Post by arbrenes » 26 Aug 2011, 22:26

I am attaching a brief test case that will hopefully explains what I am pursuing
You do not have the required permissions to view the files attached to this post.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests