CODEWITHSUNDEEP
plsql
user8383675
user8383675

Reputation:

Cannot Create Index in PL SQL

I am just a beginner to PL SQL and trying to explore queries with PL SQL on Oracle 11g express edition. I am unable to create an Index using PL SQL procedure call. I am getting an error Error report: ORA-06550

BEGIN
CREATE INDEX employee_empid_index ON employee(empid);
END;

What modifications should I make in my Code?

Upvotes: 0

Views: 3570

Answers (3)

fg78nc
fg78nc

Reputation: 5232

DDL statements (such as CREATE TABLE .. or CREATE INDEX) are not allows in PL/SQL context; (PL/SQL context is between BEGIN and END). However you can execute them using Dynamic SQL syntax within PL/SQL block.

Example

BEGIN
 EXECUTE IMMEDIATE 'CREATE INDEX employee_empid_index ON employee(empid);'
END;

Or just execute them within SQL context.

CREATE INDEX employee_empid_index ON employee(empid);

Upvotes: 3

Ebrahim Salehi
Ebrahim Salehi

Reputation: 21

in this case you must not use the "begin" and "end" terms , Remove those terms and try it again.

Upvotes: 0

bpjoshi
bpjoshi

Reputation: 1141

You should not be using DDL queries in PL/SQL. PL/SQL should be used for run time things like fetching data from one table to another table(e.g. schedule a job that copies data from one table to another table on hourly basis for back up). Table creation, index creation should be done with normal sql beforehand.

In short PL/SQL is not intended to do the jobs of DDL. Some DDL statements inside PL SQL are however allowed. This article explains the same very well

Upvotes: 2

Related Questions