IP Spoofing: An Introduction
--------------------------------------------------------------------------------
Criminals have long employed the tactic of masking their true identity, from disguises to aliases to caller-id blocking. It should come as no surprise then, that criminals who conduct their nefarious activities on networks and computers should employ such techniques. IP spoofing is one of the most common forms of on-line camouflage. In IP spoofing, an attacker gains unauthorized access to a computer or a network by making it appear that a malicious message has come from a trusted machine by “spoofing” the IP address of that machine. In this article, we will examine the concepts of IP spoofing: why it is possible, how it works, what it is used for and how to defend against it.
History
The concept of IP spoofing, was initially discussed in academic circles in the 1980's. While known about for sometime, it was primarily theoretical until Robert Morris, whose son wrote the first Internet Worm, discovered a security weakness in the TCP protocol known as sequence prediction. Stephen Bellovin discussed the problem in-depth in Security Problems in the TCP/IP Protocol Suite, a paper that addressed design problems with the TCP/IP protocol suite. Another infamous attack, Kevin Mitnick's Christmas Day crack of Tsutomu Shimomura's machine, employed the IP spoofing and TCP sequence prediction techniques. While the popularity of such cracks has decreased due to the demise of the services they exploited, spoofing can still be used and needs to be addressed by all security administrators.
Technical Discussion
To completely understand how these attacks can take place, one must examine the structure of the TCP/IP protocol suite. A basic understanding of these headers and network exchanges is crucial to the process.
Internet Protocol – IP
Internet protocol (IP) is a network protocol operating at layer 3 (network) of the OSI model. It is a connectionless model, meaning there is no information regarding transaction state, which is used to route packets on a network. Additionally, there is no method in place to ensure that a packet is properly delivered to the destination.
Examining the IP header, we can see that the first 12 bytes (or the top 3 rows of the header) contain various information about the packet. The next 8 bytes (the next 2 rows), however, contains the source and destination IP addresses. Using one of several tools, an attacker can easily modify these addresses – specifically the “source address” field. It's important to note that each datagram is sent independent of all others due to the stateless nature of IP. Keep this fact in mind as we examine TCP in the next section.
Transmission Control Protocol – TCP
IP can be thought of as a routing wrapper for layer 4 (transport), which contains the Transmission Control Protocol (TCP). Unlike IP, TCP uses a connection-oriented design. This means that the participants in a TCP session must first build a connection - via the 3-way handshake (SYN-SYN/ACK-ACK) - then update one another on progress - via sequences and acknowledgements. This “conversation”, ensures data reliability, since the sender receives an OK from the recipient after each packet exchange.
As you can see above, a TCP header is very different from an IP header. We are concerned with the first 12 bytes of the TCP packet, which contain port and sequencing information. Much like an IP datagram, TCP packets can be manipulated using software. The source and destination ports normally depend on the network application in use (for example, HTTP via port 80). What's important for our understanding of spoofing are the sequence and acknowledgement numbers. The data contained in these fields ensures packet delivery by determining whether or not a packet needs to be resent. The sequence number is the number of the first byte in the current packet, which is relevant to the data stream. The acknowledgement number, in turn, contains the value of the next expected sequence number in the stream. This relationship confirms, on both ends, that the proper packets were received. It’s quite different than IP, since transaction state is closely monitored.
Consequences of the TCP/IP Design
Now that we have an overview of the TCP/IP formats, let's examine the consequences. Obviously, it's very easy to mask a source address by manipulating an IP header. This technique is used for obvious reasons and is employed in several of the attacks discussed below. Another consequence, specific to TCP, is sequence number prediction, which can lead to session hijacking or host impersonating. This method builds on IP spoofing, since a session, albeit a false one, is built. We will examine the ramifications of this in the attacks discussed below.
Spoofing Attacks
There are a few variations on the types of attacks that successfully employ IP spoofing. Although some are relatively dated, others are very pertinent to current security concerns.
Non-Blind Spoofing
This type of attack takes place when the attacker is on the same subnet as the victim. The sequence and acknowledgement numbers can be sniffed, eliminating the potential difficulty of calculating them accurately. The biggest threat of spoofing in this instance would be session hijacking. This is accomplished by corrupting the datastream of an established connection, then re-establishing it based on correct sequence and acknowledgement numbers with the attack machine. Using this technique, an attacker could effectively bypass any authentication measures taken place to build the connection.
Blind Spoofing
This is a more sophisticated attack, because the sequence and acknowledgement numbers are unreachable. In order to circumvent this, several packets are sent to the target machine in order to sample sequence numbers. While not the case today, machines in the past used basic techniques for generating sequence numbers. It was relatively easy to discover the exact formula by studying packets and TCP sessions. Today, most OSs implement random sequence number generation, making it difficult to predict them accurately. If, however, the sequence number was compromised, data could be sent to the target. Several years ago, many machines used host-based authentication services (i.e. Rlogin). A properly crafted attack could add the requisite data to a system (i.e. a new user account), blindly, enabling full access for the attacker who was impersonating a trusted host.
Man In the Middle Attack
Both types of spoofing are forms of a common security violation known as a man in the middle (MITM) attack. In these attacks, a malicious party intercepts a legitimate communication between two friendly parties. The malicious host then controls the flow of communication and can eliminate or alter the information sent by one of the original participants without the knowledge of either the original sender or the recipient. In this way, an attacker can fool a victim into disclosing confidential information by “spoofing” the identity of the original sender, who is presumably trusted by the recipient.
Denial of Service Attack
IP spoofing is almost always used in what is currently one of the most difficult attacks to defend against – denial of service attacks, or DoS. Since crackers are concerned only with consuming bandwidth and resources, they need not worry about properly completing handshakes and transactions. Rather, they wish to flood the victim with as many packets as possible in a short amount of time. In order to prolong the effectiveness of the attack, they spoof source IP addresses to make tracing and stopping the DoS as difficult as possible. When multiple compromised hosts are participating in the attack, all sending spoofed traffic, it is very challenging to quickly block traffic.
Misconceptions of IP Spoofing
While some of the attacks described above are a bit outdated, such as session hijacking for host-based authentication services, IP spoofing is still prevalent in network scanning and probes, as well as denial of service floods. However, the technique does not allow for anonymous Internet access, which is a common misconception for those unfamiliar with the practice. Any sort of spoofing beyond simple floods is relatively advanced and used in very specific instances such as evasion and connection hijacking.
Defending Against Spoofing
There are a few precautions that can be taken to limit IP spoofing risks on your network, such as:
Filtering at the Router - Implementing ingress and egress filtering on your border routers is a great place to start your spoofing defense. You will need to implement an ACL (access control list) that blocks private IP addresses on your downstream interface. Additionally, this interface should not accept addresses with your internal range as the source, as this is a common spoofing technique used to circumvent firewalls. On the upstream interface, you should restrict source addresses outside of your valid range, which will prevent someone on your network from sending spoofed traffic to the Internet.
Encryption and Authentication - Implementing encryption and authentication will also reduce spoofing threats. Both of these features are included in Ipv6, which will eliminate current spoofing threats. Additionally, you should eliminate all host-based authentication measures, which are sometimes common for machines on the same subnet. Ensure that the proper authentication measures are in place and carried out over a secure (encrypted) channel.
Conclusion
IP Spoofing is a problem without an easy solution, since it’s inherent to the design of the TCP/IP suite. Understanding how and why spoofing attacks are used, combined with a few simple prevention methods, can help protect your network from these malicious cloaking and cracking techniques.
Friday, October 26, 2007
Wednesday, October 3, 2007
Oracle FAQ : PL/SQL
Oracle PL/SQL FAQ
Topics
What is PL/SQL and what is it used for?
Should one use PL/SQL or Java to code procedures and triggers?
How can one see if somebody modified any code?
How can one search PL/SQL code for a string/key value?
How can one keep a history of PL/SQL code changes?
How can I protect my PL/SQL source code?
Can one print to the screen from PL/SQL?
Can one read/write files from PL/SQL?
Can one call DDL statements from PL/SQL?
Can one use dynamic SQL statements from PL/SQL?
What is the difference between %TYPE and %ROWTYPE?
What is the result of comparing NULL with NULL?
How does one get the value of a sequence into a PL/SQL variable?
Can one execute an operating system command from PL/SQL?
How does one loop through tables in PL/SQL?
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
What is a mutating and constraining table?
Can one pass an object/table as an argument to a remote procedure?
Is it better to put code in triggers or procedures? What is the difference?
Is there a PL/SQL Engine in SQL*Plus?
Is there a limit on the size of a PL/SQL block?
What is PL/SQL and what is it used for?
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. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
-------------------------------------------------------------------------------
Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This 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. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
-------------------------------------------------------------------------------
How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
--------------------------------------------------------------------------------
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
--------------------------------------------------------------------------------
How can one keep a history of PL/SQL code changes?
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.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
--------------------------------------------------------------------------------
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
--------------------------------------------------------------------------------
Can one print to the screen from PL/SQL?
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.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
Note that DBMS_OUTPUT doesn't print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
--------------------------------------------------------------------------------
Can one read/write files from PL/SQL
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
--------------------------------------------------------------------------------
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
--------------------------------------------------------------------------------
Can one use dynamic SQL statements from PL/SQL?
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 statements are NOT terminated by semicolons:
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...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' 'sqlerrm);
END;
/
--------------------------------------------------------------------------------
What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
DECLARE
v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpNo emp.empno%TYPE;
Back to top of file
--------------------------------------------------------------------------------
What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;
--------------------------------------------------------------------------------
How does one get the value of a sequence into a PL/SQL variable?
As 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;
Thanks to Ronald van Woensel
--------------------------------------------------------------------------------
Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
--------------------------------------------------------------------------------
How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department 'TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is 'emp_rec.ename);
END LOOP;
END LOOP;
END;
/
--------------------------------------------------------------------------------
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
--------------------------------------------------------------------------------
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
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);
Move all the tables to one user/schema.
--------------------------------------------------------------------------------
What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
Etc.
--------------------------------------------------------------------------------
Can one pass an object/table as an argument to a remote procedure?
The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/
--------------------------------------------------------------------------------
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
-------------------------------------------------------------------------------
Is there a PL/SQL Engine in SQL*Plus?
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.
--------------------------------------------------------------------------------
Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
SQL> select * from dba_object_size where name = 'procedure_name';
Topics
What is PL/SQL and what is it used for?
Should one use PL/SQL or Java to code procedures and triggers?
How can one see if somebody modified any code?
How can one search PL/SQL code for a string/key value?
How can one keep a history of PL/SQL code changes?
How can I protect my PL/SQL source code?
Can one print to the screen from PL/SQL?
Can one read/write files from PL/SQL?
Can one call DDL statements from PL/SQL?
Can one use dynamic SQL statements from PL/SQL?
What is the difference between %TYPE and %ROWTYPE?
What is the result of comparing NULL with NULL?
How does one get the value of a sequence into a PL/SQL variable?
Can one execute an operating system command from PL/SQL?
How does one loop through tables in PL/SQL?
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
What is a mutating and constraining table?
Can one pass an object/table as an argument to a remote procedure?
Is it better to put code in triggers or procedures? What is the difference?
Is there a PL/SQL Engine in SQL*Plus?
Is there a limit on the size of a PL/SQL block?
What is PL/SQL and what is it used for?
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. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
-------------------------------------------------------------------------------
Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This 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. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
-------------------------------------------------------------------------------
How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
--------------------------------------------------------------------------------
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
--------------------------------------------------------------------------------
How can one keep a history of PL/SQL code changes?
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.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
--------------------------------------------------------------------------------
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
--------------------------------------------------------------------------------
Can one print to the screen from PL/SQL?
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.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
Note that DBMS_OUTPUT doesn't print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
--------------------------------------------------------------------------------
Can one read/write files from PL/SQL
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
--------------------------------------------------------------------------------
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
--------------------------------------------------------------------------------
Can one use dynamic SQL statements from PL/SQL?
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 statements are NOT terminated by semicolons:
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...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' 'sqlerrm);
END;
/
--------------------------------------------------------------------------------
What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
DECLARE
v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpNo emp.empno%TYPE;
Back to top of file
--------------------------------------------------------------------------------
What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;
--------------------------------------------------------------------------------
How does one get the value of a sequence into a PL/SQL variable?
As 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;
Thanks to Ronald van Woensel
--------------------------------------------------------------------------------
Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
--------------------------------------------------------------------------------
How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department 'TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is 'emp_rec.ename);
END LOOP;
END LOOP;
END;
/
--------------------------------------------------------------------------------
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
--------------------------------------------------------------------------------
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
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);
Move all the tables to one user/schema.
--------------------------------------------------------------------------------
What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
Etc.
--------------------------------------------------------------------------------
Can one pass an object/table as an argument to a remote procedure?
The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/
--------------------------------------------------------------------------------
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
-------------------------------------------------------------------------------
Is there a PL/SQL Engine in SQL*Plus?
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.
--------------------------------------------------------------------------------
Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
SQL> select * from dba_object_size where name = 'procedure_name';
Calling Stored Database Procedures from Oracle Forms
Calling Stored Database Procedures from Oracle Forms
CONCEPTS
With V7 of the database came PL/SQL V2, which added the new functionality
of database stored procedures. Unfortunately, both SQL*Forms V3.0 and Oracle
Forms V4.0 still use PL/SQL V1. Because of this, when Oracle Forms comes
across a database stored procedure call in your code, it doesn't know how to
handle it.
In order to get around this limitation, the PL/SQL team wrote a set of
procedures that resolve the reference. When Oracle Forms comes across an
identifier, it checks to see if it is a variable, bind variable, table/view,
synonym, sequence, etc. If it is none of these things, the compiler calls a
stub generator to see if it can resolve the identifier as a database function
or procedure. In that case, a stub is generated for syntactical checking, and
the compiler continues. A stub is a PL/SQL procedure or function that has the
same header as the actual procedure or function so forms can perform
syntactical checks. (If the identifier is a package, the stub generator creates
a stub that references each program unit in the package.) From then on, the
stub is used, so the stub generation procedures are only needed at compile
time.
COMMON CAUSES OF ERRORS
(Causes listed below are explained in detail in POTENTIAL PROBLEMS)
PLS-103 Using SQL*Forms 3.0.16.11 or earlier
PLS-201 Server set up incorrectly
Invalid synonym
PLS-302 Procedure uses %type arugment
Extra rows in the PSTUBTBL table
Invalid synonym
PLS-303 Invalid synonym
Using SQL*Forms 3.0.16.11 or earlier
Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
PLS-306 Missing parameters/incorrect arguments
PLS-313 Invalid synonym
Procedure uses %type argument
Server set up incorrectly
Invalid datatype in procedure
PLS-363 Attempting to assign constant to in/out parameter
PLS-903 Stored procedure has only out argument types
ORA-4031 The SGA is too full to load the PSTUB procedure
FRM-40735 Type mismatch when calling database procedure
CORE DUMP Passing real value through integer
Attempting to declare variable precision or length
Stored procedure has only OUT argument types
DEBUGGING
The first step is to determine where the process is failing. We can do
this by calling the stub generator directly from SQL*Plus.
First off, you should be able to execute your procedure from SQL*Plus. If
you are unable to do this, check the permissions and syntax of your procedure.
When you know it can execute correctly, enter these lines:
VARIABLE a VARCHAR2(2000);
VARIABLE b VARCHAR2(2000);
DELETE FROM sys.pstubtbl;
EXECUTE sys.pstub('', NULL, :a, :b);
PRINT b;
DELETE FROM sys.pstubtbl;
(For, do not prefix it with a username. If
the procedure is in a package, use the package name only instead.)
If PSTUB will not execute, or if you receive an error message referring to
another package, your server may not be set up correctly (see 'Server set
up incorrectly' in POTENTIAL PROBLEMS).
If you get a message about the shared pool, or about the package being invalid
or discarded, the problem may be that the SGA is to fragmented or full to
load the necessary stub generators (see 'The SGA is too full to load the
DIUTIL package' in POTENTIAL PROBLEMS).
If the result of the PRINT b is '$$$ s_notv6Compat', you may be trying to use
%type arguments (see 'Procedure uses %type argument' in POTENTIAL PROBLEMS).
If the result is '$$$ s_subp not found', the problem is that the stub
generator cannot find the procedure. This is probably because you do not
have a synonym set up. A limitation of stub generation is that it cannot
search across users for procedures or packages. You cannot get around this by
prefixing the package or procedure name with the name of the user
(username.procedure). You must declare a public synonym. To do this you need
to both create the synonym, and do a "grant execute on proc to public". If
the result is '$$$ s_other', this generally means that the procedure can't run
in sqlplus independently. The action to take would be to call RDBMS support.
If the stub was correctly generated, PRINT b will return the stub body for your
procedure (PRINT a will give you the package spec if your procedure is in a
package). If this is the case, the problem may be with your version of
SQL*Forms, or may be a problem with your procedure. You can scan the list of
POTENTIAL PROBLEMS to see if you can find the source of the problem.
The following is an example of a successful call to the stub generator:
SQL> create procedure donothing(var1 in number, var2 in out char) is
2 begin
3 null;
4 end;
5 /
Procedure created.
SQL> execute sys.pstub('donothing', null, :a, :b);
PL/SQL procedure successfully completed.
SQL> print b
B
----------------------------------------------------------------------
procedure donothing (VAR1 NUMBER, VAR2 in out CHAR) is begin stproc.in
it('begin donothing(:VAR1, :VAR2); end;'); stproc.bind_i(VAR1); stproc
_bind_io(VAR2); stproc.execute; stproc.retrieve(2, VAR2); end;
POTENTIAL PROBLEMS
*Server set up incorrectly
There are 13 objects that need to be on the server and VALID for it to
create a stub. You can check the status of these objects by running the
following script from SQLPLUS while connected as SYS:
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A12
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN ('PSTUBT', 'PSTUBTBL', 'PIDL', 'DIUTIL',
'DIANA', 'STANDARD', 'DBMS_STANDARD')
ORDER BY 1,2;
The correct result of this query is as follows:
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
DBMS_STANDARD PACKAGE VALID
DBMS_STANDARD PACKAGE BODY VALID
DBMS_STANDARD SYNONYM VALID
DIANA PACKAGE VALID
DIANA PACKAGE BODY VALID
DIUTIL PACKAGE VALID
DIUTIL PACKAGE BODY VALID
PIDL PACKAGE VALID
PIDL PACKAGE BODY VALID
PSTUBT PROCEDURE VALID
PSTUBTBL TABLE VALID
STANDARD PACKAGE VALID
STANDARD PACKAGE BODY VALID
If any of these objects are missing or invalid, run DIUTIL.SQL as SYS
($ORACLE_HOME/rdbms/admin for Unix, \ORAWIN\DBS\ for windows. Note: On
windows, DIUTIL must be run as SYS from SQL*DBA on the server side). A
version of DIUTIL.SQL is usually shipped with the TOOLS--it may or may not
be newer than the server version (the only way to tell is to look at the
comments in the file)--if it is newer, feel free to use it. If you
are still having problems, you may be able to correctly install the
objects by running these scripts in this order: STANDARD.SQL, PIPIDL.SQL,
PIDIAN.SQL, DIUTIL.SQL, and PISTUB.SQL.
*Invalid datatype in procedure
There are only four datatypes supported for use in stored procedures that
are called from Oracle Forms, or called by another procedure that is called
by forms: VARCHAR2, NUMBER, DATE, and BOOLEAN, as documented in the release
notes. If a package has procedures with non-V6 compatible arguments/return
types, it will be dropped from the stub. A stub will still be returned, but
it will be missing these procedures.
*Stored procedure has only OUT argument types
The workaround to this problem is to use IN/OUT arguments instead.
*Extra rows in the PSTUBTBL table
The PSTUBTBL table is a temporary storage space for stubs, and after a stub
has been created, this table should be cleared out. If there are any rows
in PSTUBTBL, this can cause the stub generation process to fail. How do
rows get committed into PSTUBTBL?
1) A user runs the sys.pstub procedure from SQL*Plus, and afterword
they commit. If this is the case, the solution is simple: log
into sqlplus and type "delete from sys.pstubtbl".
2) Two different version of Oracle Forms are referencing the same
procedure. If this is the case, try to upgrade the older version,
and if you are running both V3.0 & V4.0, make sure that you are
using V4.0.12.
3) The stored procedures are being referenced by and older version of
forms (older than V3.0.16.12.7 on UNIX systems). Solution:
upgrade version of SQL*Forms.
*The SGA is too full to load the DIUTIL package
The DIUTIL package must be loaded into the SGA in order for a stub to be
generated. This package takes 32K, and it is not uncommon for heavily
loaded systems to not have that much contiguous SGA available. You have a
couple of options in this case--you can wait until later and try again, but
this is only a temporary fix. If this is a common problem, you can increase
your SGA size, or you can 'pin' the PSTUB procedure into the SGA so that it
won't be aged out. 7.0.15 and later versions of the RDBMS includes a
package called DBMS_SHARED_POOL that will allow you to do this. If this
package is not installed, you can install it by running DBMSPOOL.SQL. To
make sure there is room in the SGA to load the package, you can do this
right after startup, or you can clear the SGA by logging into SQLPLUS as SYS
and typing:
ALTER SYSTEM FLUSH SHARED_POOL;
Do this three times to make sure the SGA is flushed. After that, run PSTUB
on DIUTIL, or generate your form, as this will put the DIUTIL package into
the SGA, then (from SQLPLUS as SYS) type:
EXECUTE DBMS_SHARED_POOL.KEEP('DIUTIL');
*Invalid synonym
Double-check that your synonym is valid, and granted to public.
*Missing parameters/incorrect arguments
Make sure the parameters you specify in Oracle Forms match those specified in
the database procedure.
*Attempting to assign constant to in/out parameter
In/out parameters expect to send a value back to Oracle Forms. You cannot
assign a value to a constant.
*Referencing synonym with access granted through a role
Access must be explicitly granted.
*Procedure uses %type argument (bug 190597)
%type is supported in newer versions of DIUTIL.SQL (but %type must still
evaluate to a V6 compatible datatype). There is a patch available for this
bug for SQL*Forms V3.0.16.12 and later and Oracle Forms V4.0.11 and later.
*Using V3.0.16.11 or earlier
Referencing stored procedures works only in V3.0.16.12 and later.
*Type mismatch when calling database procedure
Check that the datatypes of your parameters match what is specified in the
database stored procedure.
*Passing real value through integer
This type mismatch may cause a core dump.
*Attempting to declare variable precision or length
The precision and length of variables are determined by forms, and should
not be specified in the procedure.
*Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
This uses a binary_integer datatype, which is not supported.
LIMITATIONS
-----------
*In Oracle Forms, you may reference database stored procedures, but you cannot
debug them from forms. There are two tables called USER_SOURCE and
USER_ERRORS which store information about PL/SQL use and errors.
*You cannot reference database stored package variables or cursors from forms.
*On 7.0.13 and earlier of the RDBMS, Boolean values do not return the correct
values
*On 3.0.16.12 of forms and earlier, there may be some problems with BOOLEAN
values--better to use NUMBER and 0 and 1.
*You cannot look up a remote subprogram via a synonym until RDBMS 7.1.1.
*Performance:
A development group had this experience--"Generation performance seems to
degrade over time in a multi-user system when many forms are being generated
and they all have several stored procedure references. We analyzed this using
tkprof and it turned out that it was the query of SYS.PSTUBTBL which was
slowing everything down. The trace file statistics for the query were huge
(>100000 block visists). All I could think of was that the nature of the
transaction to get stored procedure stub text (i.e. run the PSTUB generator
which inserts into PSTUBTBL, select from PSTUBTBL, rollback) was demanding
large amounts of read consistent data per session, and there was some
(unidentified) problem in the server. We fixed it by dropping and recreating
the SYS.PSUBTBL table, and the stats dropped right down and generation
performance increased."
*References:
There is more information on calling stored procedures from Oracle Forms
in the Oracle Forms release notes.
CONCEPTS
With V7 of the database came PL/SQL V2, which added the new functionality
of database stored procedures. Unfortunately, both SQL*Forms V3.0 and Oracle
Forms V4.0 still use PL/SQL V1. Because of this, when Oracle Forms comes
across a database stored procedure call in your code, it doesn't know how to
handle it.
In order to get around this limitation, the PL/SQL team wrote a set of
procedures that resolve the reference. When Oracle Forms comes across an
identifier, it checks to see if it is a variable, bind variable, table/view,
synonym, sequence, etc. If it is none of these things, the compiler calls a
stub generator to see if it can resolve the identifier as a database function
or procedure. In that case, a stub is generated for syntactical checking, and
the compiler continues. A stub is a PL/SQL procedure or function that has the
same header as the actual procedure or function so forms can perform
syntactical checks. (If the identifier is a package, the stub generator creates
a stub that references each program unit in the package.) From then on, the
stub is used, so the stub generation procedures are only needed at compile
time.
COMMON CAUSES OF ERRORS
(Causes listed below are explained in detail in POTENTIAL PROBLEMS)
PLS-103 Using SQL*Forms 3.0.16.11 or earlier
PLS-201 Server set up incorrectly
Invalid synonym
PLS-302 Procedure uses %type arugment
Extra rows in the PSTUBTBL table
Invalid synonym
PLS-303 Invalid synonym
Using SQL*Forms 3.0.16.11 or earlier
Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
PLS-306 Missing parameters/incorrect arguments
PLS-313 Invalid synonym
Procedure uses %type argument
Server set up incorrectly
Invalid datatype in procedure
PLS-363 Attempting to assign constant to in/out parameter
PLS-903 Stored procedure has only out argument types
ORA-4031 The SGA is too full to load the PSTUB procedure
FRM-40735 Type mismatch when calling database procedure
CORE DUMP Passing real value through integer
Attempting to declare variable precision or length
Stored procedure has only OUT argument types
DEBUGGING
The first step is to determine where the process is failing. We can do
this by calling the stub generator directly from SQL*Plus.
First off, you should be able to execute your procedure from SQL*Plus. If
you are unable to do this, check the permissions and syntax of your procedure.
When you know it can execute correctly, enter these lines:
VARIABLE a VARCHAR2(2000);
VARIABLE b VARCHAR2(2000);
DELETE FROM sys.pstubtbl;
EXECUTE sys.pstub('
PRINT b;
DELETE FROM sys.pstubtbl;
(For
the procedure is in a package, use the package name only instead.)
If PSTUB will not execute, or if you receive an error message referring to
another package, your server may not be set up correctly (see 'Server set
up incorrectly' in POTENTIAL PROBLEMS).
If you get a message about the shared pool, or about the package being invalid
or discarded, the problem may be that the SGA is to fragmented or full to
load the necessary stub generators (see 'The SGA is too full to load the
DIUTIL package' in POTENTIAL PROBLEMS).
If the result of the PRINT b is '$$$ s_notv6Compat', you may be trying to use
%type arguments (see 'Procedure uses %type argument' in POTENTIAL PROBLEMS).
If the result is '$$$ s_subp not found', the problem is that the stub
generator cannot find the procedure. This is probably because you do not
have a synonym set up. A limitation of stub generation is that it cannot
search across users for procedures or packages. You cannot get around this by
prefixing the package or procedure name with the name of the user
(username.procedure). You must declare a public synonym. To do this you need
to both create the synonym, and do a "grant execute on proc to public". If
the result is '$$$ s_other', this generally means that the procedure can't run
in sqlplus independently. The action to take would be to call RDBMS support.
If the stub was correctly generated, PRINT b will return the stub body for your
procedure (PRINT a will give you the package spec if your procedure is in a
package). If this is the case, the problem may be with your version of
SQL*Forms, or may be a problem with your procedure. You can scan the list of
POTENTIAL PROBLEMS to see if you can find the source of the problem.
The following is an example of a successful call to the stub generator:
SQL> create procedure donothing(var1 in number, var2 in out char) is
2 begin
3 null;
4 end;
5 /
Procedure created.
SQL> execute sys.pstub('donothing', null, :a, :b);
PL/SQL procedure successfully completed.
SQL> print b
B
----------------------------------------------------------------------
procedure donothing (VAR1 NUMBER, VAR2 in out CHAR) is begin stproc.in
it('begin donothing(:VAR1, :VAR2); end;'); stproc.bind_i(VAR1); stproc
_bind_io(VAR2); stproc.execute; stproc.retrieve(2, VAR2); end;
POTENTIAL PROBLEMS
*Server set up incorrectly
There are 13 objects that need to be on the server and VALID for it to
create a stub. You can check the status of these objects by running the
following script from SQLPLUS while connected as SYS:
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A12
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN ('PSTUBT', 'PSTUBTBL', 'PIDL', 'DIUTIL',
'DIANA', 'STANDARD', 'DBMS_STANDARD')
ORDER BY 1,2;
The correct result of this query is as follows:
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
DBMS_STANDARD PACKAGE VALID
DBMS_STANDARD PACKAGE BODY VALID
DBMS_STANDARD SYNONYM VALID
DIANA PACKAGE VALID
DIANA PACKAGE BODY VALID
DIUTIL PACKAGE VALID
DIUTIL PACKAGE BODY VALID
PIDL PACKAGE VALID
PIDL PACKAGE BODY VALID
PSTUBT PROCEDURE VALID
PSTUBTBL TABLE VALID
STANDARD PACKAGE VALID
STANDARD PACKAGE BODY VALID
If any of these objects are missing or invalid, run DIUTIL.SQL as SYS
($ORACLE_HOME/rdbms/admin for Unix, \ORAWIN\DBS\ for windows. Note: On
windows, DIUTIL must be run as SYS from SQL*DBA on the server side). A
version of DIUTIL.SQL is usually shipped with the TOOLS--it may or may not
be newer than the server version (the only way to tell is to look at the
comments in the file)--if it is newer, feel free to use it. If you
are still having problems, you may be able to correctly install the
objects by running these scripts in this order: STANDARD.SQL, PIPIDL.SQL,
PIDIAN.SQL, DIUTIL.SQL, and PISTUB.SQL.
*Invalid datatype in procedure
There are only four datatypes supported for use in stored procedures that
are called from Oracle Forms, or called by another procedure that is called
by forms: VARCHAR2, NUMBER, DATE, and BOOLEAN, as documented in the release
notes. If a package has procedures with non-V6 compatible arguments/return
types, it will be dropped from the stub. A stub will still be returned, but
it will be missing these procedures.
*Stored procedure has only OUT argument types
The workaround to this problem is to use IN/OUT arguments instead.
*Extra rows in the PSTUBTBL table
The PSTUBTBL table is a temporary storage space for stubs, and after a stub
has been created, this table should be cleared out. If there are any rows
in PSTUBTBL, this can cause the stub generation process to fail. How do
rows get committed into PSTUBTBL?
1) A user runs the sys.pstub procedure from SQL*Plus, and afterword
they commit. If this is the case, the solution is simple: log
into sqlplus and type "delete from sys.pstubtbl".
2) Two different version of Oracle Forms are referencing the same
procedure. If this is the case, try to upgrade the older version,
and if you are running both V3.0 & V4.0, make sure that you are
using V4.0.12.
3) The stored procedures are being referenced by and older version of
forms (older than V3.0.16.12.7 on UNIX systems). Solution:
upgrade version of SQL*Forms.
*The SGA is too full to load the DIUTIL package
The DIUTIL package must be loaded into the SGA in order for a stub to be
generated. This package takes 32K, and it is not uncommon for heavily
loaded systems to not have that much contiguous SGA available. You have a
couple of options in this case--you can wait until later and try again, but
this is only a temporary fix. If this is a common problem, you can increase
your SGA size, or you can 'pin' the PSTUB procedure into the SGA so that it
won't be aged out. 7.0.15 and later versions of the RDBMS includes a
package called DBMS_SHARED_POOL that will allow you to do this. If this
package is not installed, you can install it by running DBMSPOOL.SQL. To
make sure there is room in the SGA to load the package, you can do this
right after startup, or you can clear the SGA by logging into SQLPLUS as SYS
and typing:
ALTER SYSTEM FLUSH SHARED_POOL;
Do this three times to make sure the SGA is flushed. After that, run PSTUB
on DIUTIL, or generate your form, as this will put the DIUTIL package into
the SGA, then (from SQLPLUS as SYS) type:
EXECUTE DBMS_SHARED_POOL.KEEP('DIUTIL');
*Invalid synonym
Double-check that your synonym is valid, and granted to public.
*Missing parameters/incorrect arguments
Make sure the parameters you specify in Oracle Forms match those specified in
the database procedure.
*Attempting to assign constant to in/out parameter
In/out parameters expect to send a value back to Oracle Forms. You cannot
assign a value to a constant.
*Referencing synonym with access granted through a role
Access must be explicitly granted.
*Procedure uses %type argument (bug 190597)
%type is supported in newer versions of DIUTIL.SQL (but %type must still
evaluate to a V6 compatible datatype). There is a patch available for this
bug for SQL*Forms V3.0.16.12 and later and Oracle Forms V4.0.11 and later.
*Using V3.0.16.11 or earlier
Referencing stored procedures works only in V3.0.16.12 and later.
*Type mismatch when calling database procedure
Check that the datatypes of your parameters match what is specified in the
database stored procedure.
*Passing real value through integer
This type mismatch may cause a core dump.
*Attempting to declare variable precision or length
The precision and length of variables are determined by forms, and should
not be specified in the procedure.
*Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
This uses a binary_integer datatype, which is not supported.
LIMITATIONS
-----------
*In Oracle Forms, you may reference database stored procedures, but you cannot
debug them from forms. There are two tables called USER_SOURCE and
USER_ERRORS which store information about PL/SQL use and errors.
*You cannot reference database stored package variables or cursors from forms.
*On 7.0.13 and earlier of the RDBMS, Boolean values do not return the correct
values
*On 3.0.16.12 of forms and earlier, there may be some problems with BOOLEAN
values--better to use NUMBER and 0 and 1.
*You cannot look up a remote subprogram via a synonym until RDBMS 7.1.1.
*Performance:
A development group had this experience--"Generation performance seems to
degrade over time in a multi-user system when many forms are being generated
and they all have several stored procedure references. We analyzed this using
tkprof and it turned out that it was the query of SYS.PSTUBTBL which was
slowing everything down. The trace file statistics for the query were huge
(>100000 block visists). All I could think of was that the nature of the
transaction to get stored procedure stub text (i.e. run the PSTUB generator
which inserts into PSTUBTBL, select from PSTUBTBL, rollback) was demanding
large amounts of read consistent data per session, and there was some
(unidentified) problem in the server. We fixed it by dropping and recreating
the SYS.PSUBTBL table, and the stats dropped right down and generation
performance increased."
*References:
There is more information on calling stored procedures from Oracle Forms
in the Oracle Forms release notes.
Subscribe to:
Posts (Atom)