How to run a executable query in Oracle -Sql Developer

Asked by Muthumanikandan on 2017-07-27

I copied following value from excel,
(exec savereceiving ('17-MAY-2017','HSPG','T000000023',TO_DATE('2017-05-17 19:20:11', 'yyyy/mm/dd hh24:mi:ss'),'T000000003','N',NULL,'T000000023','TN28TR013','1',1,1,'000000000005103512',NULL,1000);

how run and commit this in Oracle-SQL developer without opening sql developer.

For ex: we will execute this statement in Windows batch file(.bat)

Question information

Language:
English Edit question
Status:
Answered
For:
Sikuli Edit question
Assignee:
No assignee Edit question
Last query:
2017-08-02
Last reply:
2017-08-02

This question was reopened

Muthumanikandan (nmuthu1) said : #2

i found the solution for above problem,

here is my code:

text_file = open("Output.sql", "w")
text_file.write("Insert into emp_details (EMP_ID,EMP_NAME,EMP_ADDRESS,DESIGNATION,QUALIFICATION,AGE) values (917,'Muthu','Covai','PA','BE',21);")
text_file.write("\ncommit;")
text_file.close()

batch_file=open("Script.bat","w")
batch_file.write("set NetPath=%~dp0")
batch_file.write("\n@echo off")
batch_file.write("\nsqlplus -L OPCPRD/OPCDGPC@DB10:1521/ORCL.CORP.SIERRATEC.COM @%NetPath%/Output.sql")
batch_file.write("\nexit")
batch_file.close()

openApp("Script.bat")
wait(1)
closeApp("Script.bat")

by this code my values successfully inserted into Db.

I need to know how to copy from excel clipboard via sikuli script, and paste the value in following code,

text_file.write("Insert into emp_details (EMP_ID,EMP_NAME,EMP_ADDRESS,DESIGNATION,QUALIFICATION,AGE) values (917,'Muthu','Covai','PA','BE',21);")

Muthumanikandan (nmuthu1) said : #3

Thanks masuo for your answer, Kindly provide solution for the above problem.

masuo (masuo-ohara) said : #4

It is able to get value of cell from excel directly to using xlrd feature.
Why Do you want to use clipboard?

Muthumanikandan (nmuthu1) said : #5

Yes Now i can get value from excel by using following code,
import xlrd
import datetime
EXCELFILE = "D:\\Muthu\\Datamanipulation\\book1.xlsx"
book = xlrd.open_workbook(EXCELFILE)
sheet = book.sheet_by_index(0)
celltype = sheet.cell_type(40,33)
value = sheet.cell_value(40,33)

but i need to pass this value to "text_file.write" so that my value will got insert into DB.

text_file.write("Insert into emp_details (EMP_ID,EMP_NAME,EMP_ADDRESS,DESIGNATION,QUALIFICATION,AGE) values (917,'Muthu','Covai','PA','BE',21);")

Help to pass the value to text_file.write , sometimes i need to insert multiple rows so provide code for multiples rows also

Manfred Hampl (m-hampl) said : #6

How do the contents of the excel file map to the elements of the line?

What does the excel cell contain? the EMP_ID or the EMP_NAME or the complete line "917,'Muthu','Covai','PA','BE',21", or...?

You probably have to use string concatenation.
Assuming the following:
row 41 column 31 contains the EMP_ID
row 41 column 32 contains the EMP_NAME
row 41 column 33 contains the EMP_ADDRESS
etc.
Then the following should work
text_file.write("Insert into emp_details (EMP_ID,EMP_NAME,EMP_ADDRESS,DESIGNATION,QUALIFICATION,AGE) values ("+str(sheet.cell_value(40,30))+",'"+sheet.cell_value(40,31)+"','"+sheet.cell_value(40,32)+"','"+sheet.cell_value(40,33)+"','"+sheet.cell_value(40,34)+"',"+str(sheet.cell_value(40,35))+");")
You have to build a long string with contents that are very similar to the line that you created manually, containing quote and comma characters in the same sequence.

(Remark: check "string concatenation" in the python manuals)

Muthumanikandan (nmuthu1) said : #7

I used concatenation , ex: row 33 column 31 values is = exec savereceiving ('28-JUL-2017','0000100991','T000025366',TO_DATE('2017-07-28 18:20:11', 'yyyy/mm/dd hh24:mi:ss'),'T000000025','N',NULL,'T000000025','TN99E0338','1',1,1,'83400AAHH20ZB',NULL,50);

now i need to pass the above value into text_file.write(exec savereceiving ('28-JUL-2017','0000100991','T000025366',TO_DATE('2017-07-28 18:20:11', 'yyyy/mm/dd hh24:mi:ss'),'T000000025','N',NULL,'T000000025','TN99E0338','1',1,1,'83400AAHH20ZB',NULL,50);

how can i pass the value, kindly give me the code

Manfred Hampl (m-hampl) said : #8

If the value in cell row 33 column 31 is "exec ... 50);"
then you just have to do

text_file.write(sheet.cell_value(32,30))

Muthumanikandan (nmuthu1) said : #9

Thanks much.. comment #8 Solved my problem

Muthumanikandan (nmuthu1) said : #10

how can i pass the multiple values
for ex: text_file.write(sheet.cell_value(32,30)) this script pass one value,
if i need to pass multiple values should i do like this

import xlrd
import datetime
EXCELFILE = "D:\\Muthu\\Datamanipulation\\book1.xlsx"
book = xlrd.open_workbook(EXCELFILE)
sheet = book.sheet_by_index(0)
celltype = sheet.cell_type(40,33),(41,43),(42,43)
value = sheet.cell_value((40,33),(41,43),(42,43))
wait(2)
text_file = open("Output.sql", "w")
text_file.write(sheet.cell_value((40,33),(41,43),(42,43))
text_file.write("\ncommit;")
text_file.close()

batch_file=open("Script.bat","w")
batch_file.write("set NetPath=%~dp0")
batch_file.write("\n@echo off")
batch_file.write("\nsqlplus -L GPCPRD/HEROGPC@DB10:1521/ORCL.CORP.SIERRATEC.COM @%NetPath%/Output.sql")
batch_file.write("\nexit")
batch_file.close()

Muthumanikandan (nmuthu1) said : #11

Kindly share your inputs for above question

Manfred Hampl (m-hampl) said : #12

You cannot give more than one cell address to sheet.cell_value() and similar functions, but you have to use some kind of loop statements.

something similar to

    ...
    sheet = book.sheet_by_index(0)

    text_file = open("Output.sql", "w")

    for rx in range(sh.nrows):
        text_file.write(sheet.cell_value(rx,33)
        text_file.write("\ncommit;")

    text_file.close()

This loops through all rows. If this is not correct, e.g. because the data start only in row 20, you have to formulate the looping statement different, probably something like

    for rx in range(20, sheet.nrows):

This is python coding. You should try improving your knowledge in python programming.

Muthumanikandan (nmuthu1) said : #13

 i am a tester i will improve by practice..

1) i tried this comment#12 code

sheet = book.sheet_by_index(0)

    text_file = open("Output.sql", "w")

    for rx in range(sh.nrows):
        text_file.write(sheet.cell_value(rx,33)
        text_file.write("\ncommit;")

    text_file.close()

it fetch only single row value , for ex: fetched only row 33 value..

2) with the above example i tried to fetch column value

text_file = open("Output.sql", "w")

    for rx in range(sh.ncols):
        text_file.write(sheet.cell_value(rx,33)
        text_file.write("\ncommit;")

    text_file.close()

i got this error:

[error] IndexError ( index out of range: 5 )
[error] --- Traceback --- error source first line: module ( function ) statement 413: sheet ( cell_type ) IndexError: index out of range: 5

Can anyone help me with example how to fetch multiple record,
for ex: row40,col33
row41,col33
row42,col33

Manfred Hampl (m-hampl) said : #14

As far as I know the parameters are
sheet.cell_value(row,column)

In which row(s) and column(s) are your data?

If it is
row 40 col 33
row 41 col 33
row 42 col 33
then you have to loop over the rows as proposed in comment #12.

I suggest that you do debugging with print statements. something like

    print "number of rows: " + str(sh.nrows)
    for rx in range(sh.nrows):
         print "in row: " + str(rx)
         print "contents: " + sheet.cell_value(rx,33)
         text_file.write(sheet.cell_value(rx,33))
         text_file.write("\ncommit;")

etc. to see how the script is processing through the rows.

Muthumanikandan (nmuthu1) said : #15

Thanks for the above Comment # 14,it helped i am getting close to my result, but one step ahead,

I tried below code:

import xlrd
import datetime
EXCELFILE = "D:\\Muthu\\Datamanipulation\\book2.xlsx"
book = xlrd.open_workbook(EXCELFILE)
sheet = book.sheet_by_index(0)
#print "value" + str(sheet.nrows)
for rx in range(sheet.nrows):
 print "contents: " + sheet.cell_value(rx,33)

Result:
Column 33 value of all rows is getting printed

instead of printing, i need to get the result in temporary variable and pass the variable value to text_file.write

for ex :
value = sheet.cell_value(40,33) ##value temp variable
text_file.write(sheet.cell_value(40,33)) ## passing variable value to text_file.write input

Kindly provide the code for the above scenario.

Manfred Hampl (m-hampl) said : #16

You just have to replace

for rx in range(sheet.nrows):
  print "contents: " + sheet.cell_value(rx,33)

by

for rx in range(sheet.nrows):
 text_file.write(sheet.cell_value(rx,33))

Muthumanikandan (nmuthu1) said : #17

Thanks Hampl..

I tried with your code , it ran successfully but value not writing .

Can you please confirm the variable length for sikuli jython (i.e) how many characters can we assign for variable

Manfred Hampl (m-hampl) said : #18

If text_file.write(...) already worked in comments #2 and #5, why should it stop working now?

Please debug your script by adding print statements for verifying the contents of the variables at execution time.

Muthumanikandan (nmuthu1) said : #19

I debug the code with print statement, my analysis are:

Script:

import xlrd
import datetime
EXCELFILE = "D:\\Muthu\\Datamanipulation\\book2.xlsx"
book = xlrd.open_workbook(EXCELFILE)
sheet = book.sheet_by_index(0)
for rx in range(sheet.nrows):
 value = sheet.cell_value(rx,33)
 print(value) ## till this code prints column 33 all values (col 33 having 2 executable queries)

text_file = open("Output.sql", "w")
text_file.write(value) ## Here i am passing the column 33 values=2 executable queries
text_file.write(Key.ENTER)
text_file.write("/")
text_file.write("\ncommit;")
text_file.close()

batch_file=open("Script.bat","w")
batch_file.write("set NetPath=%~dp0")
batch_file.write("\n@echo off")
batch_file.write("\nsqlplus -L GPCPRD/HEROGPC@DB10:1521/ORCL.CORP.SIERRATEC.COM @%NetPath%/Output.sql")
batch_file.write("\nexit")
batch_file.close()

openApp("Script.bat")
wait(2)
print (value) # issue is here , here script prints only one value column 33 last value

Can anyone help to solve this problem:

text_file.write(value) ## while passing multiple values to DB it accepts only last value.sometimes empty values passing but commit succesfull.

For screenshots refer below link:
https://screencast-o-matic.com/watch/cbjni8lB0B

Manfred Hampl (m-hampl) said : #20

You need to have the statements

 text_file.write(value) ## Here i am passing the column 33 values=2 executable queries
 text_file.write(Key.ENTER)
 text_file.write("/")
 text_file.write("\ncommit;")

inside the
"for rx in range(sheet.nrows):"
loop.

Please consult the Python manuals to learn how to correctly use indents for grouping statements.

Can you help with this problem?

Provide an answer of your own, or ask Muthumanikandan for more information if necessary.

To post a message you must log in.