What is an Oracle ASSEMBLY?

Question

What is an Oracle ASSEMBLY, in the context of Oracle SQL?

It's listed in GV$SQLCOMMAND. Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of MERGE.

   INST_ID COMMAND_TYPE COMMAND_NAME             CON_ID
---------- ------------ -------------------- ----------
         1          215 DROP ASSEMBLY                 0
         1          216 CREATE ASSEMBLY               0
         1          217 ALTER ASSEMBLY                0

The commands work although I'm not sure what they are doing. Oddly the syntax requires a /, implying these objects may contain PL/SQL.

SQL> create or replace assembly some_assembly is 'some string';
  2  /

Assembly created.

SQL> select * from all_assemblies;

OWNER           ASSEMBLY_NAME   FILE_SPEC       SECURITY_L IDENTITY        STATUS
--------------- --------------- --------------- ---------- --------------- -------
JHELLER         SOME_ASSEMBLY   some string     SAFE                       VALID

The data dictionary views work and are documented: ALL_ASSEMBLIES.

There are some Oracle products that use assemblies but I don't know if they tie directly to this SQL statement. For example, there is the Oracle® Fusion Middleware Using Oracle Virtual Assembly Builder, Database assemblies in Oracle Enterprise Manager, and Database Extensions for .NET (which involve assemblies).

What is an ASSEMBLY? A deprecated command? An unimplemented or undocumented feature? Or a real command that's documented somewhere I haven't looked?

Seems to have been new in Oracle 11g. See https://docs.oracle.com/cd/B28359_01/server.111/b28320/whatsnew.htm#CJAIIJAG — Colin 't Hart, Mar 23 '15 at 19:53

Philᵀᴹ · Answer 1 · 2015-03-23T20:26:55.327

There is absolutely nothing about this on the Internet - even the Oracle docs regarding the %_ASSEMBLIES views don't actually state what they show.

However, if you go digging around in $ORACLE_HOME/rdbms/admin you'll see the following reference to the data dictionary views (which sit on assembly$:

remark
remark  FAMILY "ASSEMBLIES"
remark
remark  Views for showing information about PL/SQL Assemblies:
remark  USER_ASSEMBLIES, ALL_ASSEMBLIES and DBA_ASSEMBLIES
remark
create or replace view USER_ASSEMBLIES
(ASSEMBLY_NAME, FILE_SPEC, SECURITY_LEVEL, IDENTITY, STATUS)
.......

This definitely says to me that these views, and the associated CREATE/DROP/ALTER ASSEMBLY commands, are for .NET stored procedure support.

I agree with you, yet I don't see the word .NET anywhere in the docs for this. Can someone with access to a Windows machine please deploy a demo .NET class into Oracle and verify that the views *_ASSEMBLIES then return a record? — Colin 't Hart, Mar 23 '15 at 20:52

score 1 · Answer 2 · answered Mar 23 '15 at 20:17

1

I think this page has what you are looking for -

Assembly: Assembly is Microsoft's term for the module that is created when a DLL or .EXE is complied by a .NET compiler.

If I understand it correctly it is the .NET equivalent for Java's "class files".

Starting 11g, you can develop and deploy .NET procedures and functions in Oracle database.

Here is a link describing the steps to do that -

http://docs.oracle.com/cd/E20213_01/doc/win.112/e17724/devdeploy.htm

I didn't know this was called assemblies until now. So thanks for asking this question. :)

answered Mar 23 '15 at 20:17

ruudvan

156
4

Can someone verify that after doing such a deployment, the views *_ASSEMBLIES return records? Until such time, both this answer and @tblPhil's above are still "just" extremely likely assumptions. – Colin 't Hart Mar 23 '15 at 20:49
@Colin'tHart, after deploying a demo Oracle simply creates a library and a function (i.e. very similar approach to C external procedures). The difference is that the function uses dbms_clr under the hood and dbms_clr.executevarcharfunction in particular to call the function from DLL. – Dr Y Wit Jan 06 '22 at 06:52
No new rows appear in dba_assemblies. After deploying .Net assembly the new objects in DB are the FUNCTION and the LIBRARY. I presume initially Oracle planed to implement something similar to https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/getting-started-with-clr-integration?view=sql-server-ver15 but eventually it simply creates a library object. – Dr Y Wit Jan 06 '22 at 07:00

What is an Oracle ASSEMBLY?

2 Answers2