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

Tony

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

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"

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

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

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, ...

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.

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.