I want to insert a records in Oracle Database Tables. Anybody can help please?

[shantanu73]

Dear Guru's/Friends,

I had written the following script for inserting the record in Oracle database as follows:-

In ORACLE

-----------------

ODBC CONNECT32 TO [xescott;DBQ=XE ] (XUserId is LWEYbZNMPDdIHDD, XPassword is DdPSQZNMPDdIHMA);

//sql EXECUTE pTEST2(789, 'Bharat');

SQL insert into emp2(empno, ename) values(789, 'Bharat');          //IThis Statement get failed

emptab:

SQL select * from Emp;

For information.

1. Select statment is working fine

2. But Insert statement is now working it give following error:

SQL##f - SqlState: S1000, ErrorCode: 1456, ErrorMsg: [Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction SQL insert into emp2(empno, ename) values(789, 'Bharat')

I don't know how to make database READ & WRITE only. Because while creating DSN Oracle required Oracle database there is no option any where about ticking the READ & WRITE.

But In case of while SQLSERVER

While creating DSN SQLSERVER requried database there is option to tick READ & WRITE on ticking. We can insert record and select the record as well in SqlServer which is working fine.

I hope I had able to make scenerio clear

Can anybody can help/suggest me how to go about it?

Awaiting for response please.

Thanks in Advance

Shantanu

[amars]

Hi Shantanu,

Check in User Preferences if the Property Script(Allow Database Write and Execute Statements) is selected or not.

Thanks

Amar

[shantanu73]

Hello Amar,

First  of all thanks for quick response. As you have told to check whether "Allow Database Write and Execute Statement" is ticked or not.

FYI. It was not ticked. But after ticking also I had run the script it is showing same message as above.

Can guide me what I should do next ?.

Thanks in Advance

Shantanu

[amars]

Hi Shantanu,

I have executed the insert through SQL Server only and not on Oracle. Can you try executing the same insert statement through some Oracle Editor like Toad Etc. and see if it allowes you to insert and the issue is not due to some access right restrictions.

Thanks.

Amar

[jonathandienst]

Hi

At the bottom of the script editor, select the Settings tab and make sure that the option "Open Databases in Read and Write mode" is checked. Without this setting QV opens a read-only connection.

This assumes that you have the necessary permissions in Oracle.

Hope that helps

Jonathan

[shantanu73]

Hello Amar,

I had check before only of inserting the record from Oracle Sql Plus and Oracle Sql Developer as well. Which is working fine.

However, In SqlServer it is working fine. If you see my first trail mail. I had clearly mentioned it is fine with SqlServer. But not getting executed in case of ORACLE.

Even I had not ticked "Allow Database Write and Execute Statement" in case of SQLSERVER. Then also it work out fine.

Can u suggest me something else more to check or to do something else more.

Thanks in Advance

Shantanu

[shantanu73]

Hello Jonathan,

I had done necesary changed suggested by you. Then, also it is not working.

But FYI, Incase of SQLSERVER it work out fine without doing the above setting suggested by you. Only in case of ORACLE it get failed.

Can u suggest me something more pls.

Thanks in Advance

Shantanu