CodeSmith Community
Your Code. Your Way. Faster!
Gallery » CodeSmith » Schema Providers » Oracle Schema Provider

Oracle Schema Provider

Previous | Next | View all files | View Slideshow
posted by gmcelhanon
12-19-2005
Downloads: 3,458
File size: 60.3kB
Views: 16,361
Oracle Schema Provider
This is the location of the most up-to-date version of the unofficial Oracle Schema Provider for CodeSmith.  Thanks goes out to Michael Toscano (aka VikingCoder) for starting a new Oracle provider based solely on the System.Data.OracleClient namespace.
 
To "install" the provider, review the included readme.txt.
 
RANDOM NOTES:
The CommandSchema objects returned from the provider will now have the following properties:
  • CS_ObjectType (i.e "PACKAGE")
  • CS_Status (i.e. "VALID", "INVALID")
  • CS_ObjectId (OBJECT_ID of PACKAGE where command resides)
  • CS_OverloadNumber (will be -1 if no overload exists, or a number > 0 if overloads are present)
  • CS_FullName (will contain the original "raw" command name prefixed with the owner).
  • CS_Name (will contain the original "raw" command name)
To support overloads in Oracle, the provider reports the Command.Name property as CUSTOMER.MODIFY[1], CUSTOMER.MODIFY[2], and so on.  What this means is that in order for you to write templates that will handle overloaded Oracle procedures correctly, you'll need to use the CS_Name extended property value of the CommandSchema object instead of the Name property.
 
