Sponsored

Recovering All Corrupted Blocks in Oracle

You can recover all corrupted blocks with a single command in Rman. All database blocks corruptions are reported to V$DATABASE_BLOCK_CORRUPTION.

To recover all the data blocks who are listed in above view. Follow these steps:

Run Rman

After connected to Rman with Connect Target command type the following command:

Recover Corruption List;

All data block will be recovered and will be removed from the above mentioned view.


Women Invent Pill Monitoring System

Most of us have done it - forgotten if we have taken prescribed medication. 

"She originally came up with a concept of a tab system that you would pull off the bottle of medication itself that would say, 'Okay, I took my medication that day.' From there we just kind of came up with the engineering idea of how it could fit every type of medication packaging," said Beadling.



Read more... http://www.wwnytv.com/news/local/Your-Health-Local-Women-Invent-Pill-Monitoring-System-188260781.html 

Running Oracle commands in Vb.net with Tns less  connection string.

Imports required for the following procedure are:
Imports System.Data
Imports System.Data.OleDb

Pass the oracle command as parameter to following procedure.

Public Sub execute_oracledb_command(ByVal cmdstring As String)
        Dim oraconnection As OleDbConnection
        Dim oracommand As OleDbCommand

        dim srcconnect as string = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=YourHost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=YourService)));User Id=YourUser;Password=YourPsw;"

        oraconnection = New OleDbConnection(srcconnect)
        Try
            oraconnection.Open()
            'opening the connection
            oracommand = New OleDbCommand(cmdstring, oraconnection)
            oracommand.ExecuteNonQuery()
            oraconnection.Close()
        Catch ee As Exception
            messagebox.show(ee.message)
        End Try
End Sub

This blog describes the procedure to add a Feedback Form with blog rating option in blogger as in my blog you can see in sidebar by scrolling down. The Form will be created through Google Docs which is much more reliable than any.

Another very good feature is whenever any body gives his/her feedback through that form you will receive notification email from Google Docs that some has made feedback.

You can add feedback form in sidebar or as a separate page in blogger.
Rs. 163 .Coms at GoDaddy!
Steps are described below:

Sign in to Google Docs with your Gmail Id.

1.   On the home page click Create button and then click on the Form option as displayed in the picture below.




2.   The next tab page will be open to design a form like in picture below. Fill the form as you are viewing in the picture.

 3.   Then Create a multichoice item as described in the picture.

4.   Create the multichoice button list as show below.



5.   The click on done and Choose Embed option click to copy the code.

6.   A window will be open like below then select code and copy the code.
7.   Then in Layout of your blog add a gadget in sidebar and choose html script option and paste the code. Adjust the width and height in code according to your sidebar.

8.   Again go back to Google Docs web page and close the feedback form page and you will return to Google Docs home page. Then click the Feedback link as described in the picture below.

9.  Spreadsheet will open in next tab as you can see in the picture. Select tools menu and choose Notification rules option.

10.  The Notification Rules window will open add the rule as shown below.

11.  After that close the spreadsheet tab it will automatically save.

Now your Feedback Form is ready. Check your blog.

Hope you will like this. :)

Adding Feedback Form in Blogger

Reviewed by David Burn on

Mar 13

Rating:
4.5

Oracle Archive logs Transport Utility Software

Developed a software utility in Vb.Net to transfer archive logs from source location to target location. Transportation can be done via FTP also or by path.

The Software actually track the archive log location for creation of new archive log whenever new archive log being written it transfer the previous one which have been completed to the target location and there is option to manage archive logs also by that option you can transfer manually and delete the archive logs and many other task.

For order or Inquiry Click Here




Creating DDL Trigger in Oracle to exclude tables to be captured which are not supported by Oracle Streams. Create this trigger in Oracle Stream Admin user and modify the owner with your schema name.

This trigger fire whenever a table created in database and if table is containing unsupported data types then it will automatically added to the table capture rules to prevent errors.


Create Or Replace TRIGGER Ddltrigger
   After Create On Database
DECLARE
   N   Number;
