Using Notepad ++ to Execute Oracle SQL, executeoracle

Using Notepad ++ to Execute Oracle SQL, executeoracle

Link: http://www.toadworld.com/products/toad-for-oracle/ B /weblog/archive/2013/08/21/using-notepad-to-execute-oracle-sql.aspx

Notepad ++ is a very useful text editing tool. It is also very interesting to use it to execute Oracle SQL. Although it is not practical, it may be useful in some cases.

Copy the original text here for record only. For the original text, see the above link.

The original article is as follows:

Toad for Oracle is the single most robust and powerful IDE (Integrated Development Environment) and DBA tool for all your Oracle database needs. it's a veritable nuclear powered, Swiss army knife capable of grand accomplishments. however there may well be times where you need a smaller, focused and thus simpler tool for modest tasks-such as opening and running a straightforward SQL script, where you might make minor changes such as setting a different script variable value at the top. yet you still your CT important features such as syntax highlighting for readability. for those specific, lesser needs I often use the popular freeware editor Notepad ++. however it requires some manual tweaking in order to have Notepad ++ execute SQL via Oracle's SQL * Plus. in this blog I'll show you how.

Note:The astute reader might ask "Why not just use Toad's free QSR utility (I. e. quest Script Runner -- it ships with every copy of Toad for Oracle) which looks and operates the same way as the Toad Editor, but it only has des a subset of the Editor's features?" -Shown below inFigure 1. The answer is simple-people like choices and adjust people know and prefer Notepad ++ as their standard Windows Notepad replacement and editor of choice for most simple needs.

Figure 1: Quest Script Runner Example

So let's see how to enable Notepad ++ to work much like QSR-where Notepad ++ is the basic editor and spawns SQL * Plus to run the editor contents. it may not be as well integrated or flexible as QSR, but once again it's what happens people know and prefer for basic editing.Figure 2Shows the same script execution resultsFigure 1.

Figure 2: Notepad ++ Example

Here are the steps:

Figure 3: Notepad ++ Plugin Manager-Enable NppExec

Figure 4: Save your SQL * Plus execute macro

 

 

Instructions on execute immediate in PL/SQL

In oracle pl/SQL:
Execute immediate replaces DBMS_ SQL package in Oracle8i.
It parses and immediately executes dynamic SQL statements or PL/SQL blocks created when they are not running. the performance of dynamic creation and execution of SQL statements is advanced. The goal of EXECUTE IMMEDIATE is to reduce the enterprise cost and obtain high performance, which is easier to code than before. although DBMS_ SQL is still available, EXECUTE IMMEDIATE is recommended because it gains more than the package.

-- Tips

1. execute immediate will not commit a DML transaction execution and should be committed explicitly
If you use execute immediate to process DML commands,
Before the execution is complete, you need to submit it explicitly or as part of execute immediate.
If you use execute immediate to process DDL commands, it submits all previously changed data

2. multi-row queries are not supported. This interaction uses a temporary table to store records (see the example below) or REF cursors.

3. Do not use semicolons when executing SQL statements. When executing PL/SQL blocks, use semicolons at the end.

4. These features are not covered in detail in the Oracle manual.
The following example shows all possible ways to use Execute immediate.

5. For Forms developers, this function is not available for Forms 6i in PL/SQL 8.0.6.3.

Execute immediate -- usage example

1. Run DDL statements in PL/SQL

Begin
Execute immediate 'set role all ';
End;

2. Pass values to dynamic statements (USING clause)

Declare
Rochelle depnam varchar2 (20): = 'testing ';
Rochelle loc varchar2 (10): = 'Dubai ';
Begin
Execute immediate 'insert into dept values (: 1,: 2,: 3 )'
Using 50, l_depnam, l_loc;
Commit;
End;

3. Retrieve values from dynamic statements (INTO clause)

Declare
L_cnt varchar2 (20 );
Begin
Execute immediate 'select count (1) from emp'
Into l_cnt;
Dbms_output.put_line (l_cnt );
End;

4. Dynamic call routine. The variable parameters used in the routine must specify the parameter type.
Struct is considered as the IN type. Other types must be explicitly specified.

Declare
L_routin varchar2 (100): = 'gen2161. get_rowcnt ';
L_tblnam varchar2 (20): = 'emp ';
Rochelle CNT number;
L_status varchar2 (200 );
Begin
Execute immediate 'begin' | l_routin | '(: 2,: 3,: 4); end ;'
Using in l_tblnam, out l_cnt, in o ...... the remaining full text>

Execute immediate in Oracle pl/SQL programming

Haha, after verification, the cause of 1 failure is that there is no space behind the from, and you will '| the last SQL statement executed after splicing the table name is
'Select count (*) frombook', so the from keyword cannot be found. You are welcome to ask.