Picking up Number From a String Using Regular Expressions - Oracle Sql

 In Oracle SQL, you can use regular expressions to extract exactly the 5-digit number from a string like "Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934".

Here’s a one-liner using REGEXP_SUBSTR:

How to open a .csv file into excel using client_host (webutil configured in oracle forms)

 After Confirmation of your selected file existence in client machine, use below line code in oracle forms to force file to open:

Client_Host('cmd /c start excel.exe '||v_File_Name, No_Screen); 

--v_File_Name = Your selected filename with complete path e.g. "C:\temp\abc.csv"

How to export table data into CSV file using Oracle Forms Developer.

 

This article would demonstrate how to export table data into CSV file using Oracle Forms Developer.

 

To fulfil this task, you need Oracle Database, Oracle Forms Developer with client libraries installed e.g client_Text_IO. Follow the following steps to run the code given below:

 

1. Create “Location” table in your Oracle database

 Create table Location(

Location_code varchar(20),

state varchar(20),

Location_details varchar(100)

);

 

2. Insert some rows into the table

Insert into Location(‘LHE,’PUJ’,’LAHORE’);

Insert into Location(‘KHI’,’SIN’,’KARACHI’);

 

3. Paste the following code on button pressed trigger in your form. Then compile and run your form.

DECLARE

— File handler

lv_File client_Text_IO.File_Type;

 

 –File Name

lv_File_Name VarChar2(30);

 

 — Timer variable

lv_wait timer;

 

Cursor Load_Rows is

select

v.Location_code||’,’||

v.state||’,’||

v.Location_details data

from Location v;

 

BEGIN

— File Path

 

lv_File_Name := ‘C:\temp\Locations.csv’;

 

–Open file in write mode

lv_File :=  client_text_io.FOpen(lv_File_Name,’W’);

 

— Write header columns

client_text_io.Put_Line(lv_File,’Code,State,Location Name/Details,Locality,Post Code,Output Type,Active?’);

–Write rows into the file

FOR i IN Load_Rows LOOP

   client_text_io.Put_Line(lv_File, i.DATA);

END LOOP;

–Close File

client_text_io.FClose(v_File);

 

–Open file in read mode

lv_File :=  client_text_io.FOpen(lv_File_Name,’R’);

 

–Close file

client_text_io.FClose(lv_File);

lv_wait := create_timer(‘gen_doc’, 2500, NO_REPEAT);

END;

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
 

IMP-00013: only a DBA can import a file exported by another DBA

How to fix IMP-00013: only a DBA can import a file exported by another DBA

Error Description:

Import using imp utility is failing with following error.

Export file created by EXPORT:V11.02.00 via conventional path

IMP-00013: only a DBA can import a file exported by another DBA

IMP-00000: Import terminated unsuccessfully

Solution Description:

This is because of lack of privilege for importing the dump file. The export dump would have taken by a DBA privileged user but the user is trying to import the dump should have at least IMP_FULL_DATABASE privilege or a DBA privileged user can import the dump easily. You can give the access using the following syntax.

Syntax: grant IMP_FULL_DATABASE to username;

Example:

imp file=exp_schema.dmp log=imp_exp_schema.log fromuser=scott touser=test

Import: Release 11.2.0.2.0 - Production on Mon Oct 15 02:03:41 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: test

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

IMP-00013: only a DBA can import a file exported by another DBA

IMP-00000: Import terminated unsuccessfully

SQL> conn test/test

Connected.

SQL> select * from session_roles;

no rows selected

SQL> conn / as sysdba

Connected.

SQL> grant IMP_FULL_DATABASE to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> select * from session_roles;

ROLE

------------------------------

IMP_FULL_DATABASE

SELECT_CATALOG_ROLE

HS_ADMIN_SELECT_ROLE

EXECUTE_CATALOG_ROLE

HS_ADMIN_EXECUTE_ROLE

Trying the import after granting the privilege and it worked.

imp file=exp_schema.dmp log=imp_exp_schema.log fromuser=scott touser=test

Import: Release 11.2.0.2.0 - Production on Mon Oct 15 02:06:34 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: test/test

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing SCOTT's objects into TEST

. . importing table                      "My_Tab"      1000 rows imported

Import terminated successfully without warnings.

Oracle SQL: Generating Dates in Sequence & Time in Sequence

To achieve our task we use connect by level in our SQL to generate the followings:

SQL for Generating Dates in Sequence:

Select To_Date(Sysdate-30, 'dd-mm-rrrr') + (Level - 1) Dates
From Dual
Connect By Level <= ((To_Date(Sysdate, 'dd-mm-rrrr') -
To_Date(Sysdate-30, 'dd-mm-rrrr')) + 1);

Result:

SQL for Generating Time in Sequence:

Select Level,
To_Char(Trunc(Sysdate) + (Level - 1) / 24, 'YYYY-MM-DD HH24:MI') As Date_Hour,
To_Char(Trunc(Sysdate) + (Level - 1) / 24, 'HH24:MI') As Hours
From Dual
Connect By Level <= 24;

Result: