7

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?

1

2 Answers 2

3

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.

1
  • 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? Mar 23, 2015 at 20:52
2

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. :)

3
  • 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. Mar 23, 2015 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 6, 2022 at 6: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 learn.microsoft.com/en-us/sql/relational-databases/… but eventually it simply creates a library object.
    – Dr Y Wit
    Jan 6, 2022 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.