Skip to main content

call an oracle SQL script from EXCEL

 call an oracle SQL script from EXCEL

For example, I'll execute SELECT query.

Step 1: Learn name OLEDB provider of data for Oracle. If you established Oracle Client, this provider will be present. To learn a name of the provider, for example, create an empty file with extension *.udl (in Windows, certainly ). Then open its properties. On a page "Provider" you can see the list of providers.
Step 2: Open VBA in Excel (Tools - Macro - Visual Basic Editors) and write this Script (Insert - New Module):

 Sub Macro1()  
 ' Macro1 Macro  
  Dim MyConn As ADODB.Connection  
  Dim MyRst As ADODB.Recordset  
  Dim MyPr As String  
  Dim Ct As Long  
  Set MyConn = New ADODB.Connection  
  MyPr = "Provider=your_OLEDB_provider_name;Password=your_password;Persist Security Info=True;User ID=your_user;Data Source=your_Oracle_server_name"  
  MyConn.Open MyPr  
  Set MyRst = New ADODB.Recordset  
  MyRst.Open "Select * from table_name", MyConn, adOpenStatic, adLockReadOnly  
  Ct = 1  
  Do Until MyRst.EOF  
   For i = 0 To MyRst.Fields.Count - 1  
   Worksheets("Sheet1").Cells(Ct, i + 1).Formula = MyRst(i)  
   Next i  
   MyRst.MoveNext  
   Ct = Ct + 1  
  Loop  
  ActiveWorkbook.Save  
  MyRst.Close  
  Set MyRst = Nothing  
  Set MyConn = Nothing  
 End Sub  
With Best Regards. Sam.
P.S. Also you should establish necessarily the reference to objects ADO from Excel: Tools - References... For example, on my computer:
1. Microsoft ActiveX Data Objects 2.7 Libriary
2. Microsoft ActiveX Data Objects Recordset 2.8 Libriary

You can add next text in VBA-script:
   Set fso = CreateObject("Scripting.FileSystemObject")  
   Const ForReading = 1  
   Set f = fso.OpenTextFile("path to your SQL-Script", ForReading)  
   SQLText = f.ReadAll  
   f.Close  
   Set MyRst = New ADODB.Recordset  
   MyRst.Open SQLText, MyConn, adOpenStatic, adLockReadOnly  

Popular posts from this blog

Song Aankhen Khuli Ho lyrics notation

Song : Aankhen Khuli Ho Movie: Mohabbatein Notes used : W=>Western - C D E F G- A- B-/ H=>Hindustani - S R G M P- D- N- ( Here for western, G=G-, A=A-, & B=B- ) ( For hindustani, P=P-, D=D-, & N=N- ) Song I : Aankhen Khuli...Ho Ya.. Ho Bandh W=> A.... C... B..C.. E.. E...... A... A.... H=> D... S... N..S.. G G....... D... D.... Deedaar Un Ka Ho.o.taa Hai.. W=> A...B....A....D.BAG....ADB... H=> D...N...D.....R.NDP...DRN... Kaise Kahoon Main O..Yaaraa W=> B..D.. D....E.... D.....C..C..C... H=> N..R.. R....G... R.....S..S..S..... Ye Pyaar Kaise Hota Hai W=> E...B.....DB...AG...B..AA H=> G...N....RN...DP...N...DD (Tururu ru ru, ru ru rururu ru......) W=> AA...GA...BCE..., B...DB..GA H=> DD...PD...NSG..., N..RN.. PD Song II: Aa.aj He Kisi..par Yaa.ro.on..., Marke De..Khe..gein Hum W=> E....FEDCBABC.D.. D D......., G A B C.... E.......D...D..... H=> G....MGRSNDNS.R. R R......., P D N S.....G........R...R.... Pyaar Ho...

All songs notation and chords at one place

Song : O Saathi Re Film : Mukhathar Ka Sikkandhar Uses : C D D# E G A Note : The numbers at the end of the lines indicate line numbers. Pallavi: O saathi re, tere binaa bhi kya jina, tere binaa bhi kya jina A- C D D#....,D D C DD E...C..CA-...,D D C DD E...CC.......1 Play line 1 again phulon men khaliyon men sapnom ki galiyon men GGG...GAGE.. GGG G A G E.................................................2 tere bina kuchh kahin naa E A G E D C D D#.......................................................................3 tere binaa bhi kya jina, tere binaa bhi kya jina D D C DD E....C..CA-..., D D C DDE....CC.............................4 Charanam: har dhadkan men, pyaas hai teri, sanson men teri khushboo hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................5 is dharthi se, us ambar tak, meri nazar men tu hi tu hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................6 pyaar yeh tute naa GGG... GAG D#......E............................

PHP Tips-Getting the nodes list of xml doument with responseXml in ajax ,call image save in database,time difference etc

Getting the nodes list of xml doument with responseXml in ajax var obj = ""; function callAjaxObj() { try { obj = new XMLHttpRequest(); } catch(e) { try { obj = new ActiveXObject("Msxml2.XMLHTTP"); } catch(e) { try { obj = ActiveXObject("Microsoft.XMLHTTP"); } catch(e) { alert("your browser doesn't support ajax"); return false; } } } } function testResponseXml() { callAjaxObj(); obj.open("get","sample.xml",true); obj.onreadystatechange=function() { if(obj.readyState==4) { var doc = obj.responseXML.documentElement; //var doc = obj.responseXML; alert(doc.getElementsByTagName('user').length); } } obj.send(null); } Example of calender script in PHP calender script in PHP echo " $title $year "; echo "SMTWTFS"; $day_count = 1; echo ""; while ( ...