C
Frequently Asked Questions

A text file containing a list of frequently asked questions is available on line after installing Visual Information Retrieval Cartridge. Depending on your operating system, the file may be located in:

<ORACLE_HOME>/ord/vir/doc/ordrfaq.txt

A few of questions from the FAQ have been included in this appendix for emphasis.

C.1 Errors During Installation

Symptom:

SQL> grant execute on ordvirb to public;
ORA-04042: procedure, function, package, or package body does not exist

Solution:

Check that you created the type in the ORDSYS user.

Symptom:

MGR-00072: Warning: PACKAGE BODY VIR created with compilation errors.

Symptom:

PLS-00201: identifier 'ORDVIRLIBS' must be declared                    

Solution:

The shared library doesn't exist. Recreate the library.

For UNIX systems:

SQL> create library ordvirlibS as 'oraclehome/lib/libordvir.so';

where oraclehome is the value of the ORACLE_HOME environment variable.

For Windows NT systems:

SQL> create library ordvirlibS as 'C:\ORANT\BIN\ORDVIR80.DLL';

where C:\ORANT is the value of the Oracle home directory.

C.2 Errors Using the Image Types

Symptom:

ORA-06520: PL/SQL: Error loading external library
ORA-06522: ld.so.1: extprocextproc: fatal: /path/libordvir.so: can't open file: 
errno=2
ORA-06512: at "ORDSYS.VIR", line 207
ORA-06512: at "ORDSYS.ORDVIRF", line 8

Solution:

The path for the shared library is invalid. Recreate the library with the full path name of the library.

For UNIX systems:

SQL> create library ordvirlibS as '/oraclehome/lib/libordvir.so';

where oraclehome is the value of the ORACLE_HOME environment variable.

For Windows NT systems:

SQL> create library ordvirlibS as 'C:\ORANT\BIN\ORDVIR80.DLL';

where C:\ORANT is the value of the Oracle home directory.

Symptom:

ORA-04068: existing state of packages has been discarded
ORA-04063: package body "ORDSYS.VIR" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "ORDSYS.ORDVIRF", line 8

Solution:

The library ORDVIRLIBS may not exist or the package needs to be recompiled. Recompile the package.

SQL> connect ordsys/ordsys;
SQL> alter package VIR compile

C.3 RPC Errors After Installation

Symptom:

ORA-28576: lost RPC connection to external procedure agent    

Answer:

The connection information in tnsnames.ora and listener.ora is correct, but the program failed. This is due to conflicting SID_NAME/SID entries in the listener.ora and tnsnames.ora files. In other words, the extproc SID_NAME in listener.ora is also being used by another entry.

Solution:

Change the SID_NAME for the extproc entry to something unique, such as extproc or extproc2. Be sure to update the SID entry for extproc_connection_data in the tnsnames.ora file to also refer to extproc or extproc2.

Answer:

It is also possible, although unlikely, that there is a bug in the external callout that caused the program to crash. This, however, shouldn't happen with the imgdemo.dat image shipped with the product.

Solution:

Test using the imgdemo.dat image. If there are errors, then use the first solution above to track down a problem with the external callout mechanism configuration.

Solution:

If there are no errors with the imgdemo.dat image, but you get this error when you perform the same operations on your custom images that we support, then you've found a bug. Please notify your Oracle Worldwide Customer Support Services representative.

Symptom:

ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "ORDSYS.VIR", line 207
ORA-06512: at "ORDSYS.ORDVIRF", line 8

Answer:

The external callout program 'extproc' in your tnsnames.ora or listener.ora file is either invalid or not present.

Solution:

Add/edit the extproc entry in tnsnames.ora and listener.ora. Check the network configuration guide for your platform for details on creating an entry for extproc. After you edit the listener.ora file, you must stop and restart the listener.

For UNIX: Add this line in

/oraclehome/network/admin/tnsnames.ora

extproc_connection_data =
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=yourkey))
(CONNECT_DATA=(SID=extproc)))   

Add/edit this line in

/oraclehome/network/admin/listener.ora

LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=ipc)(KEY=yourkey))
)
SID_LIST_LISTENER = (SID_LIST=
 (SID_DESC=(SID_NAME=yourdb)(ORACLE_HOME=/vobs/oracle))
 (SID_DESC=(SID_NAME=extproc)(ORACLE_HOME=/vobs/oracle)
(PROGRAM=extproc))
)

To stop and restart the listener: lsnrctl

set password oracle
stop
start
quit

For Windows NT systems: Add/edit this line in
C:\ORANT\NET80\ADMIN\TNSNAMES.ora

extproc_connection_data.world = (
   (DESCRIPTION = 
   (ADDRESS = 
   PROTOCOL=tcp) (HOST=yourhost) (PORT=1521)
   CONNECT_DATA = (SID=extproc))

Add or edit the following line in
C:\ORANT\NET80\ADMIN\LISTENER.ora

SID_LIST_LISTENER = 
  (SID_LIST=
     (SID_DESC=
     (SIDNAME=ORCL)
  )
   (SID_DESC=
      (SID_NAME=extproc) (PROGRAM=extproc)
   )
 )

To stop and restart the listener: lsnrctl80

set password oracle
stop
start
quit

Or, open the control panel and open services. Stop and restart the OracleTNSListenerXX service.

C.4 SQL Select Statement Does Not Work

Symptom:

SQL> create table image (width integer);
SQL> insert into image values (123);
SQL> select width, image.width from image,images;
WIDTH      WIDTH     
---------- ----------
       123        123

Solution:

You should always use a table alias when using types to prevent naming conflicts with other objects. Rerun the statement using a table alias.

SQL> select width,t.image.width from image,images t;
WIDTH      IMAGE.WIDTH
---------- ----------
       123        600

C.5 Extract the File Name from a BFILE Image

You can get the file name and directory name using the dbms_lob package.

connect scott/tiger;
set serveroutput on
declare
  virf ordsys.ordvirf;
  filename varchar2(256);
  filepath varchar2(256);
begin
  select imagef into virf from files where id=1;
  dbms_lob.filegetname(virf.content,filepath,filename);
   -- filename is the name of the file
   -- filepath is the name of the directory
   dbms_output.put_line('The file ' || filename || 
      ' is in a directory known as ' || filepath || '.');
end;

This example would produce the following output:

The file imgdemo.dat is in a directory known as IMAGES.

The full path name may be found in the view named ALL_DIRECTORIES. The following example selects the full path for the images directory:

SQL> select directory_path from all_directories where directory_name = 'IMAGES';

---