Do You Make These Mistakes?

The smallest meaningful grouping of code is known as a block. A block is a unit of code that provides execution and scoping boundaries for variable declarations and exception handling. PL/SQL allows you to create anonymous blocks (blocks of code that have no name) and named blocks, which may be packages, procedures, functions, triggers, or object types. A PL/SQL block has up to four different sections, only one of which is mandatory: Example:- --Header Section PROCEDURE myFirstProcedure(NAME IN Varchar2) IS --Declaration Section SALARY NUMBER; JOINING_DATE DATE; BEGIN --Execution Section INSERT INTO EMPLOYEE (emp_name, salary, joining_date) VALUES (name, salary, joining_date); EXCEPTION --Exception Section WHEN DUP_VAL_IN_INDEX THEN DBMS_OUTPUT.PUT_LINE ("Duplicate Value, Cannot Insert."); END; /

You want to save output from a SQL*Plus session to a file to get record of your actions, or because you are dynamically generating commands to execute later. This can be done in SQL*Plus with the help of SPOOL command: SQL> SPOOL report.txt SQL> @run_report.sql ...output scrolls past and gets written to the file report.txt... SQL> SPOOL OFF The first command, SPOOL report, tells SQL*Plus to save everything from that point forward into the file report.txt. The file extension of .lst is the default and can be overridden by supplying your own extension in the SPOOL command like specified above. SPOOL OFF tells SQL*Plus to stop saving the output and to close the file.

SQL*Plus can also create and manipulate its own in-memory variables, and it sets aside a few special variables that will affect its behavior. Actually, there are two separate types of variables in SQL*Plus: DEFINEs and bind variables. To assign a value to a DEFINE variable, you can use the DEFINE command: SQL> DEFINE X = "UMASARATH" To view the value of x, specify: SQL> DEFINE X DEFINE X               = "UMASARATH" (CHAR) SQL> You would refer to such a variable using an ampersand (&). SQL*Plus does a simple substitution before sending the statement to the Oracle database, so you will need single-quote marks around the variable when you want to use it as a literal string. SQL> select '&x' from dual; old   1: select '&x' from dual new   1: select 'UMASARATH' from dual 'UMASARAT --------- UMASARATH SQL>

Before configuring PL SQL developer, here are the prerequisites. Download the oracle instant client from the below link and download the specified version instantclient-basic-nt-11.2.0.3.0.zip http://www.oracle.com/technetwork/topics/winsoft-085727.html This download requires oracle account. Please create it if you are not having oracle account. Unzip the file and the file instantclient_11_2 will be extracted. Place it in the below location. C:\oracle If the oracle folder is missing in C drive, please create the folder. Once the extracted folder is placed in C:/oracle, create a folder named network in instantclient_11_2 folder and one more folder named admin inside network folder. The path will look like C:\oracle\instantclient_11_2\network\admin Inside admin folder, create a file tnsnames.ora file and place all your existing connections inside this file. C:\oracle\instantclient_11_2\network\admin\tnsnames.ora Once the pre-requisites are done, the following steps are to be