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.

This same segment has been used to send chemistries, microbiology reports, radiology reports, physical observations like height, weight, and coma scale and many other results and findings. It is discussed at length in Chapter 7 of the specification. Instead of looking for a field whose name describes the data you want to send, you should look for a code to go in the Observation Identifier field that describes that data.Return to Outline

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;

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.

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.

If the value is '1', automatic delivery of Internet Explorer 7 through Automatic Updates and the Windows Update/Microsoft Update site "Express" install option is disabled. If the value is not '1' or if the key doesn't exist, the system will be offered Internet Explorer 7 by Automatic Updates or if a user performs a manual update scan for high-priority updates via the Windows Update/Microsoft Update sites.

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.

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.

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

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Private Sub ddlCulture_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlCulture.SelectedIndexChanged

You can use the xp_regdeletevalue undocumented extended stored procedure to delete a particular value for a key in the registry via Transact-SQL. This is the syntax of the xp_regdeletevalue: ... (1/18/2004 8:17:55 PM) It's that time again - we're in need of some people to help us test and provide feedback on a couple of new features that are coming out very, very soon. Best Practices Engine The BP Engin... (11/6/2003)

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

Yes. The DataDirect Connect Series Oracle drivers fully support returning result sets (including multiple result sets) generated from PL/SQL stored procedures against 7, 8, 8i, 9i, and 10g servers. This implementation is: Flexible-You can call any stored procedure without worrying about data integrity or the size of result sets.

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.

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.

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.

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.

Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

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

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

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

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.

Hi there - I am new to Oracle (used to Ingres).I am having trouble creating a database.I keep getting the following oracle errors.ORA-01501ORA-01519ORA-00604ORA-30012Can you tell me what I am doing incorrectly?Many thankseibbed

There is the basic find command which allows you to search one file at a time for string, however findstr is far more versatile. The command has the following switches FINDSTR [/B] [/E] [/L] [/R] [/S] [/I] [/X] [/V] [/N] [/M] [/O] [/F:file] [/C:string] [/G:file] [strings] [[drive:][path]filename[ ...]] When you execute a batch file (or any other DOS command without a pif setting) the _DEFAULT.PIF file in the %systemroot% directory is used.