1. Anthony Mancuso
  2. PowerBuilder
  3. Monday, 15 April 2019 16:39 PM UTC

Has anyone gone through a database conversion from Sybase 16 to Oracle 12c?

Can you off advice in:

1) The way you setup schemas in Oracle?

2) Stored Procedure Conversion?

3) Datawindows that use Stored Procedures as the source?

4) Any general advice or things to be aware of on the conversion journey?

 

Thanks

Tony

 

PB 2017 R2  Windows 7/10

 

Anthony Mancuso Accepted Answer Pending Moderation
  1. Wednesday, 17 April 2019 11:07 AM UTC
  2. PowerBuilder
  3. # 1
0
Votes
Undo

Thanks Everyone, we finally had some success yesterday with the data window issue.

Tony

Comment
There are no comments made yet.
Rodolfo Reyes Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2019 15:00 PM UTC
  2. PowerBuilder
  3. # 2
0
Votes
Undo

Hi,

Doing that has a little trick. You have to use an Oracle package.

 

Here they indicate how to do it:

https://www.appeon.com/support/documents/appeon_online_help/pb2019/connecting_to_your_database/ch11s06.html

 

 

 

Comment
you don't need to create a package for result sets, just use SYS_REFCURSOR. oracle 12c seems to have a new option as well (haven't tried it with PB).



for example:



create or replace procedure yourprocedure(arg1 varchar2, p_recordset OUT SYS_REFCURSOR)

AS

BEGIN



OPEN p_recordset FOR

Select * from mytable where mycolumn = arg1;

END;

------------------------



additional information on this:

https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets



https://oracle-base.com/articles/12c/implicit-statement-results-12cr1



  1. mike S
  2. Wednesday, 17 April 2019 13:46 PM UTC
There are no comments made yet.
Anthony Mancuso Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2019 10:57 AM UTC
  2. PowerBuilder
  3. # 3
0
Votes
Undo

Thanks for the help everyone. Our DBA has already done the migration of the Databases and Data. They seem to be having the most difficulty setting up the Roles properly. 

When I look at the database painter in PB, I can see all the schemas and the tables and stored procedures with in them.  When I try to construct a data window new from scratch, I try to associate the source of the data with a stored procedure. In the datawindow painter when you click on the Stored procedure as the source a small window opens up to let you pick the stored proc. When that window opens it is empty. The profile I connect with is set up to connect to the schema I want.

Any thoughts?

Tony

 

// Profile Developement - Redhat
SQLCA.DBMS = "O10 Oracle10g (10.1.0)"
SQLCA.LogPass = <********>
SQLCA.ServerName = "RED_HAT"
SQLCA.LogId = "AMANCUSO"
SQLCA.AutoCommit = False
SQLCA.DBParm = "PBCatalogOwner='PBUSER',DelimitIdentifier='No',Date=' ''''mm-dd-yyyy'''' ',DateTime=' ''''mm-dd-yyyy hh:mm:ss'''' ',Time=' ''''hh:mm:ss'''' ',Async=1"

Comment
In powerbuilder, connect to oracle as the schema owner
  1. mike S
  2. Tuesday, 16 April 2019 13:32 PM UTC
Hi,



Doing that has a little trick. You have to use an Oracle package.







Here they indicate how to do it:



https://www.appeon.com/support/documents/appeon_online_help/pb2019/connecting_to_your_database/ch11s06.html

  1. Rodolfo Reyes
  2. Tuesday, 16 April 2019 15:03 PM UTC
Thanks
  1. Anthony Mancuso
  2. Wednesday, 17 April 2019 11:05 AM UTC
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 15 April 2019 20:05 PM UTC
  2. PowerBuilder
  3. # 4
0
Votes
Undo

For your needs there is no difference between Oracle 10, 11, and 12. You will need to:
1. Copy the database(s)
2. Migrate the data
3. Update all stored procedures
4. Migrate all embedded SQL
5. Migrate all pipelines and other PB objects that interact with a database.
6. Test.
7. Test some more.
8. Really test the new setup.

It's not a difficult transition, but it is finicky because if you miss something it WILL come back and bite you inthe heinie in the future.


There is a lot of data available on this topic, here are two excellent links:

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/sybase/migrate_sybase_otn.htm

http://www.sqlines.com/sybase-asa-to-oracle/statements

 

Comment
There are no comments made yet.
Rodolfo Reyes Accepted Answer Pending Moderation
  1. Monday, 15 April 2019 18:48 PM UTC
  2. PowerBuilder
  3. # 5
0
Votes
Undo

Hi,

 

Oracle SQL Developer, brings in the tools menu, a tool to migrate a database to Oracle.

This tool tries to do the work of migrating tables, views, stored procedures, triggers, etc.

 

 

https://www.oracle.com/technetwork/database/migration/sybase-085571.html

 

 

Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Monday, 15 April 2019 18:40 PM UTC
  2. PowerBuilder
  3. # 6
0
Votes
Undo

a few random thoughts on how we support oracle as well as other databases:

 

as roland said, think of a schema as similar to a sybase database.  Create a schema owner that has all your database objects.   Only use that schema login to change those objects.

those objects must all be prefixed with the schema owner name OR an alias must be provided OR the easy way is to set the user default schema: after login, 

alter session set current_schema =

you have to grant access to all database objects.  create role(s) that you are granting access to and then give users that role.

in oracle an empty string '' will be saved as a NULL and not an empty string in your varchar2 columns.

 

you can write stored functions in oracle to add sybase functions.  for example you can write oracle functions for getdate(), left, right, substring, len, ...

 

 

 

 

 

 

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Monday, 15 April 2019 17:22 PM UTC
  2. PowerBuilder
  3. # 7
0
Votes
Undo

The biggest difference is that Oracle doesn't support separate databases per server so you have to use different schema owners. Sybase schema owner is dbo by default. You'll need to change all occurrences of dbo in your SQL to use the schema owner you have chosen (the Sybase database name would make sense) or to eliminate use of schema owner in the SQL.

There are several syntax differences including getdate:

SELECT getdate() INTO 

becomes

SELECT SYSDATE INTO

 

Oracle has a dummy table called DUAL which you can use to select functions into variables in script.

 

I don't know why anyone would use Oracle as it is much more expensive than any other database.

Comment
Hi Roland

You can create multiple database instances on one server with multiple schemas in each instance. As an example we have dev and test instances for our development.



Oracle is cheap, the free version is very cheap ;-)
  1. David Peace (Powersoft "The PowerBuilder & C# People")
  2. Wednesday, 17 April 2019 15:37 PM UTC
There are no comments made yet.
Matthew Balent Accepted Answer Pending Moderation
  1. Monday, 15 April 2019 17:00 PM UTC
  2. PowerBuilder
  3. # 8
0
Votes
Undo

I've been going through Sybase ASE to SQL Server migration for a while now.  

Make sure you check the errorhandling in your procs and triggers.

We had fields defined as CHAR which we changed to VARCHAR if appropriate.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.