SQL> select privilege from user_tab_privs where lower(table_name)=’t_foo’ and lower(grantee)=’frank’;
PRIVILEGE
—————————————-
UPDATE
SELECT
INSERT
DELETE
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
—————————— —————————— — — —
FRANK AQ_ADMINISTRATOR_ROLE NO YES NO
PUBLIC SELECT_CATALOG_ROLE NO YES NO
To insert a text value with an ampersand, e.g. AT&T, one can do something like this.
insert into foo values(‘AT’||Chr(38)||’T’)
To learn more see How does one disable interactive prompting in SQL*Plus?
To insert a text value with a single quote, e.g. Don’t do it, one can do something like this.
insert into foo values(‘Don”t do it’)
To learn more see How does one escape special characters when building SQL queries?
Oracle SQL queries can throw exceptions. For example in this query f there is no data then Oracle will throw a NO_DATA_FOUND exception.
SELECT status_date INTO v_status_date FROM member WHERE member_id = p_member_id_in;
ORA-01403: no data found
If this query is part of a stored procedure and is called from Hibernate you will get this uninformative exception.
org.hibernate.exception.DataException: could not execute query
Oracle SQL, like many languages, has a try catch construct. In this example you could do the following.
BEGIN
SELECT status_date INTO v_status_date FROM member WHERE member_id = p_member_id_in;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_status_date := NULL;
END;
Calling an Oracle function is quite simple. For example
SELECT package.register(1, "Frank", "Kim") FROM dual;
However calling Oracle stored procedures is a bit more tricky, especially if the Oracle stored procedure has output parameters. Here is an example of how to call an Oracle stored procedure that has three input parameters and two output parameters.
DECLARE p_credit_status_out INTEGER;
p_school_status_out INTEGER;
BEGIN
package.get_status(1, "Frank", "Kim", p_credit_status_out, p_school_status_out);
END;
/
Creation
CREATE TRIGGER trig_frank
BEFORE INSERT ON table_frank
FOR EACH ROW
BEGIN
SELECT seq_frank.nextval
INTO :new.id
FROM dual;
END;
.
RUN;
References
When you start SQLPlus you should see the Oracle version information. For example:
$ sqlplus fkim/fkim@example Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
Also you can execute SQL to retrieve the version information.
SQL> select * from v$version where banner like 'Oracle%'; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
Stone Table by WorldWanderlust
To get the table definition.
describe foo;
Or simply
desc foo
To get the constraints of a table.
select * from user_constraints where table_name='foo';
Similarly to find the table for a constraint.
select table_name from user_constraints where constraint_name='bar';
To find all the tables that have foreign key constraints for a given table you can use this query. It first find the name of the unique and/or primary key constraints in the given table. Then it finds all the constraints in other tables that reference this constraint. Note that the table names are usually uppercase.
select owner,constraint_name,constraint_type,table_name,r_owner,
r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');
A simpler way which also constrains the owner would be like this.
select table_name,constraint_name
from all_constraints
where constraint_type='R'
and owner='FRANK'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');
For further reading please see Finding Foreign Key Constraints in Oracle and Oracle Constraints.
If you want to install Oracle, that great big beast, the computer you intend to install must have a static IP address. However for many people their IP address is dynamic, that is it comes from a DHCP server. To get around this restriction you can install the Microsoft Loopback Adapter which creates a virtual static IP.
To install the Microsoft Loopback Adapter follow these instructions. You should be able to start the Add Hardware Wizard by selecting Add Hardware in the Control Panel. In Windows XP if you cannot find Add Hardware in the Control Panel then you can start the Add Hardware Wizard by selecting Printers and Other Hardware in the Control Panel. You should see an Add Hardware entry on the top of the left column.
After installing the Microsoft Loopback Adapter go to the Control Panel, select Network and Internet Connections and then Network Connections. Select the Local Area Connection that is a Microsoft Loopback Adapter (usually Local Area Connection 2). Select the Internet Protocol (TCP/IP in Windows XP, TCP/IPv4 in Windows Vista) and then press the Properties button. Next select Use the following IP address and enter an IP address that should not conflict with your LAN. I use 192.168.1.200 and set the Subnet Mask to 255.255.255.0.
After you set the IP address you can use the Windows host file to assign a friendly address to this IP address. For example add this entry to C:\WINDOWS\system32\drivers\etc\hosts.
192.168.1.200 cool.example.com
When you do a drop table on Oracle 10g, you actually don’t drop it, it goes to the Recycle Bin, kind of like Windows. To empty the recycle bin do a purge recyclebin.
Oracle has a good article about this titled Flashback Drop (Recycle Bin).