SELECT dt, To_Char(dt,'Day') Days
FROM (
SELECT TO_DATE('&start_date', 'DD-MON-YYYY') + LEVEL - 1 AS dt
FROM dual
CONNECT BY LEVEL <= TO_DATE('&end_date', 'DD-MON-YYYY')
- TO_DATE('&start_date', 'DD-MON-YYYY') + 1
)
WHERE TO_CHAR(dt, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')
ORDER BY dt;
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:
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"
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;
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;
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.
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
How to fix IMP-00013: only a DBA can import a file exported by another DBA
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
WM_CONCAT is not working in ORACLE 12c and Oracle suggested an alternative solution is to use LISTAGG.
LISTAGG working fine in Database and Oracle Reports level,
The Easiest solution in this issue …..
You can try the following steps to create WM_CONCAT in ORACLE 12c ……
Step 1: Create a TYPE and TYPE BODY
—————————Script ——————–
DROP TYPE WMSYS.WM_CONCAT_IMPL;
CREATE OR REPLACE TYPE WMSYS.Wm_Concat_Impl wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
d
270 160
bg9hz+fBwa888VEZGViHFs/oOP0wg433f64df3QCWE7VehmhTFcUe3y+JrsniV3cSuvmnE3g
Y93dtR+cCsU1N+UQDGbtzhCf2HIdr8lPzfgF2bmCTvmGlHQbTAjTftNrDq3p093ncwb32OyX
3ZFDTeH2jpjm3uWYyT8kZBfJIYxRwgLfRApoW32cpy0eRnvDBt2XfTAMXKCSNnqSoTiGA83W
6deKW+rWyBu9L/EPyFkmQZeBncNsiNDF8fa1Sm6vdQiEanlCQnaPJ11a0na8hK6psDSaey+x
fdMupCwSvg6gMrSV4QCguhOCqW2AmxRVMqpXJootPpTBxBFZc7hORGbriUI=
/
DROP TYPE BODY WMSYS.WM_CONCAT_IMPL;
CREATE OR REPLACE TYPE BODY WMSYS.Wm_Concat_Impl wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
e
431 1d6
4E5KBnNGi9ZdccetOt5Qas902MUwgzsJr0gVfHRVWGSl8CvsyI9XoIRaVeA+MgrbNy0MKs17
YaSfXYacoF8yX8sh94R2lTMSM+u3LvBLTcKy2MTwPxIQP5IREfj5ZWZ44bhRRiFdQQ1plWkB
7MPMVFaJyn+9PoTQdVdiKGeou0Q8AYzVPPKrxfa3OY5tBmId+MA3bxWAbe3drFfb3dOYu7nQ
e7Yz1Bp8IAwzL/ckiVdYV2Qqex8DhTHPjuD0YvndS6zfRttr+q6uIwXTMrBB1OyNwiMmilYC
macOerMZg9T0sj6P8g66Z6eFS8Yiw0Z5KLtVy9TEhgT83sRiqwdJe8ve/+FVjUa9RYnbEN5A
AYhXVXTVAXphRT8pnzX2SMPuZt6w9oOa3mn8Ig8PhdU+CVFi+SGebpoKgoGKxG6cIyZ5T26/
w3bHJXj6ctAgIKT7+cahCLw6NnMqtSQ=
/
—————————————————————–
Step 2: Create the function wm_concat
————————Script———————————-
CREATE OR REPLACE function WMSYS.wm_concat wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
58 96
+CuW1MAfZPVR6NOnXKVmXY2o9pswg8eZgcfLCNL+XhZy8K7/cgzcVrPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvtVG+DmAOR4C+NTa+Pit2kDbEpI5zhZUgqameSkE
DQ==
/
———————————————–
Step 3: GRANT it to PUBLIC
————————Script———————————-
GRANT EXECUTE ON WMSYS.wm_concat TO PUBLIC;
———————————————–
The purpose is to reject two records that contain duplicated values
The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.
It uses two calculated items, one in the data bock and another in a control block.
The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:
Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)
Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.
Function COMPARAISON (val1 varchar2, val2 varchar2)
Return number
Is
answer number := 0;
Begin
if val1 = val2 then
answer := 1;
end if;
return(answer);
End;
COMPARAISON is a program unit stored in the Forms module.
The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.
The first value (:ctrl.charsave) contains the bakup value of the current record.
The DEPT block must have the following properties setting:
Query all records | YES |
The CTRL block must have the following properties setting:
Query all records | YES |
Single record | YES |
Database data block | NO |
The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.
It summarize the values contained in all the rows of the DEPT block (dept.match_found).
If the total is greater than 1, we have two duplicated data.
Normally this type of error raised when graph is using in Oracle 10g Reports which is configured on Windows 10. In order to avoid above error, please follow below steps:
1. Go to the properties of reports builder.It will surely work.
Courtesy By: Muhammed Azfar Nawaz
--Following are the steps to send email from oracle database--
CREATE DIRECTORY MYEMAIL AS 'C:\file_data';
GRANT READ,WRITE ON DIRECTORY MYEMAIL TO SCOTT;
-----------------------------------
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
DBMS_OUTPUT.put_line('TEST1');
DBMS_OUTPUT.put_line('TEST1');
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
END LOOP;
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
----------------------------------------------
CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS
dest_loc BLOB := empty_blob();
src_loc BFILE := BFILENAME('MYEMAIL', p_file_name);
BEGIN
-- Open source binary file from OS
DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
-- Create temporary LOB object
DBMS_LOB.CREATETEMPORARY(
lob_loc => dest_loc
, cache => true
, dur => dbms_lob.session
);
-- Open temporary lob
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
-- Load binary file into temporary LOB
DBMS_LOB.LOADFROMFILE(
dest_lob => dest_loc
, src_lob => src_loc
, amount => DBMS_LOB.getLength(src_loc));
-- Close lob objects
DBMS_LOB.CLOSE(dest_loc);
DBMS_LOB.CLOSE(src_loc);
-- Return temporary LOB object
RETURN dest_loc;
END loadBlobFromFile;
-------------------------------------------
DECLARE
l_blob BLOB;
BEGIN
SELECT loadBlobFromFile('ayaz.pdf')
INTO l_blob
FROM dual;
send_mail(p_to => 'faisalkhan@nbp.com.pk',
p_from => 'faisalkhan@nbp.com.pk',
p_subject => 'Message attached',
p_text_msg => 'This is a test message',
p_attach_name => 'ayaz.pdf',
p_attach_mime => 'image/gif',
p_attach_blob => l_blob,
p_smtp_host => 'nbparray.nbp.com.pk');
END;
/
--------------------------------
In which situations you may receive an ORA-1017?
