1
  • I've installed Oracle Developer Tools for Visual Studio (ODTwithODAC1120320_32bit)
  • I've run the Database Configuration Assistant to enable the .NET layer on the oracle database

Yet when I try to deploy a simple function it fails to compile with the error 

C:\...\SQL Developer\Untitled2.sql
    Error(5,5): PLS-00201: identifier 'DBMS_CLR' must be declared
    Error(5,5): PL/SQL: Statement ignored
    Error(6,5): PL/SQL: Statement ignored
    Error(6,33): PLS-00201: identifier 'DBMS_CLR' must be declared
    Error(7,5): PL/SQL: Statement ignored
    Error(7,12): PLS-00201: identifier 'DBMS_CLR' must be declared

The compiled function (which just creates a connection and returns the inputted number as output)

CREATE  OR REPLACE LIBRARY "PROJECT12_DLL" AS '$ORACLE_HOME\bin\clr\Project12.dll';
/
CREATE OR REPLACE FUNCTION GETNUMBER wrapped 
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
14a 138
Zi7LUdjyS4D8cjiW2nS3rf/pLZwwg0zQNW0VfC8CAP7qXStE8/w9Xuy8PFb6oFkbDrDC1e3j
Zk34tXgr6lXtdGr+HE4Zd/dftKfDHWt9DKWOvIwrjachIBIMrDl6XnT0WbVLJcw8YQkbZdYa
0Kh//cLT8GNZJp7U3mCDP6HgYyjtUjR8y+za14OE3TiB6rLIjmWX/mBOnMneXSAUftbdmifX
ub8cMk85fnBRyoj8xsbrNIYzHWAYAQ+tnHJcGZCu+Di/YqeZx840cRj99kDVECydQMf5HLD2
XzUOJ43wC5sdUklUwA==

/

When I search on google for PLS-00201 it mentions to:

  • Either Oracle Database Extensions for .NET is not installed and configured properly
  • the .NET stored procedure has not been deployed correctly using the Oracle Deployment Wizard for .NET.

I think I've done both:

  • I've run the database configuration agent to "configure" .Net layer (not a lot of config possible, just a tickbox and a dropdown for the schema to install it under)
  • I've deployed the dll from .net (and also tried with the generated script from above)

Anyone got a clue what else I could try to make it work? (running oracle locally an a windows-7-x64 machine). thanks.

Peter De Decker
  • 31
  • 3
  • `DBMS_CLR` seems to not be deployed correctly. See this question: [oracle 11g r2 - deploying dbmsclr.plb as sysdba after ODAC installation](http://stackoverflow.com/questions/11959294/oracle-11g-r2-deploying-dbmsclr-plb-as-sysdba-after-odac-installation). Have you run the `%ORACLE_HOME%\RDBMS\ADMIN\dbmsclr.plb` script? – Vincent Malgrat Sep 25 '13 at 14:52
  • hi @Vincent Malgrat, yes I've tried that, no change, keep getting the error "dbms_clr" must be declared when I try to deploy the function. – Peter De Decker Sep 25 '13 at 15:14

1 Answers1

0

I seem to have the bad habit of answering my own questions, anyway in case someone else bumps into this issue, I solved the compiler error on DBMS_CLR must be declared by executing the following:

GRANT EXECUTE ON DBMS_CLR TO public;
GRANT EXECUTE ON DBMS_CLRTYPE TO public;
GRANT EXECUTE ON DBMS_CLRPARAMTABLE TO public;
Peter De Decker
  • 31
  • 3