Practical difference between .pls and .sql file in oracle

Question

What is the practical difference between .pls and .sql file in oracle. What are the restrictions on different types of statements in both?

I am given a project in unix(korn script) which uses both .sql and .pls files in different places. Trying to figure out which should be used where.

Answer 1

File suffixes are to a large extent just a matter of convention. We can use them to provide useful metadata to other developers.

So .pls indicates (we hope) that the file is a PL/SQL script, creating a PL/SQL package or stored procedure. In other shops we might see .pks and .pkb to indicate a package spec script and a package body script respectively. A file with a .tab or a .tbl extension indicates DDL to create a table. Because this is just a convention it requires discipline (or code reviews) to make sure we remain consistent.

The one difference is .sql. Although the convention is that it represents some SQL (a query, or perhaps DML or DDL) it has a special property in SQL*Plus. If we have a script called whatever.sql we can call it like this in SQL*Plus...

SQL>  @whatever

... whereas if a script has any other extension we must include the extension in the call...

SQL>  @whatever.pls

Other IDEs or others clients (e.g. build scripts) may use file extensions as a filtering mechanisms or for applying syntax highlighting, but their rules should always be controlled by preferences.

" What are the restrictions on different types of statements in both?"

To sum up, there are no restrictions. Some places I have worked used nothing but .sql files, Others had a complicated menagerie of scripts: .tbl, .idx, .vw, etc. Sociopaths can use just .txt for all their files: the database won't care. Provided it's valid Oracle syntax the code will run.