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. See similar questions...

You can repeatedly call getCause() of the SQLException: try { ... } catch (SQLException sqle) { for(Throwable t : sqle) { System.... See similar questions...

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. See similar questions...

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. See similar questions...

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). See similar questions...

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. See similar questions...

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. See similar questions...

You can create object types by issuing the SQL statement CREATE TYPE using dynamic SQL (the EXECUTE IMMEDIATE statement). You can use objects to group related values, as you would do with records, with the added advantage that you can store objects directly in the database. You can also create varrays, nested tables, and associative arrays of objects. You can write PL/SQL procedures and functions that accept objects as parameters. See similar questions...

All other PL/SQL I/O is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to DBMS_OUTPUT. (SQL*Plus does not display DBMS_OUTPUT data unless you issue the command SET SERVEROUTPUT ON first. See similar questions...

Before you Execute the PL/SQL do the following: SQL> set serveroutput on SQL> execute xxxxxxxxxxxxxx.xxxxxxxxxxx; dont forget the semicolon CREATE OR REPLACE PACKAGE BODY apps_ar_forms_dup_trackcm AS select line, text from user_source where upper(name) = upper('&PLSQL_NAME') order by line Click Database, then Schema Browser, wait a few minutes. Then click the PROCS tab and look for your FUNCTION, PROCEDURE or PACKAGE. click here for a screen print example See similar questions...

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.. See similar questions...

PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions: Grant direct access on the tables to your user. Do not use roles! GRANT select ON scott.emp TO my_user; Define your procedures with invoker rights (Oracle 8i and higher); create or replace procedure proc1 authid current_user is begin ... See similar questions...

No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually. See similar questions...

TEXT_IO can only be run in a PL/SQL script inside oracle FORMS only, not a database package/procedure called from forms. See similar questions...

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. See similar questions...

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. See similar questions...

A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example: CONNECT / AS SYSDBA CREATE OR REPLACE DIRECTORY mydir AS '/tmp'; GRANT read, write ON DIRECTORY mydir TO scott; Provide user access to the UTL_FILE package (created by catproc.sql): GRANT EXECUTE ON UTL_FILE TO scott; Write File DECLARE fHandler UTL_FILE.FILE_TYPE; BEGIN fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w'); UTL_FILE. See similar questions...

you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this: i := sq_sequence.NEXTVAL; However, one can use embedded SQL statements to obtain sequence values: select sq_sequence.NEXTVAL into :i from dual; See similar questions...