dynamic sql: execute immediate

[Micky]

Hello all,

I have discovered some strange problems using dynamic SQL:
I have PB 9.0 and Oracle 8i.

1. A stored procedure that is invalid raises an error when
it
is called from within powerbuilder.
2. Sometimes the transaction object that I use for my
dynamic
SQL is 'committed' when I call my dynamic SQL - Statement.

In more details, this is what we do:
(The parameter AutoCommit of MY_Transaction is set to false)

string ls_sql

1. Delete from table a where ... using MY_Transaction;

2. ls_sql = 'alter procedure my_proc compile'
execute immediate :ls_sql using MY_Transaction;
--> Now my first statement is committed! Why?

3. ls_sql = "execute my_proc(parameter1) "
execute immediate :ls_sql using MY_Transaction;

--> this statement would raise an error if my procedure
my_proc got invalid. Therefore I need the statement
above

4. other statements and then in error case: rollback using
MY_Transaction;
--> has effect only for the statements called after 2.

Should I use other dynamic SQL-statements to call my stored
procedure?

Can anybody help me please?

Thank you very much.
Micky

[Bruce Armstrong [TeamSybase]]

On 13 Apr 2004 05:49:46 -0800, Micky wrote:

>Hello all,
>
>I have discovered some strange problems using dynamic SQL:
>I have PB 9.0 and Oracle 8i.
>
>1. A stored procedure that is invalid raises an error when
>it is called from within powerbuilder.

Yep. Unless the person who calls the procedure has rights to
recompile the stored procedure, it will remain invalid until somebody
who does have those rights recompiles it.

It's a good idea to check your database for invalid object anytime you
make and object change.

>2. Sometimes the transaction object that I use for my
>dynamic SQL is 'committed' when I call my dynamic SQL - Statement.
>
>In more details, this is what we do:
>(The parameter AutoCommit of MY_Transaction is set to false)

Orace doesn't care about the setting of AutoCommit.

>
>string ls_sql
>
>1. Delete from table a where ... using MY_Transaction;
>
>2. ls_sql = 'alter procedure my_proc compile'
> execute immediate :ls_sql using MY_Transaction;
> --> Now my first statement is committed! Why?

Because you issued a DDL command.

>
>3. ls_sql = "execute my_proc(parameter1) "
> execute immediate :ls_sql using MY_Transaction;
>
> --> this statement would raise an error if my procedure
> my_proc got invalid. Therefore I need the statement
>above

No. Try:

(a) making sure all your procedures are still valid after any database
mod

and / or

(b) issueing the ALTER ANY PROCEDURE to the PUBLIC role. The alter
any procedure privilidge only allows the user to compile procedures,
not actually modify them.

>
>4. other statements and then in error case: rollback using
>MY_Transaction;
>--> has effect only for the statements called after 2.
>
>Should I use other dynamic SQL-statements to call my stored
>procedure?
>

Well, what you should do is declare them as RPCFUNC external methods
on a user object of type transaction. However, that doesn't have
anything to do with the problems you're experiencing above.

Bruce Armstrong [TeamSybase]
http://www.teamsybase.com

Sixth Annual Sybase Tools Seminar - April 19 - Minneapolis, MN
http://www.powerobjects.com/seminar/?source=newsgroups

Two new books on developing with PowerBuilder
http://www.pb9books.com?source=newsgroups

Need code sample? Check out CodeXchange:
http://www.codexchange.sybase.com

ISUG Enhancement Requests
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

Preach the gospel at all times. If necessary, use words. - Francis of Assisi
http://www.needhim.org

[Mickymac]

Thank you, Bruce.

Your statement about using a DDL command helps me.
However, there remains the problem with the stored procedure
that I would like to understand / solve better:

I am not responsible for the state of this procedure.
Therefore, I cannot ensure, that it is valid, when I call
it.
But I have the right to 'ALTER ANY PROCEDURE'.
Nevertheless, when I execute any procedure that is invalid,
I get the following Oracle Error message:
ORA-24372: Invalid object for describe.

I do not get this error message if I call this procedure
from
other applications (like SQL-Plus).

That's why I am asking for a different way to call this
procedure. (Is it possible, that I cannot call an invalid
procedure using execute immediate, but that I HAVE to follow
your suggestion and declare them as RPCFUNC?)

Thank you very much for your help
Micky

[Phil]

In my experience, there is an issue around PB not recompiling invalid stored
object. Even the object owner is not able to call an invalid object and have it
compiled. My advice is really Bruce's advice of ensuring that all invalid
objects are valid before calling the object from within PB.

Make note that you cannot compile and validate objects from within PB either (as
in issuing a alter compile statement), as I have tried that and that does not
work. You must compile from SQL*Worksheet, OEM, or another product.

Phil