Accessing an Oracle Table: Relation Level Interface

Assuming you have access permission for the table you wish to import, you can use db_import/2 as:

| ?- db_import('TABLENAME'('FIELD1', 'FIELD2', .., 'FIELDn'), 'Pname').

where 'TABLENAME' is the name of the table you wish to access and 'Pname' is the name of the predicate you wish to use to access the table from XSB. 'FIELD1' through 'FIELDn' are the exact attribute names as defined in the database catalog. The chosen attributes define the view and the order of arguments for the database predicate 'Pname'. For example, to create a link to the DEPT table through the 'dept' predicate:

| ?- db_import('DEPT'('DEPTNO','DNAME','LOC'),dept).

yes
| ?- dept(Deptno, Dname, Loc).

Deptno = 10
Dname = ACCOUNTING
Loc = NEW YORK

Backtracking can then be used to retrieve the next row of the table DEPT.

Records with particular field values may be selected in the same way as in Prolog. (In particular, no mode specification for database predicates is required). For example:

| ?- dept(A, 'ACCOUNTING', C).

generates the query:

SELECT DEPTNO, LOC
FROM DEPT rel1
WHERE rel1.DNAME = :BIND1;

and

| ?- dept('NULL'(_), 'ACCOUNTING', C).

generates: (See section 4.3.7)

SELECT NULL , rel1.DNAME , rel1.LOC
FROM DEPT rel1
WHERE rel1.DEPTNO IS NULL AND rel1.DNAME = :BIND1;

During the execution of this query the :BIND1 variable will be bound to 'ACCOUNTING'.
If a field includes a quote (') then this should be represented by using two quotes.

Note that the relation level interface can be used to define and access simple project views of single tables. For example:

| ?- db_import('DEPT'('DEPTNO','DNAME'),deptview).

defines deptview/2.

The predicate db_import/2 (and other Oracle interface predicates) automatically asserts data dictionary information. You can use the Prolog predicate listing/2 to see the asserted data dictionary information at any time.

Note: as a courtesy to Quintus Prolog users we have provided compatibility support for some PRODBI predicates which access tables at a relational level.

i) | ?- db_attach(Pname, table(Tablename)).

eg. execute

| ?- db_attach(dept, table('DEPT')).

then execute

| ?- dept(Depno, Dname, Loc).

to retrieve the rows.

ii) | ?- db_record('DEPT', R).

    R = [20,RESEARCH,DALLAS];

    R = ...

You can use db_record/2 to treat the whole database row as a single list structure.