BEGIN
   IF Ora_dict_obj_type = 'TABLE' And Ora_dict_obj_owner = 'SCOTT'
   Then
      BEGIN
         Select 1
           Into N
           From DBA_STREAMS_UNSUPPORTED
          Where Owner = 'SCOTT' And Table_name = Ora_dict_obj_name;

         IF N = 1
         Then
            Dbms_streams_adm.Add_table_rules
                                          (Table_name           => 'SCOTT.'
                                                                   || ORA_DICT_OBJ_NAME,
                                           Streams_type         => 'capture',
                                           Streams_name         => 'STREAMS_CAPTURE',
                                           Queue_name           => 'STRMADMIN.STREAMS_CAPTURE_Q',
                                           Include_dml          => TRUE,
                                           Include_ddl          => FALSE,
                                           Inclusion_rule       => FALSE,
                                           Source_database      => 'STREAM1'
                                          );
         END IF;
      EXCEPTION
         When Others
         Then
            Null;
      END;
   END IF;
END;
/
Oracle Streams examples

Oracle Database Replication Software to replicate Oracle database to another server for real time backup and other purposes like reporting, web etc.

The software is based on Oracle Streams, a built-in feature of the Oracle database, is a data replication and integration feature. It provides a flexible infrastructure that meets a wide variety of information sharing needs. Oracle Streams enables the propagation of data, transactions and events in a data stream either within a database, or from one database to another.

The software has Auto E-Mail option to report client at any time any service stopped.

It collects data information for Source Database and for Target Database and email configuration and on that behalf it generates scripts and executes through installation wizard.

The price of this software is $50

for inquiry Click Here









I am also providing free scripts for that which you can use for replication with little modification. Check this link http://www.foxinfotech.in/2014/02/setup-oracle-streams-replication-backup.html

Oracle Database Real Time Replication

Reviewed by Joseph on

Feb 3

Rating:
5

NAME_IN built-in in Oracle D2k

