Link existing files with connection objects, create folder structure with all existing objects

Yevon

Is it possible to "link" an object to an already existing file without needing to download one by one and performing a "download package" and "download package body"? Or just downloading whole database objects to the repository files with a predefined folder structure?

Christian.Shay -Oracle

Hi Yevon,

Unfortunately not. Are you trying to source control the files similar to a SQL Server Database Project?

Could you please let me know more details of what you have in mind, how you are planning to use this?.

You may want to look at liquibase as an option (free, open source) or a similar change management system.

Thanks,

Christian

Yevon

Hi Christian,

I'm trying to source control multiple existing oracle db's, including apex applications, with intention to apply ci/cd with gitlab pipelines and liquibase. I want to have every git repository organized by schemas and object types in folders, and being able to share common project settings between developers, like code snippets, code format rules, extensions etc. I want relatively small git respositories, with just the db connections they need. For example we have an internal db and an external db that have completely different projects on them, so I would use one vscode workspace for the internal db, and another workspace for the public db. Every workspace should have different connections.

I already have that file structure made with more than 3000 files on it. I just want to have full intellisense support in vscode over this file structure with:

• ctrl + click support in function names for easy navigation between package bodies.
• Refactor local vars and function names while just pressing f2.
• Autocomplete functions and their parameters referencing current package or packages in other schemas.
• Functions documentation like javadoc in package headers, that is shown while autocompleting function names.

• I was trying a few extensions for achieving this, but I'm having some small problems with every solution:
• Oracle official extension: I'm not being able to make it work with the existing file structure and keeping intellisense support. It works if I manually download packages from the connection object tree.
• Oradew for building automatically that file structure for existing databases. It works nicely and is capable of generating that file structure automatically, and having connections organized by instance types (DEV, STAGING, PROD). I know, if you have ci/cd this won't be needed really as you work locally and then you push your changes to the repository.
• Language PL/SQL for intellisense support: Works nicely and is capable of providing very nice intellisense support with the file structure provided with oradew extension, but it is not properly mantained anymore and has some bugs, for example it is unable to work with packages that has the word EDITIONABLE on them. It provides pldoc documentation for functions and is shown by intellisense when autocompleting functions.

Christian.Shay -Oracle

Thanks for all this. With regard to "source control multiple existing oracle db", I wonder if we integrated with liquibase better if that would help here? Eg, you can get the definition of a schema pretty easily in liquibase.

Yevon

Having an integration with sqlcl that already has liquibase functionality integrated would be awesome. The oracle vscode extension would detect the sqlcl system path trough existing environment variables, or allowing to setup a manual path to it. It would just call sqlcl liquibase commands for importing existing databases or deploying incremental changes. And it would be automatically in sync when you open an object with the existing connection object browser.

I have to check if latest version of sqlcl with liquibase is able to generate the initial changelog.xml for multiple schemas and checking dependencies for generating the changelog in the correct order, or if I have to generate it manually the first time. I think that latest version of liquibase allows to import multiple schemas at a time, but I haven't checked if does it properly.

I still don't know how will the apex team perform the announced git integration, but it would be nice having apex applications in the new json format in apex 22.1 inside the git repo, separated by component types.

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/comment/16834716#Comment_16834716

We wouldn't integrate with sqlcl actually, but we might do something similar to what sqlcl is doing from inside our extension. I am curious to hear if you think that would meet your needs. After you take a closer look, please let me know.

By the way, liquibase can generate that first changelog for you, and yes the objects are ordered with dependencies in mind.

Yevon

Yes, it would meet completely the needs a direct integration with liquibase for importing existing databases, always that resulting files have intellisense support on them.