CODEWITHSUNDEEP
oracle-databaseplsqlcompilationoracle-sqldeveloper
user2093576
user2093576

Reputation: 3092

Saving a new procedure in SQLDeveloper

If we create a new procedure using Oracle SQL Developer, suppose TESTPROC 

CREATE OR REPLACE PROCEDURE TESTPROC AS 
BEGIN
 --SOMETHING
END TESTPROC;

and just save it using CTRL+S and don't run it, will it not be saved as procedure? if we close the window and try to search for it in list of procedures, we cant find it?

Do we need to run the proc at least once to save it?

Any way to retrieve a procedure lost this way?

Upvotes: 1

Views: 4432

Answers (4)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

just save it using CTRL+S and don't run it, will it not be saved as procedure?

Oracle SQL Developer is an IDE and NOT the database server. It provides an interface to interact with database. CTRL + S is Windows OS shortcut and has nothing to do with Oracle database.

You must compile the procedure to store it into the disk.

You said,

when I am trying to create a new procedure of the same name, i get error object already exists

That is because you are not using the REPLACE keyword to override the existing object.

Let's see why:

SQL> CREATE PROCEDURE foo
  2  AS
  3  BEGIN
  4    NULL;
  5  END;
  6  /

Procedure created.

SQL> CREATE PROCEDURE foo
  2  AS
  3  BEGIN
  4    NULL;
  5  END;
  6  /
CREATE PROCEDURE foo
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object

How to avoid it: Use CREATE OR REPLACE

SQL> CREATE OR REPLACE PROCEDURE foo
  2  AS
  3  BEGIN
  4    NULL;
  5  END;
  6  /

Procedure created.

To see whether the compiled procedure exists in the database, query the [DBA|ALL|USERS]_PROCEDURES view.

There are two things:

  • Stand alone procedures - use object_name as filter
SELECT owner,
  object_name,
  procedure_name,
  object_type
FROM all_procedures
WHERE owner    ='OWNER'
AND object_name='PROC_NAME';
  • Packaged procedures - use procedure_name as filter
SELECT owner,
  object_name,
  procedure_name,
  object_type
FROM all_procedures
WHERE owner    ='OWNER'
AND procedure_name='PROC_NAME';

Upvotes: 2

Moudiz
Moudiz

Reputation: 7377

try to search for it in list of procedures

this query tells you if your procedure exists on your database

select * from user_procedures where object_name ='THE NAME OF YOUR PROC'

Or

select * from user_objects where object_type ='PROCEDURE' and OBJECT_NAME='THE NAME OF YOUR PROC'

to solve error object already exists add or replace in your procedure like this

create or replace procedure Proc_test ...

Upvotes: 2

Erkan Haspulat
Erkan Haspulat

Reputation: 12552

PLSQL Developer or any other client is just a tool for you to talk to the database itself. You might be using Toad, sql* or any other interface, and they may all have their own way of doing things.

What you call running, is actually called compiling. When you "run" the statement you provided, it is only compiled against the database you are connected to. It can only actually run, if you call the procedure you compiled from a PLSQL block.

Try to understand the differences between a database server and an IDE, then you'll be a lot more comfortable working with Oracle.

Upvotes: 1

Tatiana
Tatiana

Reputation: 1499

CTRL+S - you save a file. You can find this file in your OS.

To save procedure in DB you must create it in DB. For this you need execute CREATE PROCEDURE. So no, you didn't "save" it in DB.

Upvotes: 2

Related Questions