Browse DevX
Sign up for e-mail newsletters from DevX

Search the Tips

PL/SQL - Page 2

21-40 of 55     Previous     Next
A Script That Lists All Tables and Their Column Names With Data Types
by Jatin Kumar
This script creates a report of all user owned tables and their columns with data types and nullability information. ...
Renaming an Oracle Table
by Jiwan Kishore
Create a synonym to the table with a new name. A public synonym is accessible by all schemas in the database, not just the user who created the synonym.
Setting the SQL Plus Environment With login.SQL File
by Shiv Gupta
The environment of a SQL plus session, like linesize, pagesize, etc., can be permanently set according to one's own needs with the help of login.sql file. The settings can be set by a SET ...
Determining the Size of an Oracle Database
by Shiv Gupta
The size of the database is the total size of the datafiles that make up the tablespaces of the database. These details are found in the dba_extents view. Type the following lines at the SQL-PLUS ...
Examine the Number of Extents in a Table
by Jose Antonio
The amount of time it takes to retrieve data blocks is proportionate to the number of extents in the table. Ideally, a table should have one to three extents. Run this script from sqlplus to get ...
Use Of 'Connect By' clause for Hierarchal Query
by Trupti Rajparia
The Connect By clause is useful for tables which have 2 or more columns with parent-child relationships. While displaying rows, we can use 'LEVEL' pseudo column to indent the results as hierarchy.
Code to Send E-Mails from an Oracle Stored Procedure
by Jose Antonio
The following code (based on the UTL_SMTP package supplied in Oracle8i V8.1.6, ...
Use Decode Function for Oracle Queries
by Trupti Rajparia
Decode is a very useful and handy function for Oracle queries. ...
Showing Rows of a Table in Columns Format
by Jatin Shah
Sometimes in our database structures we've got to store information in such a ...
Using ROWNUM Pseudocolumn in Oracle
by Trupti Rajparia
When querying in Oracle, use ROWNUM pseudocolumn to limit the number of returned rows. Pseudocolumns behave like table columns but are not actually stored in tables. (Other pseudocolumns are RowId, ...
SubQueries in the FROM Clause of SELECT
by Trupti Rajparia
When a SubQuery is used in a FROM clause instead of a Table name, Oracle ...
Optimize Oracle's SQL Statement Cache
by Andrew Holliday
When Oracle receives a SQL statement to execute, it first looks to see ...
Determining Your Session IDs for SQL Server and Oracle
by Andrew Holliday
If you work with SQL Server or Oracle you at some point probably will need ...
Roles Are Disabled at Package Compilation Time
by Trupti Rajparia
When granting permissions through roles, it is important to know that ...
Dynamic SQL in Oracle 8i
by Andrew Holliday
Oracle 8I gives developers a new feature to allow execution of native dynamic SQL. This is an alternative to the DBMS_SQL package previously required. Now developers can use dynamic SQL in their ...
Using Temp Tables in Oracle 8i
by Andrew Holliday
Oracle 8i introduces the concept of temp tables. This feature long since enjoyed by SQL Server developers enables data to be visible on a Session ...
Quickly Comment and Uncomment Blocks of Code in SQL
by Don Benson
During development, it is often necessary to have blocks of code for ...
Wrapping Application Code
by Jayanta Sengupta
Oracle comes with a PL/SQL wrapper, which converts the PL/SQL source code into an intermediate form of object code. By hiding application internals, the wrapper prevents the misuse of your code by ...
Avoid Multiple Exit Points in a Function
by Jayanta Sengupta
If you have a database function that has multiple return statements, try to minimize the exit points. I will explain with the following ...
Caching Packages on Database Startup
by Jayanta Sengupta
Oracle8i has introduced database-level and system-level triggers. They can track events in the database such as logon/logoff from a schema, startup and shutdown of a database, and response to a ...
21-40 of 55     Previous     Next
Thanks for your registration, follow us on our social networks to keep up-to-date