Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?". Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL.

You can test or manipulate strings using the built-in functions REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR. Oracle's regular expression features use characters like '.', '*', '^', and '$' that you might be familiar with from UNIX or Perl programming. For multi-language programming, there are also extensions such as '[:lower:]' to match a lowercase letter, instead of '[a-z]' which does not match lowercase accented letters.

When you imbed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the right places.

PL/SQL lets you issue a query and return a result set using a cursor variable, also known as a REF CURSOR. See "Using Cursor Variables (REF CURSORs)".

Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that the statements within quotes are NOT semicolon terminated: EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)'; -- Using bind variables... sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; -- Returning a cursor..

Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory. The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (somewhat larger than the original). The binary code can be distributed without fear of exposing your proprietary algorithms and methods. Oracle will still understand and know how to execute the code.

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example: CREATE TABLE SOURCE_HIST -- Create history table AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.* FROM USER_SOURCE WHERE 1=2; CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table AFTER CREATE ON SCOTT.

Those acronyms refer to various features such as programming languages, databases, etc. that might be available with a hosting plan. Please check out our Glossary for definitions.

The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code. SELECT type, name, line FROM user_source WHERE UPPER(text) LIKE '%&KEYWORD%'; If you run the above query from SQL*Plus, enter the string you are searching for when promted for KEYWORD. If not, replace &KEYWORD with the string you are searching for.

Latest Answer: Hi All,I am new to this community. It seems the question is related to Begin End block of PL/SQL. Se... Latest Answer: You must be speaking about PLS_INTEGER. PLS_INTEGER, uses machine-arithmetic unlike BINARY_INTEGER... Latest Answer: DESC will display the values in DESCending order when used in an ORDER BY clause.For example:SELECT ... Latest Answer: In hash join hash table gets build using the inputs. Nested loops as the name implies uses looping c..

By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, you might put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO.

SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn't include all the things that normal programming languages have, such as loops and IF...THEN...ELSE statements. PL/SQL is a normal programming language that includes all the features of most other programming languages.

Unfortunately, the official specifications are not freely available (ANSI makes them available for purchase). However, there are books - such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer - which give a comprehensive overview of the standard, including coverage of stored procedures.

It is not recommended that you use database triggers or stored procedures. A trigger will be activated when any application or utility is used to access the file. For example. If you use FTP to transfer a file to another platform, the trigger application will be activated to decrypt a field. This defeats the intent of the security and may result in very little additional security of your data.

We don't use stored procedures since they will result in a non-portable application. Our design allows, however, for stored procedures to be added elegantly--all you need to do is to provide an alternate Data Access Object that plugs into the application architecture and makes stored procedure calls. These are essentially a performance optimization that are particular to a specific application and its deployment environment.

Why am I being blocked by spid -1? I am not able to kill it and have to restart SQL Server to get around it. Why does the new 'TOP' command in SQL 7 not work? I get a syntax error. Also ALTER TABLE and other SQL 7 only commands.

Latest Answer: SQL is a structured query language.Used to perform operations such as retrieval,updations,insertions... How can I use the bind variable in a report to create an output file. For example I give the starting... What is the difference between IS/AS while creating a procedure ?i.e Create or Replace procedure is/as... How to write a query or procedure or function to retrieve all the tables from database where the table...

SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution. As such, SQL cannot be used to execute procedural code with conditional, iterative and sequential statements. To overcome this limitation, PL/SQL was created. PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of Ada.

One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example: set serveroutput on begin dbms_output.put_line('Look Ma, I can print from PL/SQL!!!'); end; / DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDIATE" statement (native SQL). Examples: begin EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)'; end; begin execute Immediate 'TRUNCATE TABLE emp'; end; DECLARE var VARCHAR2(100); BEGIN var := 'CREATE TABLE temp1(col1 NUMBER(2))'; EXECUTE IMMEDIATE var; END; Users running Oracle versions below Oracle 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).