How to play a video file from a database table?

To play a video file from a database table, you need to store the video file as a BLOB (binary large object) in the table and use Webutil to access it from Oracle Forms. You also need to use the host built-in procedure to invoke a media player that can play the video file. Here is a possible code snippet that you can modify according to your needs:

declare
  v_blob blob;
  v_filename varchar2(100);
  v_player varchar2(100) := 'C:\\Program Files\\Windows Media Player\\wmplayer.exe';
begin
  select video into v_blob from tfile where id = :block.id; -- assuming you have a block with an id item
  v_filename := webutil_file_transfer.as_to_client_with_progress(v_blob,'video.mp4','Downloading video file...'); -- assuming the video file is mp4 format
  host(v_player || ' ' || v_filename); -- invoking the media player with the video file as argument
end;

This code queries the video column from the tfile table based on the id item in the current block1. Then it uses the webutil_file_transfer.as_to_client_with_progress function to transfer the BLOB to a temporary file on the client machine. Finally, it uses the host procedure to launch the Windows Media Player with the temporary file as an argument.

I hope this helps you. Please note that this code is not tested and may not work as expected. You may need to do some adjustments and configurations to make it work.

but host procedure cannot be used to invoke client files?

Sent message. but host procedure cannot be used to invoke client files

You are right, the host procedure can only invoke executables on the application server, not on the client machine. To invoke executables on the client machine, you need to use the webutil_host.host procedure instead. Here is how you can modify the code:

declare
  v_blob blob;
  v_filename varchar2(100);
  v_player varchar2(100) := 'C:\\Program Files\\Windows Media Player\\wmplayer.exe';
begin
  select video into v_blob from tfile where id = :block.id; -- assuming you have a block with an id item
  v_filename := webutil_file_transfer.as_to_client_with_progress(v_blob,'video.mp4','Downloading video file...'); -- assuming the video file is mp4 format
  webutil_host.host(v_player || ' ' || v_filename); -- invoking the media player on the client machine with the video file as argument
end;

This code uses the webutil_host.host procedure instead of the host procedure to launch the Windows Media Player on the client machine. You need to make sure that you have configured Webutil properly and granted the necessary privileges to use this procedure.

How to create a DB link between two oracle database instances

Oracle has invested heavily in distributed database technology and the creation of a database link is very straightforward.  You specify the database link name, the remote user to connect to, the password for the remote user and the TNS service name for the database link connection:

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'tns_service_name';

Starting in 11g release 2, the syntax has been enhanced to remove the need to epscify a TNS service_name:

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'myserver:1521/MYSID';

Above we see that the TNS instance name has been replaced by allowing the server name, port number and Oracle system ID (ORACLE_SID).

You can also create a database link to non-Oracle databases, the steps for a database link to MySQL:

Step 1:  Your first step is having installed the TRANSPARENT GATEWAY (it comes in as of the options when you install Oracle).

Step 2: You must have a user in the SQL Server.

Step 3: In the directory <ORACLE_HOME>\tg4msql\admin look for the file inittg4msql.ora and have the following options:

HS_FDS_CONNECT_INFO="SERVER=name_server;DATABASE=name_db"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=user_sqlserver
HS_FDS_RECOVERY_PWD=pass_user_sqlserver

Step 4: Configure the listener and add the following

(SID_DESC =
(PROGRAM = tg4msql)
(SID_NAME = MYSQL)
(ORACLE_HOME = C:\oracle1)
)
)

and in our tnsnames.ora add

MYSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MYSQL)
)
(HS=OK)
)

where HS set up as a heterogonous service

Step 5: create a link to sql server.

create database link mysql connect to user_in_sqlserver identified by <password> using 'MYSQL';

Step 6: You can now use the database link to a foreign database:

select * from table@mysql