Solved

sending data from oracle form to other application

Posted on 2014-03-26
14
578 Views
Last Modified: 2014-04-03
we have one application "oracle database"  , usually , when we have alot of data in details
we post the data in excell sheet , then we have macro to send the rows to the form of the application

i also designed one code in MS ACCESS , to do the same , and here is the code
Private Sub Command30_Click()
On Error GoTo Err_Com

Dim SE, Re As DAO.Recordset
Dim K As Integer
Dim Ga As String

AppActivate "General Ledger"
'DoCmd.SetWarnings False

'Ga = "SELECT AccrudeEntry.* INTO ForApha FROM AccrudeEntry;"
'DoCmd.RunSQL Ga

DoCmd.SetWarnings True

Set SE = CurrentDb.OpenRecordset("UploadAcrAlpha")

If SE.BOF = True Then
MsgBox "There Is No Operational Accounts For Bills This Month"
Exit Sub
End If

SE.MoveFirst
Do Until SE.EOF

DoEvents
SendKeys Chr(9)
SendKeys SE![AccountNo]
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys SE![AlphaCode]
SendKeys Chr(9)
SendKeys Round(SE![Amount], 3)
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys "Accrude Amount"
SendKeys Chr(9)
SendKeys "{ENTER}", True
DoEvents
SE.MoveNext
Loop
SE.Close

Exit Sub
Err_Com:
    
    MsgBox Err.Description
    
End Sub

Open in new window


the code above sending data from recordset in required field , and sending key SHIFT , or TAB when required

now i want to do the same from one oracle form , i want to send data to the application
in oracle we have cursor instead of recordset in MS ACESS
same scenario , it should loop  a cursor , but i don't know  2 things :
1- how to get the open application name ?  in ms access it is
AppActivate "General Ledger"
2 - how to use sending key function , like this in ms access

DoEvents
SendKeys Chr(9)
SendKeys SE![AccountNo]
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys SE![AlphaCode]
SendKeys Chr(9)
SendKeys Round(SE![Amount], 3)
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys "Accrude Amount"
SendKeys Chr(9)
SendKeys "{ENTER}", True
DoEvents
0
Comment
Question by:NiceMan331
  • 8
  • 6
14 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
Comment Utility
Since your are running local.
You could try building a a script using text_io and execute it with the forms host command


step1 test the execution of a script by hostcommand
create a testscript in your editor "C:\Users\username\Documents\expert_exchange\test.vbs"
change username to your own username (multiple times)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.OpenTextFile("C:\Users\username\Documents\expert_exchange\" &"\test.txt", 2, True)
File.Write "testing"
File.Close
and execute (for example in a when button  pressed) using the host-build in
host('cscript C:\Users\username\Documents\expert_exchange\test.vbs');

If you succeeded in creating the file test,txt with content testing step 1 is done.

step 2 test if you can reach "General Ledger" by editing a GL.vbs file:
something like
AppActivate "General Ledger"
DoEvents
SendKeys Chr(9)
SendKeys 123
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys aaa
SendKeys Chr(9)
SendKeys 4000
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys "Accrude Amount"
SendKeys Chr(9)
SendKeys "{ENTER}", True
DoEvents

If it does what you want

step3
in your triggger use build text_io build-ins to create  a vbs-scripts with a uniq-name (for example using session_id and timestamp)  based on the results of the query : hardcoding the sendkey values  and execute the script

step4
call your hostcommand with the no_screen option
and execute another one to erase the vbs-script

I did never use it this way but this would be my approach. (i did use text_io and the host-command)
0
 

Author Comment

by:NiceMan331
Comment Utility
thanx for your post , but i'm sorry , as a begginer in oracle , let me do it step by step

create a testscript in your editor "C:\Users\username\Documents\expert_exchange\test.vbs"

i searched a lot on google to find how to create that script , i found a large amount of information about it , it is mixed now for my small knowledge
how to create that script ?
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
open notepad, copy the text

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.OpenTextFile("C:\Users\username\Documents\expert_exchange\" &"\test.txt", 2, True)
File.Write "testing"
File.Close

change the path in FSO.OpenTextFile to an existing directory on your system
save as test,vbs i a directory you want

open that directory in windows explorer ,  double-click test.vbs
and check wether test.txt is written with 1 line 'testing'
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , done
testing was written already to test.txt
now i have to create gl.vbs  and put the same codes i wrote in ms access ?
0
 

Author Comment

by:NiceMan331
Comment Utility
i created GL.VBS , i put those code inside

AppActivate "General Ledger"
DoEvents
SendKeys 15121

but nothing happened
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
Sorry, VB-scripting is also unknown terrain for me.
Ik was mixing vba and vbs-scripting

You wil need modifications to translate vba to vbs
Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("General Ledger")
' Give General Ledger time to activate
WScript.Sleep 500
objShell.SendKeys 15121


You did have  your "General Ledger"-application already open ?

Since I don't have your general ledger a tested simular action  with notepad

I used (not starting it from forms though)

notepad.vbs  to start notepad
and notepadactivate.vbs
pay attention to the objShell.AppActivate("Kladblok") : 'Kladblok' is the dutch name of notepad : you will probably need the 'Notepad'  name. You can use windows taskmanager or the title of your ledger-app to find the name you need

notepad.vbs

Set objShell = WScript.CreateObject("WScript.Shell")
objShell.Run ("%windir%\notepad")
' Give Notepad time to load
WScript.Sleep 500
objShell.SendKeys "Hello World!"

notepadactivate.vbs
Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("Kladblok")
' Give Notepad time to activate
WScript.Sleep 500
objShell.SendKeys "Activated"
0
 

Author Comment

by:NiceMan331
Comment Utility
step 2 done
the gl.vbs script successfully reached my app , and filled by the sanded keys values
now could u please explain your step 3
step3
in your triggger use build text_io build-ins to create  a vbs-scripts with a uniq-name (for example using session_id and timestamp)  based on the results of the query : hardcoding the sendkey values  and execute the script
for how do i fill the values  from a table ( using cursor for example ) to be written in the notepad (followed send keys )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:flow01
Comment Utility
should be something like

DECLARE
OUTFILE TEXT_IO.FILE_TYPE;
filename  varchar2(100);

BEGIN

SELECT 'c:yourpath\' || 'GL' || sys_context('USERENV', 'SESSIONID') || '_' || to_char(sysdate,'yyyymmddhh24miss') || .vbs' into filename FROM DUAL;

OUTFILE := TEXT_IO.FOPEN(filename,'W');

TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || 'Set objShell = CreateObject("WScript.Shell")');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || 'objShell.AppActivate("General Ledger")');

TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || ''' Give Notepad time to load');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || 'WScript.Sleep 500');

FOR SE IN (select * from UploadAcrAlpha) LOOP

TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || SE.AccountNo || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || SE.AlphaCode || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || Round(SE.Amount, 3) || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"Accrude Amount"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{ENTER}"';  -- i don't know what the , True means in VBA

END LOOP;

TEXT_IO.FCLOSE(OUTFILE);
END;
0
 

Author Comment

by:NiceMan331
Comment Utility
after some adjustment to the top of the code

TEXT_IO.PUT_LINE (OUTFILE,'Set objShell = CreateObject("WScript.Shell")');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.AppActivate("General Ledger")');

TEXT_IO.PUT_LINE (OUTFILE, ' Give General Ledger time to activate');
TEXT_IO.PUT_LINE (OUTFILE,'WScript.Sleep 500');

Open in new window


TEXT_IO.PUT_LINE (OUTFILE, ' Give General Ledger time to activate');
not yet adjusted , because its output should be
 ' Give General Ledger time to activate
i think it need additional '
anyhow , the output of the code is

Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("General Ledger")
' Give General Ledger time to activate
WScript.Sleep 500
objShell.SendKeys "{TAB}"
objShell.SendKeys 43513
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"
objShell.SendKeys 3010
objShell.SendKeys "{TAB}"
objShell.SendKeys 4000
objShell.SendKeys "{TAB}"
objShell.SendKeys Accrude Amount
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"

i try it many times , it not send any thing to the application
while , i manually adjust the old gl.vbs , rename it to be gl1.vbs , its out put like this

Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("General Ledger")
' Give General Ledger time to activate
WScript.Sleep 500
objShell.SendKeys "{TAB}"
objShell.SendKeys 43513
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"
objShell.SendKeys 3010
objShell.SendKeys "{TAB}"
objShell.SendKeys 4000
objShell.SendKeys "{TAB}"
objShell.SendKeys "Accrude"
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"

this works perfectly , i spent alot of times to check what is the difference between 2 files
i didn;t see any difference
in conlicdion , gl.vbs not works
gl1.vbs works
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
The  difference is in  ''  surrounding the accrude text .
But I would have expected that
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"Accrude Amount"');
would have made that line
single quote,  double quote ,Accrude Amount, double quote , single_quote
0
 

Author Comment

by:NiceMan331
Comment Utility
great
it is wonderful now , i used also host in the command , it works also fine
finally , i have 3 points

1-is this line necessary :
TEXT_IO.PUT_LINE (OUTFILE, 'Give General Ledger time to activate');

i removed it , and works

2- look at this
FOR SE IN ( SELECT * FROM GEN_LEDGERS WHERE DOC_DATE = '20-03-2014' and tran_no = 1521) LOOP
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.acc_no || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || nvl(SE.cost_center,'"{TAB}"') || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_debit || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_credit || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || SE.DESCRIBT || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
END LOOP;

here as a program built , each record should have one value only , eaighter debit or credit
and the typing will be like this
of user type field debit , the cursor automatically skip the next field credit to the next
describt , like this
debit , TAB , descript
shall we use decode in the statement , or i will use the loop 2 times
1 time for debit records , then for credit reports ,
3- could we use same scanrio to write data to excell sheet ?
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
1.  Yes you can leave it out : in VBS-script the ' starts a comment line

2. You did not mention the wanted behaviour if it;s a credit field:
?
TAB, credit, descript
?

And i don't know if the debit field contains 0 or null if  there is a credit field
decide for yourself and adjust if necessary
IF SE.l_debit IS NOT NULL THEN
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_debit || '');
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
ELSE
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_credit || '');
END IF;

so don't loop twice

 
3.  Yes , you can. But:
     Try opening an excel-sheet in notepad or wordpad:  
     that's the way your text-file should look alike to create a "real" excel-sheet.
     If you look carefully you can see the structure and make a coding effort to create it.
     
     More easy, but less friendly is to create a comma seperated file.
     You can open it in excel, but in my experience you have to inform excel in what way the data are stored.

You can try it your self
create a file   toexcel.txt
"A",1,"B",,4
"D",3,"",5,4

start excel , open toexcel.txt  and answer the questions in the wizard
However, depending on the way you want to use it you will meet more to solve: what is your date format,  what your decimal sign, what language do you use (and even more : are that setting on the system and excell the same for all the user of your file),
0
 

Author Comment

by:NiceMan331
Comment Utility
sorry for delay , but i lost the internet connection in my office
i will do same code of above to write in the txt file
0
 

Author Comment

by:NiceMan331
Comment Utility
yes , i tried it , it is ok , it sent to text file , then i opened it in excell
thanx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now