Dot notation is used for identifying record fields, object attributes, and things inside packages or other schemas. When you combine these things, you might need to use expressions with multiple levels of dots, where it isn't always clear what each dot refers to. Here are some of the combinations.

Example question: "How come I can't use [sql-param tablename.fieldname] notation when I do a multi-table query?" DBI simply does not support tablename.fieldname in the return value. You can just refer to them by the fieldname. In the case of field names that are the same but need to be selected from different tables, you can use SELECT orderline.quantity as o_quan,... and refer to it as [sql-param o_quan]. See similar questions...

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

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

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...

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... 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...

You can create a DataQuery by specifying a SQL query and as many attribute/column mappings as you like. You can then define bind variables or set filters on that query to gain the final query you wish. See similar questions...

A copyright notation consists of the word "copyright" or the international copyright symbol, which is the letter "C" within a circle, together with the year of first publication and the copyright owner's name. For example, a proper copyright notation for this work would be either of the following: c 1997 Andrew D. Epstein or "Copyright 1997 Andrew D. Epstein." 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...