Description
Returns the value of the indicated variable.
The returned value is in the form of a character string. However, you can use NAME_IN to return
numbers and dates as character strings and then convert those strings to the appropriate data types. You
can use the returned value as you would use any value within an executable statement.
If you nest the NAME_IN function, Form Builder evaluates the individual NAME_IN functions from the
innermost one to the outermost one.
Syntax
FUNCTION NAME_IN
(variable_name VARCHAR2);
Built-in Type unrestricted function
Returns VARCHAR2
Enter Query Mode yes
Parameters
variable_name Specifies a valid variable or text item. The data type of the name is
VARCHAR2.
Usage Notes
If the returned value is a date string, NAME_IN will use the format mask specified in the
BUILTIN_DATE_FORMAT property. If the DATE_FORMAT_COMPATIBILITY_MODE property
is set to 4.5 the default American format is used to format the returned string.
NAME_IN examples
/*
** Built-in: NAME_IN
** Example: Simple implementation of a Last-In-First-Out
** stack mechanism using Global variables.
** For each named stack, a global variable
** GLOBAL.<stackname>_PTR points to the largest
** element on the stack. PUSH increments this
** value as new elements are added. Values
** PUSH’ed on or POP’ed off the named stack are
** actually stored in GLOBAL variables of a
** conveniently formed name: GLOBAL.<stackname>nnn
** where ’nnn’ is the number of the element on the
** stack.
**
** Usage:
** Push(’MYSTACKNAME’, ’1’);
** Push(’MYSTACKNAME’, ’2’);
**
** str_var := Pop(’MYSTACKNAME’); -- Gets ’2’
** str_var := Pop(’MYSTACKNAME’); -- Gets ’1’
** str_var := Pop(’MYSTACKNAME’); -- Gets ’EOS’
292
**
*/
PROCEDURE Push ( the_stackname VARCHAR2,
the_value VARCHAR2 ) IS
ptr_name VARCHAR2(40); -- This stack’s pointer name
prefix VARCHAR2(40); -- Common prefix for storage vars
elt_name VARCHAR2(40); -- Name of storage element
new_idx VARCHAR2(4) ; -- New stack pointer value
BEGIN
/*
** For any named stack that we reference, the global
** variables used for storing the stack’s values and the
** stack’s pointer all begin with a common prefix:
** GLOBAL.<stackname>
*/
prefix := ’GLOBAL.’ || the_stackname;
/*
** This named stack’s pointer resides in
** GLOBAL.<stackname>_PTR Remember that this is the *name*
** of the pointer.
*/
ptr_name := prefix || ’_PTR’;
/*
** Initialize the stack pointer with a default value of
** zero if the stack pointer did not exist previously, ie
** the GLOBAL.<stackname>_PTR had yet to be created.
*/
Default_Value( ’0’, ptr_name );
/*
** Since we’re PUSH’ing a new element on the stack,
** increment the stack pointer to reflect this new
** element’s position. Remember that GLOBAL variables are
** always of type VARCHAR2, so we must convert them TO_NUMBER
** before any calculations.
*/
new_idx := TO_CHAR( TO_NUMBER( Name_In( ptr_name ) ) + 1 ) ;
Copy( new_idx , ptr_name );
/*
** Determine the name of the global variable which will
** store the value passed in, GLOBAL.<stackname><new_idx>.
** This is simply the prefix concatenated to the new index
** number we just calculated above.
*/
elt_name := prefix||new_idx;
Copy( the_value , elt_name );
END;
FUNCTION Pop ( the_stackname VARCHAR2)
RETURN VARCHAR2 IS
ptr_name VARCHAR2(40); -- This stack’s pointer name
prefix VARCHAR2(40); -- Common prefix for storage vars
elt_name VARCHAR2(40); -- Name of storage element
new_idx VARCHAR2(4) ; -- New stack pointer value
cur_idx VARCHAR2(4) ; -- Current stack pointer value
the_val VARCHAR2(255);
EMPTY_STACK CONSTANT VARCHAR2(3) := ’EOS’;
NO_SUCH_STACK CONSTANT VARCHAR2(3) := ’NSS’;
BEGIN
/*
** For any named stack that we reference, the global
** variables used for storing the stack’s values and the
** stack’s pointer all begin with a common prefix:
** GLOBAL.<stackname>

*/
prefix := ’GLOBAL.’ || the_stackname;
/*
** This named stack’s pointer resides in
** GLOBAL.<stackname>_PTR Remember that this is the *name*
** of the pointer.
*/
ptr_name := prefix || ’_PTR’;
/*
** Force a default value of NULL so we can test if the
** pointer exists (as a global variable). If it does not
** exist, we can test in a moment for the NULL, and avoid
** the typical error due to referencing non-existent
** global variables.
*/
Default_Value( NULL, ptr_name );
/*
** If the *value* contained in the pointer is NULL, then
** the pointer must not have existed prior to the
** Default_Value statement above. Return the constant
** NO_SUCH_STACK in this case and erase the global
** variable that the Default_Value implicitly created.
*/
IF Name_In( ptr_name ) IS NULL THEN
the_val := NO_SUCH_STACK;
Erase( ptr_name );
/*
** Otherwise, the named stack already exists. Get the
** index of the largest stack element from this stack’s
** pointer.
*/
ELSE
cur_idx := Name_In( ptr_name ) ;
/*
** If the index is zero, then the named stack is already
** empty, so return the constant EMPTY_STACK, and leave
** the stack’s pointer around for later use, ie don’t
** ERASE it.
**
** Note that a stack can only be empty if some values
** have been PUSH’ed and then all values subsequently
** POP’ed. If no values were ever PUSH’ed on this named
** stack, then no associated stack pointer would have
** been created, and we would flag that error with the
** NO_SUCH_STACK case above.
*/
IF cur_idx = ’0’ THEN
the_val := EMPTY_STACK;
/*
** If the index is non-zero, then:
** (1) Determine the name of the global variable in
** which the value to be POP’ed is stored,
** GLOBAL.<stackname><cur_idx>
** (2) Get the value of the (cur_idx)-th element to
** return
** (3) Decrement the stack pointer
** (4) Erase the global variable which was used for
** value storage
*/
ELSE
elt_name:= prefix || cur_idx;
the_val := Name_In( elt_name );
new_idx := TO_CHAR( TO_NUMBER( Name_In(ptr_name) ) - 1 ) ;
Copy( new_idx , ptr_name );
Erase( elt_name );
END IF;
END IF;
RETURN the_val;
END;

Send email with attachments in Oracle D2k, Oracle Forms 10g, Oracle Forms 6i.

Create a directory object Email_Files in current schema from where you want to send emails.

Create or Replace directory email_files as 'c:\urdir\';

