Guardar un .avi en Oracle
¿Como puedo guardar un fichero de video, extensión .avi en una base de datos Oracle.
Me he definido una tabla con un campo long raw pero no se como insertar el archivo avi.
Gracias.
Me he definido una tabla con un campo long raw pero no se como insertar el archivo avi.
Gracias.
Respuesta de missoracle
1
Es un gusto poder ayudarte. Me gustaría que versión de BD tienes, porque tengo una solución para el cargue de videos en una tabla para la versión 8i.
-- Si observas lo que te envío a continuación es bastante sencillo. Otra cosa para tener en cuenta es el sistema operativo en el que tienes el vídeo. No te se decir si no es Unix sino Windows aplica de igual forma. Puedes probar y hablamos.
PURPOSE ------- This sample PLSQL code illustrate the ORDSYS.ORDVIDEO object interface available in Oracle8i. The ORDSYS.ORDVIDEO API permits to load/unload sound files into ORDSYS.ORDVIDEO objects from a filesystem or on the web. SCOPE & APPLICATION ------------------- Developper community. Overview -------- This code permits to load/unload video files from a filesystem into a VIDEOS table. The code also illustrate which attributes are stored. The ORDSYS.ORDVIDEO API is also used in the Oracle Code Wizard for the PL/SQL Gateway, a tools to upload/download video files via a webserver directly in ORDSYS.ORDVIDEO objects, and in Annotation Services, a client tools that permits to manipulate ORDSYS.ORDVIDEO from a GUI interface.( see http://otn.oracle.com/software/products/intermedia/content.html ) ORDSYS.ORDVIDEO PLSQL API DEMO ------------------------------ - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - connect sys/change_on_install as sysdba PROMPT As sys create directory 'MYDIR' and grant read on that directory to scott create or replace directory MYDIR as 'd:\media'; grant read on directory MYDIR to scott with grant option; grant javauserpriv to scott; grant javasyspriv to ordplugins; grant javasyspriv to ordsys; call dbms_java.grant_permission('SCOTT','java.io.FilePermission', 'd:\media\*','write'); PROMPT Create table videos to store the video files in an application schema connect scott/tiger set serveroutput on create table videos (id number primary key, vid ORDSYS.ORDVIDEO); PROMPT create a package to handle videos create or replace package video_mgmt as procedure load_video_from_dir(num in integer, filename in varchar2); procedure unload_video_to_dir (num in integer, filename in varchar2); procedure video_info (num in integer); end; / create or replace package body video_mgmt as myVideo ORDSYS.ORDVIDEO; ctx RAW(4000) := NULL; stmt varchar2(300); /* Create a procedure to load video from filesystem into the database */ procedure load_video_from_dir (num in integer, filename in varchar2) is begin insert into videos values(num, ORDSYS.ORDVIDEO.init('FILE','MYDIR',filename)); select vid into myVideo from videos where id = num FOR UPDATE; --- we import the video into the table using a buffer (ctx). myVideo.import(ctx); --- we do a setproperties on the object which sets the --- attributes of the object such as mimetype from the metadata in --- the video file. myVideo.setproperties(ctx); update videos set vid=myVideo where id=num; commit; dbms_output.put_line('video '||filename||' loaded in key '||num); exception when ORDSYS.ORDSourceExceptions.INCOMPLETE_SOURCE_INFORMATION then rollback; DBMS_OUTPUT.PUT_LINE ('Import: value of the source type is NULL'); when ORDSYS.ORDSourceExceptions.NULL_SOURCE then rollback; DBMS_OUTPUT.PUT_LINE('Import: value of source video is NULL'); when ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED then rollback; DBMS_OUTPUT.PUT_LINE('Import: method is not supported'); -- to see the complete errormessage for the source plugin exception, -- you might remove the following exception handling for the -- source_plugin_exception when ORDSYS.ORDSOURCEExceptions.SOURCE_PLUGIN_EXCEPTION then rollback; DBMS_OUTPUT.PUT_LINE('Import: source plugin exception caught'); when ORDSYS.ORDVideoExceptions.VIDEO_PLUGIN_EXCEPTION then rollback; DBMS_OUTPUT.PUT_LINE('setProperties: video plug-in exception caught'); when dup_val_on_index then dbms_output.put_line('video '||num||' already exists'); end; /* Create the procedure to unload video from the database */ procedure unload_video_to_dir (num in integer, filename in varchar2) is begin select vid into myVideo from videos where id=num; --- we export the video into the table using a buffer (ctx). if (myVideo.isLocal) then myVideo.export(ctx, 'FILE', 'MYDIR', filename); dbms_output.put_line('video '||num||' exported'); else dbms_output.put_line('video cannot be exported since not stored locally in the database'); end if; exception when ORDSYS.ORDSourceExceptions.INCOMPLETE_SOURCE_INFORMATION then DBMS_OUTPUT.PUT_LINE('Export: value of srcType is NULL'); when ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED then DBMS_OUTPUT.PUT_LINE('Export: method not supported'); -- to see the complete errormessage for the source plugin exception, -- you might remove the following exception handling for the source_plugin_exception when ORDSYS.ORDSOURCEExceptions.SOURCE_PLUGIN_EXCEPTION then DBMS_OUTPUT.PUT_LINE('Export: source plugin exception caught'); when ORDSYS.ORDSourceExceptions.IO_ERROR then DBMS_OUTPUT.PUT_LINE('Export: problems writing BLOB data to the file specified'); when no_data_found then dbms_output.put_line('no video exist for number : '||num); end; /* Create the procedure to print info about videos referenced in the database */ procedure video_info (num in integer) is width number; height number; begin select vid into myVideo from videos where id= num; dbms_output.put_line('video key = ' || num); begin dbms_output.put_line('video name = ' || myVideo.getSourceName); dbms_output.put_line('video source location = ' || myVideo.getSourceLocation||' on '|| myVideo.getSourceType); exception when ORDSYS.ORDSourceExceptions.incomplete_source_location then dbms_output.put_line('no source location exists'); when ORDSYS.ORDSourceExceptions.incomplete_source_name then dbms_output.put_line('no source name exists'); end; dbms_output.put_line('file format = ' || myVideo.getFormat); myVideo.getFrameSize(width, height); dbms_output.put_line('frame size = ' || width ||'x'|| height); dbms_output.put_line('frame resolution = ' || myVideo.getFrameResolution); dbms_output.put_line('frame rate = ' || myVideo.getFrameRate); dbms_output.put_line('video duration = ' || myVideo.getVideoDuration); dbms_output.put_line('number of frames = ' || myVideo.getNumberOfFrames); dbms_output.put_line('compression type = ' || myVideo.getCompressionType); dbms_output.put_line('number of colors = ' || myVideo.getNumberOfColors); dbms_output.put_line('video bitrate = ' || myVideo.getBitRate); dbms_output.put_line('mime type = ' || myVideo.getMimeType); begin dbms_output.put_line('description = ' || myVideo.getDescription); exception when ORDSYS.ORDVIDEOExceptions.DESCRIPTION_IS_NOT_SET then dbms_output.put_line('no description set'); end; dbms_output.put_line('audio comments length = ' || myVideo.getCommentLength); if (myVideo.isLocal) then dbms_output.put_line('audio file is locally stored in the database'); else dbms_output.put_line('audio file is only referenced in the database'); end if; exception when no_data_found then dbms_output.put_line('no video exist for number : '||num); end; end; / show error PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROMPT Insert some video into the videos table with load_video procedure PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ exec video_mgmt.load_video_from_dir(1, 'oracle.rm'); exec video_mgmt.load_video_from_dir(2, 'oracle.mov'); exec video_mgmt.load_video_from_dir(3, 'oracle.avi'); PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROMPT Display what has been loaded PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ exec video_mgmt.video_info(1); exec video_mgmt.video_info(2); exec video_mgmt.video_info(3); PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROMPT Export some video from the videos table with unload_video procedure PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ exec video_mgmt.unload_video_to_dir(1, 'oracle_bis.rm'); exec video_mgmt.unload_video_to_dir(2, 'oracle_bis.mov'); exec video_mgmt.unload_video_to_dir(3, 'oracle_bis.avi'); - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Sample Output ------------- Connected. As sys create directory 'MYDIR' and grant read on that directory to scott Directory created. Grant succeeded. Grant succeeded. Call completed. Create table videos to store the video files in an application schema Connected. Table created. create a package to handle videos Package created. Package body created. No errors. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Insert some video into the videos table with load_video procedure +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ video oracle.rm loaded in key 1 PL/SQL procedure successfully completed. video oracle.mov loaded in key 2 PL/SQL procedure successfully completed. video oracle.avi loaded in key 3 PL/SQL procedure successfully completed. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Display what has been loaded +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ video key = 1 video name = oracle.rm video source location = MYDIR on FILE file format = RMFF frame size = x frame resolution = frame rate = video duration = 17 number of frames = compression type = number of colors = video bitrate = 15000 mime type = audio/x-pn-realaudio no description set audio comments length = 0 audio file is locally stored in the database PL/SQL procedure successfully completed. video key = 2 video name = oracle.mov video source location = MYDIR on FILE file format = MOOV frame size = 160x120 frame resolution = frame rate = 0 video duration = 30 number of frames = 0 compression type = CVID number of colors = 24 video bitrate = mime type = video/quicktime no description set audio comments length = 0 audio file is locally stored in the database PL/SQL procedure successfully completed. video key = 3 video name = oracle.avi video source location = MYDIR on FILE file format = AVI frame size = 160x120 frame resolution = frame rate = 15 video duration = number of frames = compression type = CRAM number of colors = video bitrate = mime type = video/x-msvideo no description set audio comments length = 0 audio file is locally stored in the database PL/SQL procedure successfully completed. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Export some video from the videos table with unload_video procedure +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ video 1 exported PL/SQL procedure successfully completed. video 2 exported PL/SQL procedure successfully completed. video 3 exported PL/SQL procedure successfully completed. SQL> spool off
-- Si observas lo que te envío a continuación es bastante sencillo. Otra cosa para tener en cuenta es el sistema operativo en el que tienes el vídeo. No te se decir si no es Unix sino Windows aplica de igual forma. Puedes probar y hablamos.
PURPOSE ------- This sample PLSQL code illustrate the ORDSYS.ORDVIDEO object interface available in Oracle8i. The ORDSYS.ORDVIDEO API permits to load/unload sound files into ORDSYS.ORDVIDEO objects from a filesystem or on the web. SCOPE & APPLICATION ------------------- Developper community. Overview -------- This code permits to load/unload video files from a filesystem into a VIDEOS table. The code also illustrate which attributes are stored. The ORDSYS.ORDVIDEO API is also used in the Oracle Code Wizard for the PL/SQL Gateway, a tools to upload/download video files via a webserver directly in ORDSYS.ORDVIDEO objects, and in Annotation Services, a client tools that permits to manipulate ORDSYS.ORDVIDEO from a GUI interface.( see http://otn.oracle.com/software/products/intermedia/content.html ) ORDSYS.ORDVIDEO PLSQL API DEMO ------------------------------ - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - connect sys/change_on_install as sysdba PROMPT As sys create directory 'MYDIR' and grant read on that directory to scott create or replace directory MYDIR as 'd:\media'; grant read on directory MYDIR to scott with grant option; grant javauserpriv to scott; grant javasyspriv to ordplugins; grant javasyspriv to ordsys; call dbms_java.grant_permission('SCOTT','java.io.FilePermission', 'd:\media\*','write'); PROMPT Create table videos to store the video files in an application schema connect scott/tiger set serveroutput on create table videos (id number primary key, vid ORDSYS.ORDVIDEO); PROMPT create a package to handle videos create or replace package video_mgmt as procedure load_video_from_dir(num in integer, filename in varchar2); procedure unload_video_to_dir (num in integer, filename in varchar2); procedure video_info (num in integer); end; / create or replace package body video_mgmt as myVideo ORDSYS.ORDVIDEO; ctx RAW(4000) := NULL; stmt varchar2(300); /* Create a procedure to load video from filesystem into the database */ procedure load_video_from_dir (num in integer, filename in varchar2) is begin insert into videos values(num, ORDSYS.ORDVIDEO.init('FILE','MYDIR',filename)); select vid into myVideo from videos where id = num FOR UPDATE; --- we import the video into the table using a buffer (ctx). myVideo.import(ctx); --- we do a setproperties on the object which sets the --- attributes of the object such as mimetype from the metadata in --- the video file. myVideo.setproperties(ctx); update videos set vid=myVideo where id=num; commit; dbms_output.put_line('video '||filename||' loaded in key '||num); exception when ORDSYS.ORDSourceExceptions.INCOMPLETE_SOURCE_INFORMATION then rollback; DBMS_OUTPUT.PUT_LINE ('Import: value of the source type is NULL'); when ORDSYS.ORDSourceExceptions.NULL_SOURCE then rollback; DBMS_OUTPUT.PUT_LINE('Import: value of source video is NULL'); when ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED then rollback; DBMS_OUTPUT.PUT_LINE('Import: method is not supported'); -- to see the complete errormessage for the source plugin exception, -- you might remove the following exception handling for the -- source_plugin_exception when ORDSYS.ORDSOURCEExceptions.SOURCE_PLUGIN_EXCEPTION then rollback; DBMS_OUTPUT.PUT_LINE('Import: source plugin exception caught'); when ORDSYS.ORDVideoExceptions.VIDEO_PLUGIN_EXCEPTION then rollback; DBMS_OUTPUT.PUT_LINE('setProperties: video plug-in exception caught'); when dup_val_on_index then dbms_output.put_line('video '||num||' already exists'); end; /* Create the procedure to unload video from the database */ procedure unload_video_to_dir (num in integer, filename in varchar2) is begin select vid into myVideo from videos where id=num; --- we export the video into the table using a buffer (ctx). if (myVideo.isLocal) then myVideo.export(ctx, 'FILE', 'MYDIR', filename); dbms_output.put_line('video '||num||' exported'); else dbms_output.put_line('video cannot be exported since not stored locally in the database'); end if; exception when ORDSYS.ORDSourceExceptions.INCOMPLETE_SOURCE_INFORMATION then DBMS_OUTPUT.PUT_LINE('Export: value of srcType is NULL'); when ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED then DBMS_OUTPUT.PUT_LINE('Export: method not supported'); -- to see the complete errormessage for the source plugin exception, -- you might remove the following exception handling for the source_plugin_exception when ORDSYS.ORDSOURCEExceptions.SOURCE_PLUGIN_EXCEPTION then DBMS_OUTPUT.PUT_LINE('Export: source plugin exception caught'); when ORDSYS.ORDSourceExceptions.IO_ERROR then DBMS_OUTPUT.PUT_LINE('Export: problems writing BLOB data to the file specified'); when no_data_found then dbms_output.put_line('no video exist for number : '||num); end; /* Create the procedure to print info about videos referenced in the database */ procedure video_info (num in integer) is width number; height number; begin select vid into myVideo from videos where id= num; dbms_output.put_line('video key = ' || num); begin dbms_output.put_line('video name = ' || myVideo.getSourceName); dbms_output.put_line('video source location = ' || myVideo.getSourceLocation||' on '|| myVideo.getSourceType); exception when ORDSYS.ORDSourceExceptions.incomplete_source_location then dbms_output.put_line('no source location exists'); when ORDSYS.ORDSourceExceptions.incomplete_source_name then dbms_output.put_line('no source name exists'); end; dbms_output.put_line('file format = ' || myVideo.getFormat); myVideo.getFrameSize(width, height); dbms_output.put_line('frame size = ' || width ||'x'|| height); dbms_output.put_line('frame resolution = ' || myVideo.getFrameResolution); dbms_output.put_line('frame rate = ' || myVideo.getFrameRate); dbms_output.put_line('video duration = ' || myVideo.getVideoDuration); dbms_output.put_line('number of frames = ' || myVideo.getNumberOfFrames); dbms_output.put_line('compression type = ' || myVideo.getCompressionType); dbms_output.put_line('number of colors = ' || myVideo.getNumberOfColors); dbms_output.put_line('video bitrate = ' || myVideo.getBitRate); dbms_output.put_line('mime type = ' || myVideo.getMimeType); begin dbms_output.put_line('description = ' || myVideo.getDescription); exception when ORDSYS.ORDVIDEOExceptions.DESCRIPTION_IS_NOT_SET then dbms_output.put_line('no description set'); end; dbms_output.put_line('audio comments length = ' || myVideo.getCommentLength); if (myVideo.isLocal) then dbms_output.put_line('audio file is locally stored in the database'); else dbms_output.put_line('audio file is only referenced in the database'); end if; exception when no_data_found then dbms_output.put_line('no video exist for number : '||num); end; end; / show error PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROMPT Insert some video into the videos table with load_video procedure PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ exec video_mgmt.load_video_from_dir(1, 'oracle.rm'); exec video_mgmt.load_video_from_dir(2, 'oracle.mov'); exec video_mgmt.load_video_from_dir(3, 'oracle.avi'); PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROMPT Display what has been loaded PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ exec video_mgmt.video_info(1); exec video_mgmt.video_info(2); exec video_mgmt.video_info(3); PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROMPT Export some video from the videos table with unload_video procedure PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ exec video_mgmt.unload_video_to_dir(1, 'oracle_bis.rm'); exec video_mgmt.unload_video_to_dir(2, 'oracle_bis.mov'); exec video_mgmt.unload_video_to_dir(3, 'oracle_bis.avi'); - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Sample Output ------------- Connected. As sys create directory 'MYDIR' and grant read on that directory to scott Directory created. Grant succeeded. Grant succeeded. Call completed. Create table videos to store the video files in an application schema Connected. Table created. create a package to handle videos Package created. Package body created. No errors. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Insert some video into the videos table with load_video procedure +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ video oracle.rm loaded in key 1 PL/SQL procedure successfully completed. video oracle.mov loaded in key 2 PL/SQL procedure successfully completed. video oracle.avi loaded in key 3 PL/SQL procedure successfully completed. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Display what has been loaded +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ video key = 1 video name = oracle.rm video source location = MYDIR on FILE file format = RMFF frame size = x frame resolution = frame rate = video duration = 17 number of frames = compression type = number of colors = video bitrate = 15000 mime type = audio/x-pn-realaudio no description set audio comments length = 0 audio file is locally stored in the database PL/SQL procedure successfully completed. video key = 2 video name = oracle.mov video source location = MYDIR on FILE file format = MOOV frame size = 160x120 frame resolution = frame rate = 0 video duration = 30 number of frames = 0 compression type = CVID number of colors = 24 video bitrate = mime type = video/quicktime no description set audio comments length = 0 audio file is locally stored in the database PL/SQL procedure successfully completed. video key = 3 video name = oracle.avi video source location = MYDIR on FILE file format = AVI frame size = 160x120 frame resolution = frame rate = 15 video duration = number of frames = compression type = CRAM number of colors = video bitrate = mime type = video/x-msvideo no description set audio comments length = 0 audio file is locally stored in the database PL/SQL procedure successfully completed. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Export some video from the videos table with unload_video procedure +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ video 1 exported PL/SQL procedure successfully completed. video 2 exported PL/SQL procedure successfully completed. video 3 exported PL/SQL procedure successfully completed. SQL> spool off
