Oracle Stored Procedures

Hi,

Do Oracle Stored Procedures work with DBXTalk ?

It appears they don't:

connection executeMultiStatement: 'exec usrtest.test();'.

and multiple variations of this all give:

RECOVERABLE OpenDBX: ORA-00900 invalid SQL statement

Thanks

I'm glad to say that the solution above does work, the stored
procedure does get called as required:


v1 := 'test1'.
s1 := 'begin usrtab.usrcred.test(''',v1,''');end;'.
res := [connection enableMultipleStatements. connection
executeMultiStatement:(s1).connection commit] on: Error do: [ :ex|
Transcript show: ex messageText;cr.].

Cheers

On Feb 16, 12:05 pm, kromakey <[hidden email]> wrote:
> Hi,
>
> Although that clears the error mesage, the procedure doesn't appear to
> get called ??
>
> Any ideas?
>
> Thanks
>
> On Feb 15, 10:05 pm, kromakey <[hidden email]> wrote:
>
>
>
> > Hi,
>
> > I think it needs something like the following to work:
>
> > connection executeMultiStatement: 'begin usrtest.test(); end;'.
>
> > Well it's no longer throwing the ORA-00900 error, not sure the proc is
> > working though.
>
> > Cheers
>
> > On Feb 15, 5:10 pm, kromakey <[hidden email]> wrote:
>
> > > Hi,
>
> > > I've tried removing the ';' and I am still getting the same error.
>
> > > Thanks
>
> > > On Feb 15, 3:40 pm, Mariano Martinez Peck <[hidden email]>
> > > wrote:
>
> > > > On Wed, Feb 15, 2012 at 2:38 PM, kromakey <[hidden email]> wrote:
> > > > > Hi,
>
> > > > > Do Oracle Stored Procedures work with DBXTalk ?
>
> > > > Hi. I am not sure. I never tried. I only tried for MySQL and PostgreSQL.
> > > > Check the DBXStoredProceduresTest
>
> > > > > It appears they don't:
>
> > > > > connection executeMultiStatement: 'exec usrtest.test();'.
>
> > > > > and multiple variations of this all give:
>
> > > > > RECOVERABLE OpenDBX: ORA-00900 invalid SQL statement
>
> > > > Can you try connection executeMultiStatement: 'exec usrtest.test()'.
> > > > note that I removed the ';' of the end.
>
> > > > Let us know.
>
> > > > > Thanks
>
> > > > --

> > > > Marianohttp://marianopeck.wordpress.com-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -

>
> - Show quoted text -

Hi,

Here's a test DBXStoredProceduresTest:

###################################################

testOracleFunctions
       | conn result resultSet cmd param |

       (self platform = DBXOracleBackend ) ifFalse: [ ^self ].

       conn := self doConnect.
       result := conn enableMultipleStatements.
       conn open.

       result ifFalse: [
               conn disconnect.
               ^ self].

       "
       drop table testab;
               create table testab(name varchar2(20));

               create or replace procedure testproc(par in varchar2)
               as
                    sql_string varchar2(500);
                    mycursor integer;
                    retval integer;
               begin
                    sql_string := 'insert into testab(name)
values('''||par||''')';
                    mycursor := dbms_sql.open_cursor;

dbms_sql.parse(mycursor,sql_string,dbms_sql.native);
                    retval := dbms_sql.execute(mycursor);
                    dbms_sql.close_cursor(mycursor);
               end testproc;
       "

               param := 'testinsert'.
       cmd := 'begin testproc(''',param,''') end;'.
       resultSet := conn executeMultiStatement:(cmd).
       conn commit;
       DBXTranscript show: resultSet.

       conn disconnect.

###################################################


Cheers
Colin

On Feb 16, 3:29 pm, Mariano Martinez Peck <[hidden email]>
wrote:

> On Thu, Feb 16, 2012 at 4:23 PM, kromakey <[hidden email]> wrote:
> > I'm glad to say that the solution above does work, the stored
> > procedure does get called as required:
>
> > v1 := 'test1'.
> > s1 := 'begin usrtab.usrcred.test(''',v1,''');end;'.
> > res := [connection enableMultipleStatements. connection
> > executeMultiStatement:(s1).connection commit] on: Error do: [ :ex|
> > Transcript show: ex messageText;cr.].
>
> Excellent news! Thanks for pushing this :)
> Would you like to create a test is DBXStoredProceduresTest ? so that we can
> integrate it.
> Otherwise, I will open an issue to do it when I have time (really busy
> until middle April).
>
> Thanks
>
>
>
>
>
> > Cheers
>
> > On Feb 16, 12:05 pm, kromakey <[hidden email]> wrote:
> > > Hi,
>
> > > Although that clears the error mesage, the procedure doesn't appear to
> > > get called ??
>
> > > Any ideas?
>
> > > Thanks
>
> > > On Feb 15, 10:05 pm, kromakey <[hidden email]> wrote:
>
> > > > Hi,
>
> > > > I think it needs something like the following to work:
>
> > > > connection executeMultiStatement: 'begin usrtest.test(); end;'.
>
> > > > Well it's no longer throwing the ORA-00900 error, not sure the proc is
> > > > working though.
>
> > > > Cheers
>
> > > > On Feb 15, 5:10 pm, kromakey <[hidden email]> wrote:
>
> > > > > Hi,
>
> > > > > I've tried removing the ';' and I am still getting the same error.
>
> > > > > Thanks
>
> > > > > On Feb 15, 3:40 pm, Mariano Martinez Peck <[hidden email]>
> > > > > wrote:
>
> > > > > > On Wed, Feb 15, 2012 at 2:38 PM, kromakey <[hidden email]>
> > wrote:
> > > > > > > Hi,
>
> > > > > > > Do Oracle Stored Procedures work with DBXTalk ?
>
> > > > > > Hi. I am not sure. I never tried. I only tried for MySQL and
> > PostgreSQL.
> > > > > > Check the DBXStoredProceduresTest
>
> > > > > > > It appears they don't:
>
> > > > > > > connection executeMultiStatement: 'exec usrtest.test();'.
>
> > > > > > > and multiple variations of this all give:
>
> > > > > > > RECOVERABLE OpenDBX: ORA-00900 invalid SQL statement
>
> > > > > > Can you try connection executeMultiStatement: 'exec
> > usrtest.test()'.
> > > > > > note that I removed the ';' of the end.
>
> > > > > > Let us know.
>
> > > > > > > Thanks
>
> > > > > > --

> > > > > > Marianohttp://marianopeck.wordpress.com-Hidequoted text -

>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -
>
> --

> Marianohttp://marianopeck.wordpress.com- Hide quoted text -

>
> - Show quoted text -