An Oracle ASSEMBLY

oracleplsql

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?

Best Answer

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.