- Want to bring up the printer dialog
box in Forms?
You’ll need to attach the library D2KWUTIL.PLL.
(Note: This only works in Windows.)
DECLARE
cPrinter VARCHAR2(250);
cPort VARCHAR2(250);
BEGIN
WIN_API_DIALOG.SELECT_PRINTER(cPrinter, cPort, TRUE);
--The cPrinter variable now holds whatever printer the user selected
--and you can pass it as a report parameter, save it in a field, etc.
END;
[top
of page]
- On what operating system is a Form being
run?
Use the Forms built-in function get_application_property(operating_system)
to return the client's OS.
[top
of page]
- Script to recompile all invalid objects
spool runobj.sql
SELECT 'alter '||
replace(object_type,'BODY')|| '
'||SUBSTR(object_name,1,30)||' compile '
||decode(object_type,
'PACKAGE BODY','body',null)||';'
FROM user_objects
WHERE (object_type IN ('PROCEDURE',
'TRIGGER','FUNCTION','PACKAGE')
or object_type like '%PACKAGE%')
AND status != 'VALID'
order by object_type, object_name
/
spool off
start runobj.sql
[top
of page]
- How do I read registry settings from
Forms?
The forms built-in package TOOL_ENV will return
values from the Oracle registry settings
[top
of page]
- Generate a random number in Oracle.
Use the package DBMS_RANDOM
[top
of page]
- What's the difference between Oracle's
Standard Edition and Enterprise Edition?
Technet has a matrix
detailing the differences between Standard
and Enterprise
[top
of page]
- How do you find what version of Oracle
is running?
select *
from v$version
/
[top
of page]
- How do you time something in units less
than 1 second?
The Oracle package procedure dbms_utility.get_time
will return the hundredths of seconds.
[top
of page]
- Create a dynamic ORDER BY clause
Use DECODE to determine which column you want
to order by:
SELECT
u.last_name,
u.first_name,
u.company_name
FROM
user u
ORDER BY
DECODE(p_sortOrder,'LAST',u.last_name,
'FIRST',u.first_name,u.company_name)
[top
of page]
- In PL/SQL, how do you immediately return
an error if you try to update a table already
in a locked state?
Use the NOWAIT option when you update:
CURSOR c1 IS
SELECT
e.last_name,
e.first_name,
e.company_name
FROM emp a
WHERE a.emp_no = 135
FOR UPDATE NOWAIT;
[top
of page]
- Function to return if a character string
contains only numbers.
create or replace function MY_IS_NUMBER
(test_value in varchar2) RETURN
BOOLEAN IS
l_dummy number;
BEGIN
l_dummy := to_number(test_value);
RETURN(TRUE);
EXCEPTION
WHEN OTHERS
THEN
RETURN(FALSE);
END;
/
[top
of page]
- Encrypt your PL/SQL code
Oracle provides a WRAP function that will
convert your .sql file into an encrypted file
that has a /plb extension. This plb file can
then be given to clients to run on the database.
The source code in the database dba_source
table will also be encrypted.
At the DOS prompt, run
WRAP INAME=/mydir/myfile.sql
ONAME=/mydir/myfile.plb
[top
of page]
- Pause the program for n seconds
Use the Oracle database package procedure:
dbms_lock.sleep(n)
to pause for n seconds.
[top
of page]
- How to find out what query a user is
running
select a.sid,s.sql_text
from v$session a, v$sqltext s
where a.sql_address = s.address
and a.sql_hash_value = s.hash_value
and a.username = 'YOUR_USER'
order by a.sid, s.piece;
[top
of page]
- Running dynamic SQL with EXECUTE IMMEDIATE
is much easier than the old dbms_sql package
DECLARE
string VARCHAR2(100);
value VARCHAR2(50);
BEGIN
string := 'INSERT INTO table1 values (:bind1)';
value := 'columnvalue';
EXECUTE IMMEDIATE string USING value;
END;
[top
of page]
- Turn off archiving for a table
When using tables to hold large amounts
of derived data, such as a nightly
summary table used by reports, avoid needless
archiving and speed up the
insert process by turning off archiving for
that table. It can be done in
two steps:
CREATE TABLE testlog
(text varchar2(5)) NOLOGGING
/
Inserts should use the APPEND hint:
INSERT /*+ append */ INTO testlog
VALUES ('Test')
/
[top
of page]
- First day of month
TRUNC(sysdate,'MONTH')
[top
of page]
- First day of year
TRUNC(sysdate,'YEAR')
[top
of page]
- How can I encrypt data
that I store in my Oracle database?
As of 8.1.6, there is an Oracle package DBMS_OBFUSCATION_TOOLKIT
that has functions for encrypting and decrypting
data.
[top
of page]
- Get the operating system
username for the user that initiated the database
connection
SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL;
[top
of page]
- Query to return a list
of every Oracle built-in function
select distinct object_name
from all_arguments
where package_name = 'STANDARD'
/
[top
of page]
- Useful Quotes
- Failure is not an option... It is a
feature that is built into the software.
- Unknown
[top
of page]
|
