Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201: identifier 'CTX_DDL' must be declared
PLS-00201: identifier 'CTX_DDL' must be declared [message #672674] Tue, 23 October 2018 07:14 Go to next message
Sania Fatimi
Messages: 1
Registered: October 2018
Junior Member
Hi friends,

I am trying to create a procedure as below, but getting error as -PLS-00201: identifier 'CTX_DDL' must be declared

CREATE or replace PROCEDURE try_ctx
IS
BEGIN
CTX_DDL.CREATE_POLICY ('test_policy', 'CTXSYS.AUTO_FILTER');
END;

I am unable to understand, how to resolve this error?Please help.

Thankyou
Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672675 is a reply to message #672674] Tue, 23 October 2018 07:25 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Sania Fatimi wrote on Tue, 23 October 2018 05:14
Hi friends,

I am trying to create a procedure as below, but getting error as -PLS-00201: identifier 'CTX_DDL' must be declared

CREATE or replace PROCEDURE try_ctx
IS
BEGIN
CTX_DDL.CREATE_POLICY ('test_policy', 'CTXSYS.AUTO_FILTER');
END;

I am unable to understand, how to resolve this error?Please help.

Thankyou

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Which schema owns CTX_DDL?
Which schema is executing code above?

Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672676 is a reply to message #672675] Tue, 23 October 2018 07:27 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
You need to be granted execute on the ctxsys.ctx_ddl package.

--ps: the link BS gave you for the use of [code] tags is broken, try this one: How to use code tags and make your code easier to read

[Updated on: Tue, 23 October 2018 07:29]

Report message to a moderator

Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672677 is a reply to message #672676] Tue, 23 October 2018 07:30 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
John Watson wrote on Tue, 23 October 2018 08:27
You need to be granted execute on the ctxsys.ctx_ddl package.

--ps: the link BS gave you for the use of [code] tags is broken, try this one: How to use code tags and make your code easier to read
... to the user (schema) that is trying to run the package
Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672680 is a reply to message #672674] Tue, 23 October 2018 22:23 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The following is just a demonstration of what has already been said, using user test. You will need to substitute your actual user. You can also grant privileges from CTXSYS or from a user with DBA privileges.

-- reproduction of problem:
SCOTT@orcl_12.1.0.2.0> CREATE USER test IDENTIFIED BY test
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> GRANT CREATE SESSION TO test
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> GRANT CREATE PROCEDURE TO test
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> CONNECT test/test
Connected.
TEST@orcl_12.1.0.2.0> CREATE or replace PROCEDURE try_ctx
  2  IS
  3  BEGIN
  4    CTX_DDL.CREATE_POLICY ('test_policy', 'CTXSYS.AUTO_FILTER');
  5  END;
  6  /

Warning: Procedure created with compilation errors.

TEST@orcl_12.1.0.2.0> SHOW ERRORS
Errors for PROCEDURE TRY_CTX:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: Statement ignored
4/3      PLS-00201: identifier 'CTX_DDL' must be declared

-- solution:
TEST@orcl_12.1.0.2.0> CONNECT ctxsys/ctxsys
Connected.
CTXSYS@orcl_12.1.0.2.0> GRANT EXECUTE ON CTXSYS.CTX_DDL TO test
  2  /

Grant succeeded.

CTXSYS@orcl_12.1.0.2.0> CONNECT test/test
Connected.
TEST@orcl_12.1.0.2.0> CREATE or replace PROCEDURE try_ctx
  2  IS
  3  BEGIN
  4    CTX_DDL.CREATE_POLICY ('test_policy', 'CTXSYS.AUTO_FILTER');
  5  END;
  6  /

Procedure created.

TEST@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
Previous Topic: a procedure with dinamic sql
Next Topic: query performance
Goto Forum:
  


Current Time: Tue Sep 29 22:23:48 CDT 2020