TD-3949: Calls to procedures does not work
TD-3949: Calls to procedures does not work
Hi,
we have got trial version of TD and recompiled with very small changes an old project (CTD1.5.1). However, by starting application we run immediately into Oracle errors. It seems that calls to Oracle procedures do not work as before. In CTD1.5.1 to call procedure in the Oracle package it was enough to do the following:
Set lsSql = "DBMS_APPLICATION_INFO.SET_MODULE(psModuleName,psAction)"
If SqlPLSQLCommand(phSql,lsSql)
...
Now:
1. Without SYS specificator the procedure will not be found.
2. Even with SYS specificator, we get ORA-24328 or ORA-24323.
In the log file in new version from all bind variables only the first character is shown.
What are we doing wrong?
we have got trial version of TD and recompiled with very small changes an old project (CTD1.5.1). However, by starting application we run immediately into Oracle errors. It seems that calls to Oracle procedures do not work as before. In CTD1.5.1 to call procedure in the Oracle package it was enough to do the following:
Set lsSql = "DBMS_APPLICATION_INFO.SET_MODULE(psModuleName,psAction)"
If SqlPLSQLCommand(phSql,lsSql)
...
Now:
1. Without SYS specificator the procedure will not be found.
2. Even with SYS specificator, we get ORA-24328 or ORA-24323.
In the log file in new version from all bind variables only the first character is shown.
What are we doing wrong?
Re: TD-3949: Calls to procedures does not work
I have exactly the same problem (same procedure and same error) !
Any clue ?
Etienne
Any clue ?
Etienne
Re: TD-3949: Calls to procedures does not work
No, we still don't understand it.
Have you any idea what should be done in order to be able execute procedures from another schema? Grants and public synonyms are Okey, but somehow it is not enough.
I see that some post attract attention of Gupta developer, this one unfortunately not.
Have you any idea what should be done in order to be able execute procedures from another schema? Grants and public synonyms are Okey, but somehow it is not enough.
I see that some post attract attention of Gupta developer, this one unfortunately not.
Re: TD-3949: Calls to procedures does not work
Hi,
"I see that some post attract attention of Gupta developer, this one unfortunately not."
It did :-) It is important especially the title of your post. So here is what I tested and found out
1)You are right there is a problem in the TD 5.1 native router with SqlPLSQLCommand
2)There is no problem it seems (at least with my testcase) with the SqlORA**() function instead
3)Thre is no problem with OLEDB neither.
So the issue is NOT that something speciall need to be done on the ORACLE side (I use 10g), since with TD 4.2 the testcase works fine with NATIVE router and SqlPLSQLCommand/SqlOra**()/and OLEDB SqlPrepareSP...and it is not in TD 5.1 for NATIVE ROUTER and SqlPLSQLCommand but the rest does...thus clearly a bug...
So here is the BUG LOG TD-3949 ...
Currently to worakround, you can use SqlORA***() functions it seems, as you probably know you cannot use DYNAMIC ARRAYS, that is the only limitation. Overall we always recommanded the uses of those functions instead of SqlPLSQLCommand() for at least performance issues. The syntax change is minimal also so there should not be to much problem switching to them. You can use also OLEDB, but you will have to test further your application and do changes ie ( SqlImmediate, SqlVarSetup not supported on OLEDB etc..)
I attach my testcase so you can confirm this is indeed the problem and like to know if for you SqlORA**() fixes this issue. If you do have problem with SqlORA***() or anything else, please consider passing a testecase if possible because it really helps us to speed up on those issues.
Thanks
Jean-Marc
"I see that some post attract attention of Gupta developer, this one unfortunately not."
It did :-) It is important especially the title of your post. So here is what I tested and found out
1)You are right there is a problem in the TD 5.1 native router with SqlPLSQLCommand
2)There is no problem it seems (at least with my testcase) with the SqlORA**() function instead
3)Thre is no problem with OLEDB neither.
So the issue is NOT that something speciall need to be done on the ORACLE side (I use 10g), since with TD 4.2 the testcase works fine with NATIVE router and SqlPLSQLCommand/SqlOra**()/and OLEDB SqlPrepareSP...and it is not in TD 5.1 for NATIVE ROUTER and SqlPLSQLCommand but the rest does...thus clearly a bug...
So here is the BUG LOG TD-3949 ...
Currently to worakround, you can use SqlORA***() functions it seems, as you probably know you cannot use DYNAMIC ARRAYS, that is the only limitation. Overall we always recommanded the uses of those functions instead of SqlPLSQLCommand() for at least performance issues. The syntax change is minimal also so there should not be to much problem switching to them. You can use also OLEDB, but you will have to test further your application and do changes ie ( SqlImmediate, SqlVarSetup not supported on OLEDB etc..)
I attach my testcase so you can confirm this is indeed the problem and like to know if for you SqlORA**() fixes this issue. If you do have problem with SqlORA***() or anything else, please consider passing a testecase if possible because it really helps us to speed up on those issues.
Thanks
Jean-Marc
You do not have the required permissions to view the files attached to this post.
Re: TD-3949: Calls to procedures does not work
Hi Jean-Marc,
thank you for answering.
We would use SqlOraPLSQL* functions instead of SqlPLSQLCommant, but for some reason it does not work either.
I have attached a screen short with your test case, where count is 0 (must be 1).
No error message.
What could it be?
Thank you,
Vladimir
thank you for answering.
We would use SqlOraPLSQL* functions instead of SqlPLSQLCommant, but for some reason it does not work either.
I have attached a screen short with your test case, where count is 0 (must be 1).
No error message.
What could it be?
Thank you,
Vladimir
You do not have the required permissions to view the files attached to this post.
Re: TD-3949: Calls to procedures does not work
Hi there,
First I thought I overlooked something before telling you it works, but it does actually for me, see snapshot. Also was confused for a few about SqlFetchNext() and thought was needed but obvisouly. I also set now the nCount variable to 0 before the call and got 1 as a result as it should. So for me this is working fine both SqlORA and OLEDB
Can you try OLEDB with the testcase, you just need to modify the attached UDL it might tell us more...
Also I attach process explorer DLL loaded for CBI51.EXE during the test with it you can see what I used...I used ORA 10gR2 on VISTA...
Let me know if you find something and the results wiht OLEDB..
JM
First I thought I overlooked something before telling you it works, but it does actually for me, see snapshot. Also was confused for a few about SqlFetchNext() and thought was needed but obvisouly. I also set now the nCount variable to 0 before the call and got 1 as a result as it should. So for me this is working fine both SqlORA and OLEDB
Can you try OLEDB with the testcase, you just need to modify the attached UDL it might tell us more...
Also I attach process explorer DLL loaded for CBI51.EXE during the test with it you can see what I used...I used ORA 10gR2 on VISTA...
Let me know if you find something and the results wiht OLEDB..
JM
You do not have the required permissions to view the files attached to this post.
Re: TD-3949: Calls to procedures does not work
Hi Again,
Sorry forgot the process explorer file and also try with the change you did
Call SqlOraPLSQLPrepare( hSql, 'BEGIN pkgtest.test(:nCount); END;' )
In my test did not use BEGIN END... I still have 1 as return.
JM
Sorry forgot the process explorer file and also try with the change you did
Call SqlOraPLSQLPrepare( hSql, 'BEGIN pkgtest.test(:nCount); END;' )
In my test did not use BEGIN END... I still have 1 as return.
JM
Re: TD-3949: Calls to procedures does not work
Again as the name contained .exe...
You do not have the required permissions to view the files attached to this post.
Re: TD-3949: Calls to procedures does not work
Hi,
thank you very much for help.
The usage of SqlOraPLSQL* functions solves the problem with SqlPLSQLCommand. Although we have to check all calls to stored
procedures.
It works also with OLEDB.
Can you estimate,when the problem with SqlPLSQLCommand will be solved?
Vladimir
thank you very much for help.
The usage of SqlOraPLSQL* functions solves the problem with SqlPLSQLCommand. Although we have to check all calls to stored
procedures.
It works also with OLEDB.
Can you estimate,when the problem with SqlPLSQLCommand will be solved?
Vladimir
Re: TD-3949: Calls to procedures does not work
Hi Vladimir,
Yes I can imagine checking all SP and change them to SqlORA is a pain... but I guess it is worth it. Still this needs to get fixed, but cannot give you an estimate but ORACLE access is used by many and that make this issue very important. I will check non the less.
Cheers
Jean-Marc
Yes I can imagine checking all SP and change them to SqlORA is a pain... but I guess it is worth it. Still this needs to get fixed, but cannot give you an estimate but ORACLE access is used by many and that make this issue very important. I will check non the less.
Cheers
Jean-Marc
Re: TD-3949: Calls to procedures does not work
Hi,
probably I should open another topic, just tell me that.
We are running into more problems.
Please, have a look in the attached file.
The log shows some query, that fills the table. After that,
some value in one of the records in the result set is changed and
update is applied. This does not work. The same happens with delete
command.
Two points are probably important:
(1) ROWID bind variable is lost;
(2) only the first character is shown for all other bind variables.
In the older version both things are different and both update and delete work. What is wrong?
Vladimir
probably I should open another topic, just tell me that.
We are running into more problems.
Please, have a look in the attached file.
The log shows some query, that fills the table. After that,
some value in one of the records in the result set is changed and
update is applied. This does not work. The same happens with delete
command.
Two points are probably important:
(1) ROWID bind variable is lost;
(2) only the first character is shown for all other bind variables.
In the older version both things are different and both update and delete work. What is wrong?
Vladimir
You do not have the required permissions to view the files attached to this post.
Re: TD-3949: Calls to procedures does not work
Hi Valdimir,
I'm sure you have seen those issues already
TD-3948
TD51 Oracle router reports ORA-01410: invalid ROWID when updating a table with rowid
It seems to be related but like to be sure about it.
Jean-Marc
I'm sure you have seen those issues already
TD-3948
TD51 Oracle router reports ORA-01410: invalid ROWID when updating a table with rowid
It seems to be related but like to be sure about it.
Jean-Marc
Re: TD-3949: Calls to procedures does not work
Hi Jean-Marc,
we use TD 5.1.0.5917
Should it be already fixed in this build?
If yes, then there is still a problem.
Vladimir
we use TD 5.1.0.5917
Should it be already fixed in this build?
If yes, then there is still a problem.
Vladimir
Re: TD-3949: Calls to procedures does not work
Hi Vladimir,
Not in yours currently, it was a recent fix and I like to check it.
Jean-Marc
Not in yours currently, it was a recent fix and I like to check it.
Jean-Marc
Re: TD-3949: Calls to procedures does not work
Hi Jean-Marc,
have you got my test case for ROWID problem?
Vladimir
have you got my test case for ROWID problem?
Vladimir
Who is online
Users browsing this forum: [Ccbot] and 11 guests