I have added support for SQL Server-like extended properties through creation of a CODESMITH_EXTENDED_PROPERTIES table (which is automatically excluded from the list returned by GetTables, by the way).  This is a very powerful feature and I have already started putting it to great use for allowing fine grained control of certain aspects of my PL/SQL generation process (i.e. defining lookup tables and columns, being able to exclude certain columns from all procedure signatures, like CREATEDATE and MODIFIEDDATE, and to provide defaults for those values when they're excluded).  The possibilities are endless.

Comments

dmarines wrote Re: Oracle Schema Provider
on 12-21-2005 9:13 AM
Hi, I'm trying to figure out how to add custom properties to my Oracle table columns when I generate DAL code (for instance EXCLUDED=TRUE, or DEFAULT_IF_DBNULL=-1); Could anybody post a simple example of what I should add to CODESMITH_EXTENDED_PROPERTIES to support this? Thanks, Dinu
gmcelhanon wrote Re: Oracle Schema Provider
on 12-21-2005 11:31 AM
The key to understanding the extended properties table is in the GetExtendedProperties method implementation. For columns this information is as follows: CONTAINER_OBJECT_OWNER = The table's owner/schema OBJECT_NAME = tableName.columnName CODESMITH_SCHEMA_TYPE = "ColumnSchema" PROPERTY_NAME = "EXCLUDED" PROPERTY_VALUE = "true" CLR_TYPE = "System.Boolean" When you retrieve the extended property, you will receive a boolean value (returned as a generic "object'). You'll just need to cast it appropriately.
ZhuangYicheng wrote Re: Oracle Schema Provider
on 12-21-2005 9:04 PM
Hello, My CodeSmith version is 3.1.6, and I have put the SchemaExplorer.OracleNativeSchemaProvider.dll and SchemaExplorer.OracleNativeSchemaProvider.dll.config into the SchemaProviders folder, but in codeSmith sudio, I can't find the right provider type(the list didn't have the oracle provider type), why?
ejsmith wrote Re: Oracle Schema Provider
on 12-21-2005 10:30 PM
The file has now been updated with source code for the provider as well as binaries that should work with 3.1.x.
PackBjammin wrote Re: Oracle Schema Provider
on 03-01-2006 2:33 PM
For 3.2.x, you need to recompile with VS 2005 before it will show up in the SchemaProvider combo box.
Williamtel wrote Re: Oracle Schema Provider
on 03-04-2006 7:26 AM
When I try to recompile for 3.2 inVS 2005, it shows that a SchemaExplorer reference is missing. Where can I find this?
gmcelhanon wrote Re: Oracle Schema Provider
on 03-06-2006 1:41 PM
In a default installation... it's in C:\Program Files\CodeSmith\v3.0\AddIns
Williamtel wrote Re: Oracle Schema Provider
on 03-18-2006 9:34 AM
Thanks for the tip. When I remove the existing reference to schema explorer, and I try to add it again, it gives me the error: A reference to c:\program files\codesmith\v3.2\AddIns\Schema\SchemaExplorer.dll could not be added. This is not a valid assembly or Com component. I have downloed the .msi twice, and even went so far as to download the .zip and extracted the file. I am currently evaling the software; so I am using the demo version. Any ideas?
ejsmith wrote Re: Oracle Schema Provider
on 03-18-2006 10:38 AM
It sounds like you are trying to reference a .NET 2.0 assembly from .NET 1.x.
Williamtel wrote Re: Oracle Schema Provider
on 03-19-2006 7:30 AM
Yuck. That sure doesn't look like how I posted it. Try this instead. http://sourceforge.net/tracker/index.php?func=detail&aid=1453787&group_id=118735&atid=682442
ejsmith wrote Re: Oracle Schema Provider
on 03-20-2006 1:05 PM
I just updated the file with both CodeSmith 3.1 and CodeSmith 3.2 binaries.
underhil wrote Re: Oracle Schema Provider
on 03-22-2006 9:52 AM
I rebuilt the OracleNativeSchemaProvider.dll with VS2005 and placed the release .dll in the CodeSmith SchemaProviders folder. The Oracle provider showed up successfully in the Provider Type option of the database picker..., however, no output was produced when I ran the NetTiers.cst template. If I simply change providers to a SQL Server provider, everything is correctly generated. Using CodeSmith 3.2.6 rev 887.
jenad1kr wrote Re: Oracle Schema Provider
on 04-03-2006 3:29 PM
underhil: Any luck with your problem? I have the same exact scenario and I am not getting any output from oracle. any help is appreciated!! note: for some reason, the selected list of tables in SourceTables vanishes after "run" command.
chuckm wrote Re: Oracle Schema Provider
on 04-07-2006 1:55 PM
underhil,jenad1kr: I'm having same problem as well. I'm new to CodeSmith...is there some sort of debug option that'll lrt you trace into the generation?
kimblewu wrote Re: Oracle Schema Provider
on 06-28-2006 7:58 PM
how to run "Create Extended Properties Table.sql",i don't know where to excute the file.
klakkad wrote Re: Oracle Schema Provider
on 07-03-2006 7:00 PM
I have recompile OracleNativeSchemaProvider.dll with VS2005 But still can not See data source object with Oracle Provider
Pablo22g wrote Re: Oracle Schema Provider
on 07-11-2006 7:42 AM
Hi, I have used this oracle schema , then I created a connection to a dabase and I tried to add the schema to the GAC but it gave me that it should be a strong named, then I deleted this schema by removing it from the codesmith folder and now I m gettting the error below. And also I m not able to delete the connection to the database. I have tried to read the schema bo nothing is working, I have reinstalled codesmth over a dozen of times and nada same error ty for your help NullReferenceException (SchemaExplorer.Design.DataSourceManagerUI.b) v3.2.7.955 Username: ghorrap MachineName: P2056 Stacktrace: at SchemaExplorer.Design.DataSourceManagerUI.b() at SchemaExplorer.Design.DataSourceManagerUI..ctor() at e0.a(Object A_0, ToolBarButtonClickEventArgs A_1) at System.Windows.Forms.ToolBar.OnButtonClick(ToolBarButtonClickEventArgs e) at System.Windows.Forms.ToolBar.WmReflectCommand(Message& m) Message=Object reference not set to an instance of an object. Data=System.Collections.ListDictionaryInternal Assemblies: mscorlib, 2.0.0.0 CodeSmithStudio, 3.2.7.955 System, 2.0.0.0 System.Windows.Forms, 2.0.0.0 System.Drawing, 2.0.0.0 CodeSmith.Gui, 3.2.7.955 CodeSmith.Engine, 3.2.7.955 System.Runtime.Remoting, 2.0.0.0 System.Configuration, 2.0.0.0 System.Xml, 2.0.0.0 ActiproSoftware.SyntaxEditor, 3.1.202.0 ActiproSoftware.Shared, 1.0.75.0 System.Web, 2.0.0.0 ActiproSoftware.DockableWindow, 2.0.208.0 ActiproSoftware.TabStripPanel, 2.0.208.0 ActiproSoftware.WinUICore, 1.0.75.0 qdzvkvc-, 3.2.7.955 Interop.SHDocVw, 1.1.0.0 Accessibility, 2.0.0.0 gaau9tyt, 3.2.7.955 800kb9ov, 3.2.7.955 SchemaExplorer, 3.2.7.955 bpuvoldd, 3.2.7.955 SchemaExplorer.SqlSchemaProvider, 3.2.7.955 System.Data, 2.0.0.0
gmcelhanon wrote Re: Oracle Schema Provider
on 07-11-2006 9:28 PM
It is not clear to me what you are trying to accomplish. Why are you trying to GAC the schema provider? Why did you then delete it?
Julio_Izquierdo wrote Re: Oracle Schema Provider
on 07-12-2006 11:16 AM
When a package contains functions and procedures, is there any way to distinguish them?
allegecityrat wrote Re: Oracle Schema Provider
on 07-18-2006 4:37 PM
Can i anyhow let the Oracle Schema Provider get list of tables or views that are accessible by the user connected but that are not in the tablespace default for the user? Thank you in advance!
asegela wrote Re: Oracle Schema Provider
on 07-21-2006 8:32 AM
Hi underhil and jenad1kr, same problem as you. No code generation and the table list disappears. Is there anyone how can solve the problem?
onderorgun wrote Re: Oracle Schema Provider
on 09-08-2006 3:57 AM
Hi, I have just rebuit the dll and can access Oracle Schema. But when I open sample template and select associated database ( test ok) I receive the following error: System.ArgumentException: Column 'constraint_name' does not belong to table Table. at System.Data.DataRow.GetDataColumn(String columnName) at System.Data.DataRow.get_Item(String columnName) at SchemaExplorer.OracleNativeSchemaProvider.GetTablePrimaryKey(String connectionString, TableSchema table) at SchemaExplorer.TableSchema.get_PrimaryKey() at _CodeSmith.TypedDataSet_cst.__RenderMethod1(TextWriter writer, Control control) at CodeSmith.Engine.DelegateControl.Render(TextWriter writer) at CodeSmith.Engine.Control.RenderChildren(TextWriter writer) at CodeSmith.Engine.CodeTemplate.Render(TextWriter writer) at CodeSmith.Engine.CodeTemplate.RenderToString() at ej.s() What is the problem?
mitch.wheat wrote Re: Oracle Schema Provider
on 09-20-2006 9:37 PM
I have the same problem that if I choose the ORACLE provider no output is generated. No errors occur. I have noticed that the list of selected tables disappears after generation...
ejsmith wrote Re: Oracle Schema Provider
on 11-14-2006 2:31 PM
Updated to included binaries for CodeSmith 4.0.
fuwei.cool wrote Re: Oracle Schema Provider
on 11-20-2006 12:18 AM
Thank ejsmith Very much! I have connected to Oracle by your Provider successfully,but the Templates for oracle should written by myself!
eric.newton@wyndhamvo.com wrote Re: Oracle Schema Provider
on 12-19-2006 2:40 PM
What if I don't have the priviledge of creating CODESMITH_EXTENDED_PROPERTIES into the db? The code should have the ability to run without extended properties... they simply aren't that necessary...
naru wrote Re: Oracle Schema Provider
on 01-23-2007 3:09 PM
The tables list is empty after connecting to oracle database thru oracleschemaprovider. Let me know if I am doing something wrong.
zigaj wrote Re: Oracle Schema Provider
on 02-19-2007 9:45 AM
gbcelhanon, I have an update that also writes the extended properties to the db. Get back to me, so we can include it in an updated release.
mrspeaker wrote Re: Oracle Schema Provider
on 02-20-2007 4:28 PM
@Pablo22g: I had that same error - opening the "Manage Data Sources" in the schema explorer would pop up the "crash report" screen everytime. No amount of uninstalling/reinstalling would fix it. I FINALLY hunted it down with filemon. The datasources are stored in an xml file called SchemaExplorer.config in the Documents and Settings\Application Data\CodeSmith\... directory! Remove the Oracle datasource and it works.
ctkach@visalia.com wrote Re: Oracle Schema Provider
on 04-05-2007 12:23 PM
I just downloaded the most recent version of the OracleSchemaProvider to work in conjunction with CodeSmith 4.0 and the provided WilsonORMapper mapping.config template but receive an error when I try to generate the code. The error is below; System.NotSupportedException: Unexpected SchemaExplorer type 'MemberColumnSchema' encountered. Extended properties are not yet supported for this type. at SchemaExplorer.OracleNativeSchemaProvider.GetExtendedProperties(String connectionString, SchemaObjectBase schemaObject) at SchemaExplorer.SchemaObjectBase.get_ExtendedProperties() at _CodeSmith.MappingFile_cst.SetKeyType(TableSchema table, entity currentEntity) in c:\Documents and Settings\ctkach\My Documents\CodeSmith\Samples\v4.0\Templates\WilsonORMapper\MappingFile.cst:line 124 at _CodeSmith.MappingFile_cst.__RenderMethod1(TextWriter writer, Control control) in c:\Documents and Settings\ctkach\My Documents\CodeSmith\Samples\v4.0\Templates\WilsonORMapper\MappingFile.cst:line 362 at CodeSmith.Engine.DelegateControl.Render(TextWriter writer) at CodeSmith.Engine.Control.RenderChildren(TextWriter writer) at CodeSmith.Engine.CodeTemplate.Render(TextWriter writer) at CodeSmith.Engine.CodeTemplate.RenderToString() at CodeSmith.Gui.CodeTemplateGenerator.f(Object A_0, EventArgs A_1) Has anyone been able to get this combination to work? (i.e. the OracleSchemaProvider and the WilsonORMapper mapping.config template). I know this use to work in CodeSmith 3.2 but haven't had any luck with the new version. Any help would be appreciated. Thanks!
Nolan Egly wrote Re: Oracle Schema Provider
on 04-19-2007 10:10 AM
The method 'GetTableKeys' in the file 'OracleNativeSchemaProvider.cs' has an error in the query that gets the list of foreign keys for a table. If the table has a compound foreign key (the key consists of more than one column) the query returns the columns twice. Here's the query I used; it appears to fix the problem. If my fix doesn't have other problems itself it might be good to roll it into the official download. string sql = string.Format( @"select cons.constraint_name, cols.column_name, cols.position, rcols.table_name related_table_name, rcols.column_name related_column_name from all_constraints cons -- get column names associated with this key join all_cons_columns cols on cols.owner = cons.owner and cons.constraint_name = cols.constraint_name -- if you don't filter out rows with null positions, you'll get check constraints too and cols.position is not null -- get foreign table name and column names associated with this key join all_cons_columns rcols on rcols.owner = cons.owner and rcols.constraint_name = cons.r_constraint_name and rcols.position = cols.position where cons.owner = '{0}' and cons.table_name = '{1}' -- foreign keys only, no check constraints and cons.constraint_type = 'R' order by cons.constraint_name, cols.position", table.Owner, table.Name);
mpev wrote Re: Oracle Schema Provider
on 04-29-2007 10:49 PM
Hi, I've copied the OracleNativeSchemaProvider files to the SchemaProviders directory, and 'OracleNativeSchemaProvider' now appears in the list of available schemas when you go to create a new data source - *** but I can't seem to get a connection string to work with my DB. *** I'm running Oracle 10 Express, and have been using the following string... rovider=MSDAORA.1;Password=pswd;User ID=userid;Data Source=prod Can anyone please help? Thanks in advance - MP
dawie wrote re: Oracle Schema Provider
on 08-01-2007 8:37 AM
I've downloaded the latest native ORA schema prov, added the MemberColumnSchema case to the GetExtendedProperties method but still an exception shows saying "Unexpected SchemaExplorer type 'MemberColumnSchema' encountered" when I run the NetTiers.cst template. The strange thing is that in the stack trace it seems the exception is thrown from OracleSchemaProvider (and not OracleNativeSchemaProvider): System.NotSupportedException: Unexpected SchemaExplorer type 'MemberColumnSchema' encountered. Extended properties are not yet supported for this type. at SchemaExplorer.OracleSchemaProvider.GetExtendedProperties(.... The native provider is selected in the data source definition and even the SchemaExplorer.config refers to the correct provider: providerType="SchemaExplorer.OracleNativeSchemaProvider, SchemaExplorer.OracleNativeSchemaProvider, Version=1.0.2769.27719, Culture=neutral, PublicKeyToken=null" Any help will be appreciated. Regards
Copyright © 2008 CodeSmith Tools, LLC
Powered by Community Server (Commercial Edition), by Telligent Systems