How to enable/disable case sensitive passwords in Oracle11g?

Case-sensitive passwords are a new security feature introduced in Oracle 10g, however this feature is enabled by default in Oracle 11g.

If you utilise an older version of Oracle Forms (6i in my instance) with an Oracle 11g database, case-sensitive passwords may cause issues. If the passwords don't match, you can't login to the database since the LOGON built-in transmits the password to the database in UPPERCASE.

Oracle 11g requires a modification to the SEC_CASE_SENSITIVE_LOGON startup option in order to deactivate case-sensitive passwords:

Step 1:

SQL> connect sys/oracle as sysdba

Step 2:

SQL> show parameter SEC_CASE_SENSITIVE_LOGON

Step 3:

SQL> sec_case_sensitive_logon       boolean   TRUE

Step 4:

SQL> alter system set sec_case_sensitive_logon=false scope=both;

Revert the value back to true to allow case-sensitive passwords once more.

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.

Read More

How to delete duplicate rows from a table

 

SQL> SELECT * FROM EMP;

        ID NAME
---------- --------------------
        10 SCOTT
        10 SCOTT
        20 HELLOW WORLD
        30 SMITH
        20 HELLOW WORLD
        30 SMITH

6 rows selected.

SQL> DELETE FROM TEST

WHERE ROWID NOT IN (SELECT MIN (ROWID)

FROM TEST

GROUP BY ID,NAME);

3 rows deleted.

SQL> SELECT * FROM EMP;

        ID NAME
---------- --------------------
        10 SCOTT
        20 HELLOW WORLD
        30 SMITH

SQL> COMMIT;

Commit complete.

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.

Read More

Send Email From Oracle Database

 The steps to send emails from an Oracle database are as follows:

Step 1:

CREATE DIRECTORY MYEMAIL AS 'C:\file_data';

GRANT READ,WRITE ON DIRECTORY MYEMAIL TO SCOTT;

Step 2:

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;

Step 3:

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;

Step 4:

DECLARE

  l_blob BLOB;

BEGIN

  

  SELECT   loadBlobFromFile('ayaz.pdf')

  INTO   l_blob

  FROM   dual;

  send_mail(p_to          => '(Email)@(Domain).com.pk',

            p_from        => '(Email)@(Domain).com.pk',

            p_subject     => 'Message attached',

            p_text_msg    => 'This is a test message',

            p_attach_name => 'abc.pdf',

            p_attach_mime => 'image/gif',

            p_attach_blob => l_blob,

            p_smtp_host   => '(SMTP Email)');

END;

Read More

Oracle Forms: Avoid duplicated records in a block

 

Rejecting two records with duplicate values is the goal.

Two computed items are utilised, one in the data block and the other in the control block.

The DEPT block now contains the first computed item (:DEPT.MATCH FOUND). It includes the following formula:

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.

If the two numbers are equal, the function compares them and returns 1 (a value greater than 0). Otherwise, it returns 0.

The current record's backup value is contained in the first value (:ctrl.charsave).

The DEPT block must be configured with the following properties:

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 computed item is appended to the CTRL block as ":CTRL.MATCH FOUND".

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.

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.

Read More