OraFAQ Forum: SQL & PL/SQL » PLS-00201: identifier 'CTX

Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201: identifier 'CTX_DDL' must be declared

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLS-00201: identifier 'CTX_DDL' must be declared [message #672674]

Tue, 23 October 2018 07:14

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

Report message to a moderator

Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672675 is a reply to message #672674]

Tue, 23 October 2018 07:25

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672676 is a reply to message #672675]

Tue, 23 October 2018 07:27

John Watson
Messages: 8836
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

Bill B
Messages: 1971
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

Report message to a moderator

Re: PLS-00201: identifier 'CTX_DDL' must be declared [message #672680 is a reply to message #672674]

Tue, 23 October 2018 22:23

Barbara Boehmer
Messages: 9005
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.

Report message to a moderator

Previous Topic:	a procedure with dinamic sql
Next Topic:	query performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 28 18:31:56 CDT 2023