Problem In Connecting Oracle9i SP with CR11.0

Hi!

I made an Stored Proc. in oracle 9i, in which i inserted data in a temporary table. The SP got compiled successfully. But when i tried to add that SP in the CR 11.0, through DSN, i got an error msg.

Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
ORA-06512: at "SCOTT.TEST_INSERTDATA1", line 6
ORA-06512: at line 1

If data is not inserted in stored proc, then no error comes while connecting Crystal report to stored proc of oracle.

This problem is specifically for those stored procs, where data is inserted in some table inside stored proc and that is used in CR.

Code of Oracle Stored Proc is as follows.
----------------

create or replace procedure Test_InsertData1(curmain in out CommonCursor_Pkg.abc,arg1 varchar2,arg2 varchar2)
as
lcsqry varchar2(500);
begin
lcsqry:='delete from reptable1';
execute immediate(lcsqry);

for i in 1..10 loop
lcsqry:='insert into reptable1(sfld1,nfld1) values(''aa'','||i||')';
execute immediate(lcsqry);
end loop;
commit;

lcsqry:='select sfld1,nfld1 from reptable1';
open curmain for (lcsqry);

end;
/
--------------------
-- Here CommonCursor_Pkg.abc denotes a ref cursor type named 'abc' declared inside CommonCursor_Pkg package. Code for this is :

create package CommonCursor_Pkg
as
type abc is ref cursor;

end;
/
-------------------

-- 'reptable1' is a global temporary table.

--CR 11.0 is connected to oracle in following manner.

Goto
Database Expert-> create new connection ->ODBC(RDO)
Then selecting the Oracle DSN from the list.

Or

Goto
Database Expert-> Create new Connection-> OLE DB(ADO)

please see how to fix this problem.

Thanks and regards

Vineet kumarThe procedure should have select statement at the end. As you are using using dynamic sql, scope is over if you run that proceudre. Try using global temporary tablesql

Problem getting select data set for XML Auto, Elements into a table variable -

I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.If I put it like

select @.l_variable = (select my sql statement for Auto, Elements)
it gives me syntax error.

I can't do a select into a temp table from my select statement for XML Auto.

I can't use select into as well.

I can't enclose my entire select statment in paranthesis to put it in a table variable.

How do I proceed?|||Here is the actual query. I am trying to get the result of the select statement into the table variable:

declare @.i_Customer_Id int
,@.i_Role_ID int
,@.i_Base_URL varchar(255)

declare @.l_XML_Table table (XML_String varchar (8000))

select @.i_Customer_Id = 10
,@.i_Role_ID = 1
,@.i_Base_URL = 'http://www.NewWebSite.com'

select
MM.Module_Description Topic_Type
,PM.Page_Description Title
,@.i_Base_URL + PM.Page_URL URL
from
Role_Page_Map RPM
,Module_Master MM
,Page_Master PM

where RPM.Role_ID = @.i_Role_ID
and RPM.Customer_Id = @.i_Customer_Id

and RPM.Page_ID = PM.Page_ID
and PM.Module_ID = MM.Module_ID

for XML Auto, Elements|||You can't select FOR XML into a table. See BOL for more information.|||That's true. However can't the resultant xml string be taken in a varchar variable as well?

problem getting result set through a stored procedure call using VB.

Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.See if this helps: http://www.sqlxml.org/faqs.aspx?faq=104
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"abc" <er.nehasinghal@.gmail.com> wrote in message
news:1121936278.733465.80930@.g47g2000cwa.googlegroups.com...
Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.sql