PROCEDURE Send_Email(ifrom In varchar2, ito In varchar2, icc In varchar2, ifile In varchar2, ifile2 In varchar2,
isubject In varchar2, imsg In varchar2, isrl_no In number)
 Is
 
    /*utl_smtp related variables. */
    vfile varchar2(100);
    v_connection_handle  Utl_Smtp.CONNECTION;
    v_from_email_address VARCHAR2(1000) := ifrom;
    v_to_email_address   VARCHAR2(1000) := ito;
    v_smtp_host          VARCHAR2(100) := 'mail.urdomain.com'; --your mail server
    v_subject            VARCHAR2(1000) := isubject;
    l_message            VARCHAR2(2000) := imsg;
  nls_charset    varchar2(255); 
    /* this send_header procedure is written in the documentation */
    PROCEDURE send_header(pi_name In VARCHAR2, pi_header In VARCHAR2) As
    BEGIN
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          pi_name || ': ' || pi_header || Utl_Tcp.CRLF);
    END;

 BEGIN
       Select Value
       Into   nls_charset
       From   NLS_DATABASE_PARAMETERS
       Where  parameter = 'NLS_CHARACTERSET';
 
    /*utl_smtp related coding. */
    v_connection_handle := Utl_Smtp.OPEN_CONNECTION(v_smtp_host, 25);
    Utl_Smtp.EHLO(v_connection_handle, v_smtp_host);
    utl_smtp.command(v_connection_handle, 'auth login');
  utl_smtp.command(v_connection_handle,utl_encode.text_encode('yourid@urdomain.com', nls_charset, 1));
  utl_smtp.command(v_connection_handle, utl_encode.text_encode('urpassword', nls_charset, 1));
    Utl_Smtp.Mail(v_connection_handle, v_from_email_address);
    Utl_Smtp.RCPT(v_connection_handle, v_to_email_address);
    IF icc Is Not Null Then
     Utl_Smtp.RCPT(v_connection_handle, icc);
    END IF;
    Utl_Smtp.OPEN_DATA(v_connection_handle);
    send_header('From', ifrom);
     send_header('To', ito);
    IF icc Is Not Null Then
      send_header('Cc', icc);
    END IF;
    send_header('Subject', v_subject);
    IF IFILE Is Null And IFILE2 Is Null Then
         Utl_Smtp.WRITE_DATA(v_connection_handle,
                           'Content-Type: text/html;' || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle,
                            ' charset=US-ASCII' || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle, l_message || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
    END IF;
   
    --mime header.
    IF ifile Is Not Null Then
     For i In 1..2 LOOP
      /*preparing the lob from file for attachment. */
          /*lob operation related varriables */
      IF i = 1 Then
        vfile := ifile;
      Else
       IF ifile2 Is Null Then
         EXIT;
       Else
        vfile := ifile2;
       END IF;
      END IF;
          DECLARE
         v_src_loc  BFILE := Bfilename('EMAIL_FILES', Nvl(vfile, 'temp'));
         l_buffer   RAW(54);
         l_amount   BINARY_INTEGER := 54;
         l_pos      INTEGER := 1;
         l_blob     BLOB := Empty_Blob;
         l_blob_len INTEGER;
         v_amount   INTEGER;
         BEGIN
      Dbms_Lob.OPEN(v_src_loc, Dbms_Lob.LOB_READONLY); --read the file
      Dbms_Lob.CREATETEMPORARY(l_blob, TRUE); --create temporary lob to store the file.
      v_amount := Dbms_Lob.GETLENGTH(v_src_loc); --amount to store.
      Dbms_Lob.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- loading from file into temporary lob
      l_blob_len := Dbms_Lob.getlength(l_blob);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          'MIME-Version: 1.0' || Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          'Content-Type: multipart/mixed; ' || Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          ' boundary= "' || 'EMAIL_FILES.SECBOUND' || '"' ||
                          Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
      IF i = 1 Then
        -- mail body
        Utl_Smtp.WRITE_DATA(v_connection_handle,
                            '--' || 'EMAIL_FILES.SECBOUND' || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle,
                            'Content-Type: text/html;' || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle,
                            ' charset=US-ASCII' || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle, l_message || Utl_Tcp.CRLF);
        Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
      END IF;
      -- mail attachment
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          '--' || 'EMAIL_FILES.SECBOUND' || Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          'Content-Type: application/octet-stream' ||
                          Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          'Content-Disposition: attachment; ' || Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                         ' filename="' || vfile || '"' || --my filename
                          Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          'Content-Transfer-Encoding: base64' || Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
    /* writing the blol in chunks */
      WHILE l_pos < l_blob_len LOOP
        Dbms_Lob.Read(l_blob, l_amount, l_pos, l_buffer);
        Utl_Smtp.write_raw_data(v_connection_handle,
                                Utl_Encode.BASE64_ENCODE(l_buffer));
        Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
        l_buffer := Null;
        l_pos    := l_pos + l_amount;
      END LOOP;
      Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
      Dbms_Lob.FREETEMPORARY(l_blob);
     Dbms_Lob.FILECLOSE(v_src_loc);
      EXCEPTION
       When Others Then
         Dbms_Lob.FREETEMPORARY(l_blob);
         Dbms_Lob.FILECLOSE(v_src_loc);
        END;
     END LOOP;
      -- close email
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          '--' || 'EMAIL_FILES.SECBOUND' || '--' || Utl_Tcp.CRLF);
      Utl_Smtp.WRITE_DATA(v_connection_handle,
                          Utl_Tcp.CRLF || '.' || Utl_Tcp.CRLF);
      END IF;
      Utl_Smtp.CLOSE_DATA(v_connection_handle);
      Utl_Smtp.QUIT(v_connection_handle);
  EXCEPTION
    When Others Then
