Victor,
running only the insert statement from sqlplus is not the whole story.
If you create an entry through ARS there are a bunch of statements which
gets executed. Immediately before the insert are 2 statements to
generate the request_id. You should see something like this in your
sql-logfile.
update arschema set nextid = nextid + 1 where schemaid = xxxx;
select nextid from arschema where schemaid = xxxx;
insert into Txxxx (cxxx,cyyy,czzz) values ('x','y','z');
Try to run these 3 in sqlplus. IMHO error ORA-02047 indicates some
problem with handling distributed transactions. If this sequence of
statements generates an error in sqlplus try a commit; after the first 2
statements. If this solves the problem you can set "Next-ID-Commit: T"
in ar.conf. This causes ARS to issue a commit after generating the
nextid.
HTH
Kind Regards Conny
________________________________
Von: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] Im Auftrag von Victor Olufowobi
Gesendet: Sonntag, 13. Dezember 2009 09:41
An: arslist@ARSLIST.ORG
Betreff: Re: View form from Oracle based AR system to remote MS SQL
server
**
Thanks again Joe,
I will try what you suggested and have the outcome posted - but I want
you to consider the following:
- I can update without problem using the view form created. It's when
I'm inserting (CREATE operation) the error occurs
- I can successfull run the INSERT command ARS is trying to run from
sqlplus
Victor
On Sat 12/12/09 14:56 , Joe D'Souza jdso...@shyle.net sent:
Victor,
Well that's half the battle won then.. Yes MS-SQL can be weird
when it comes
to case sensitivity of view names and columns within views
even.. For e.g.
if your ARS was hosted on a MS-SQL server, and the view name was
in small
case and you tried creating a view form in upper case, it would
not
recognize that name. There is a way to override that case
sensitivity but we
won't deal with that since you already updated the view to have
it upper
cased..
This is what I found for ORA-02047.
http://ora-02047.ora-code.com/ <http://ora-02047.ora-code.com/>
Based on the clue provided with the explanation of the error
code, can you
the Run Process Application-Release-Pending just an action
before updating
the MS-SQL view data in your workflow - and if that does not
work the same
action after the update action to that DB-Link? I'm assuming
that will force
any pending commits before (or if necessary after) you try
updating the
external view using that DB-Link in light of what the article in
the above
link says..
If that does not work (and the commit is required while and not
before or
after the update), try writing a stored procedure that has a
commit inside
of it - e.g..
BEGIN
update tablen...@..... set ... where ...;
commit;
END;
Use that stored procedure in a direct SQL at the point where you
want to run
that update to the foreign database. Make sure that you declare
all the
variables that you need while defining the stored procedure.
Let me know how it goes..
Cheers
Joe
-----Original Message-----
From: Action Request System discussion list(ARSList)
[arsl...@arslist.org]on Behalf Of Victor
Sent: Saturday, December 12, 2009 3:48 AM
To: arslist@ARSLIST.ORG
Subject: Re: View form from Oracle based AR system to remote MS
SQL
server
On Thursday 10 December 2009 21:16:14 Joe D'Souza wrote:
> Hello Victor,
>
> That is what I suspected with the invalid identifier error..
if your table
> was not recognized it would be invalid table or view name
error which is
> not your case.. Which is why the later part of my previous
email holds
> good.. Take a SQL log, you may find that offending column name
or
columns..
>
> There is something in the way that ARS is naming the offending
column or
> columns causing that error..
>
> When you find what column it is email the list or me directly
and we'll
try
> to figure it out.. you might need to create a independent view
in MS-SQL
of
> that table having names of fields that are legal to use in
case there is a
> column name there that the ARS converts into something else..
This is just
> a speculative solution, we'll know more when you know more..
>
> Cheers
>
> Joe
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [arsl...@arslist.org]on Behalf Of Victor
> Sent: Thursday, December 10, 2009 3:00 PM
> To: arslist@ARSLIST.ORG
> Subject: Re: View form from Oracle based AR system to remote
MS SQL
> server
>
>
> Thanks Joe,
>
> I will try all you said and have you posted tomorrow(I'm out
of office at
> the moment) but I want to point out that will creating the
view form I was
> able to load the table and the pick the fields required for
the form.
> Only when I pressed "Create" the error occurred.
>
> .. and furthermore the ms sql username and password was
hard-cored into
the
> public DB link created
>
> Victor
>
>
________________________________________________________________________
___
>____ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
<http://klub.burdamedia.pl/parse.php?redirect=http://www.arslist.org>
> Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the
Answers Are"
Joe,
Thank you for pointing me to the right direction!.
I set on SQL log as suggested and found out that while SELECTing
fields from
MSSQL server to create a view in Oracle for the view form, ARS
was unable to
parse fields created in Oracle db with fields from MSSQL server.
This is
because the field names in MSSQL were in lowercases while in
Oracle they
were all in uppercases! (can this behaviour be changed?)
I redo the fields in SQL server to uppercases and I was able to
complete the
creation of the view form!.
Thanks a lot for that.
However, when I tried to submit to the form I received this
error:
"ARERR [552] Failure during SQL operation to the database:
ORA-02047: cannot
join the distributed transaction in progress"
Once again I set on the SQL log.
ARS was trying to INSERT the required values to the view created
for the
view form when the error occurred.
I ran the command ARS was trying to run in sqlplus and it was
executed
successfully.
Do you have an Idea what might be the cause of the error in ARS?
Thanks very much for you suggestions so far.
Victor
________________________________________________________________________
_______
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the
Answers Are"
_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers
Are"_
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