--    utl_smtp.quit(v_connection_handle);
     IF ifile Is Not Null Then
      Utl_Smtp.QUIT(v_connection_handle);

     END IF;
      RAISE;
  END;

Pass the Xml filename parameter to this procedure to import that file. Before running this procedure create the table xmltbl with the fields as the tag name e.g. <Name>Abc</Name> for this Name tag field Name should be there in xmltbl.

CREATE OR REPLACE PROCEDURE Xml_Insert (infile In varchar2)
As
   Vinput     Varchar2 (1000);
   Nstart     Number             := 1;
   Nocr       Number             := 2;
   Nlen       Number;
   Vextract   Varchar2 (1000);
   Nprv       Number             := 0;

   TYPE Rec Is RECORD (
      Field   Varchar2 (100),
      Val     Varchar2 (1000)
   );

   TYPE Tarray Is Table Of Rec
      Index By Binary_integer;

   Ary        Tarray;
   Ncount     Number             := 1;
   Vfield     Varchar2 (100);
   Vval       Varchar2 (1000);
   Vinsert    Varchar2 (2000);
   Ifile      Utl_file.File_type;
BEGIN
   --Dbms_Output.enable (20000);
   Ifile := Utl_File.fopen ('c:\', infile, 'r', 1000);
         -- SKIP FIRST LINE
         Utl_File.get_Line (Ifile, Vinput);
      LOOP
      BEGIN
         Utl_File.get_Line (Ifile, Vinput);
         Nlen := Length (Rtrim(Vinput));
         Nstart := 1;
         Nocr := 2;
         Nprv := 0;
   ARY.Delete;
   NCOUNT := 1;
   IF NLEN > 0 Then
         LOOP
            Vextract := Substr (Vinput, Nstart, Instr (Vinput, '>', Nstart, Nocr) - Nprv);

            BEGIN
--               Dbms_Output.put_Line (Vextract);
               Ary (Ncount).Field := Substr (Vextract, 2, Instr (Vextract, '>') - 2);
               Ary (Ncount).Val :=
                  Substr (Vextract,
                          Instr (Vextract, '>') + 1,
                            Instr (Vextract, '<', Instr (Vextract, '>') + 1, 1)
                          - Instr (Vextract, '>')
                          - 1
                         );
               Ncount := Ncount + 1;
            EXCEPTION 
     When No_Data_Found Then
       Null;
               When Others
               Then
  RAISE;
            END;

            Nprv := Instr (Vinput, '>', Nstart, Nocr);
            Nstart := Instr (Vinput, '>', Nstart, Nocr) + 1;

            IF Nstart >= Nlen
            Then
               EXIT;
            END IF;
         END LOOP;

         BEGIN
            Vinsert := 'insert into xmltbl (';

            For I In 1 .. Ary.Count
            LOOP
               Vinsert := Vinsert || Ary (I).Field || ',';
            END LOOP;

            Vinsert := Rtrim (Vinsert, ',') || ') Values (';

            For I In 1 .. Ary.Count
            LOOP
               Vinsert := Vinsert || Chr (39) || Ary (I).Val || Chr (39) || ',';
            END LOOP;

            Vinsert := Rtrim (Vinsert, ',') || ')';
--            Dbms_Output.put_Line (Vinsert);

            EXECUTE Immediate (Vinsert);

            COMMIT;
         END;
   END IF;
      EXCEPTION
      When No_Data_Found Then
          IF Utl_File.is_Open (Ifile)
         Then
            Utl_File.fclose (Ifile);
         END IF;
   EXIT;
      When Others
      Then   

         IF Utl_File.is_Open (Ifile)
         Then
            Utl_File.fclose (Ifile);
         END IF;         
               --Dbms_Output.put_Line (Vinsert);
   RAISE;
   END;
      END LOOP;

--- process insert stmt thrg array -----
END;
/


V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.