Mailing List Archive: 49091 messages
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

Any experience with REBOL + Oracle here?

 [1/9] from: petr::krenzelok::trz::cz at: 2-Jun-2006 14:24


Hi, I have following example, where I wan to send to Oracle PL SQL script: cmd: { declare i number; begin i := 1; while i < 11 loop dbms_output.put_line(to_char(i)); i := i + 1; end loop; end; } db: open oracle://ble:ble-DB_BLE db-port: first db insert db-port cmd And it crashes REBOL. I wonder how does it work internally in REBOL? Does it try to parse the string? Hopefully not? Should REBOL generally be able to handle such kind of db access? Thanks, Petr

 [2/9] from: ale870:gmai:l at: 2-Jun-2006 16:17


I don't understand if your script is pl/sql. If so, I think you forgot procedure is prefix. More: PL/SQL does NOT use "{" but "begin... end". Think that PL/SQL is a branch of ADA language (similar to Pascal). You can write procedure as follows (first declare is not needed): create or replace procedure cmd is i number; begin i := 1; while i < 11 loop dbms_output.put_line(to_char(i)); i := i + 1; end loop; end; Another question: do you get any error, or simply crash? --Alessandro On 6/2/06, Petr Krenzelok <petr.krenzelok-trz.cz> wrote:

 [3/9] from: petr:krenzelok:trz:cz at: 2-Jun-2006 16:26


Alessandro Manotti wrote:
> I don't understand if your script is pl/sql. If so, I think you forgot > "procedure is" prefix.
<<quoted lines omitted: 12>>
> Another question: do you get any error, or simply crash? > --Alessandro
hi, I get a crash. Even via configured odbc Oracle driver and something as easy as: declare i number; begin i:=1; end; it still crashes ... -pekr-

 [4/9] from: ale870:g:mail at: 2-Jun-2006 16:31


Did you try to execute something using a DB editor client like AquaStudio, SQL Navigator, TOAD, etc... (specific tools to work with Oracle). If you haven't them, did you try with SQL Plus? (it does not use any odbc connection, but works directly with TNS Names). --Alessandro On 6/2/06, Petr Krenzelok <petr.krenzelok-trz.cz> wrote:

 [5/9] from: petr:krenzelok:trz:cz at: 2-Jun-2006 16:41


Alessandro Manotti wrote:
> Did you try to execute something using a DB editor client like > AquaStudio, SQL Navigator, TOAD, etc... (specific tools to work with > Oracle). > > If you haven't them, did you try with SQL Plus? (it does not use any > odbc connection, but works directly with TNS Names). > > --Alessandro >
TOAD: cmd: "i number; begin i:=1; dbms_output.put('ahoj'); end;" so - a valid code .... which crashes rebol .... -pekr-

 [6/9] from: ale870::gmail at: 2-Jun-2006 16:48


Maybe Rebol expect e returning value (I''m not sure, since I have only view/pro and I cannot use SQL :-( ). 1) which version of Oracle do you use (in Rebol site I saw Rebol supports up-to version 8). 2) did you try with a simple "select sysdate from dual"? 3) did you try a "insert" statement (no returning value)? --Alessandro On 6/2/06, Petr Krenzelok <petr.krenzelok-trz.cz> wrote:

 [7/9] from: petr:krenzelok:trz:cz at: 2-Jun-2006 17:09


Alessandro Manotti wrote:
> Maybe Rebol expect e returning value (I''m not sure, since I have only > view/pro and I cannot use SQL :-( ). > > 1) which version of Oracle do you use (in Rebol site I saw Rebol > supports up-to version 8). >
I will have to check with admins ....
> 2) did you try with a simple "select sysdate from dual"? > 3) did you try a "insert" statement (no returning value)? >
select, insert etc. works ... the problem is with PL SQL .... returning value or not. Actually I don't know, how to "return" a value .... I thought that put_line etc functions kind of "return a value" -pekr-

 [8/9] from: karim:elfounas:easybraine at: 2-Jun-2006 18:18


Petr Krenzelok a écrit :
> Alessandro Manotti wrote: >> Maybe Rebol expect e returning value (I''m not sure, since I have only
<<quoted lines omitted: 16>>
>> >>
Hello Petr, It seems that Rebol/command has a problem. If Rebol throw an exception, I can suspect a syntax problem, but a crash let me think about a real bug (maybe a new RAMBO bug) I think that using dbms_output will not resolve your problem, because this output is stored in a buffer but not in the same channel than the result set. But there is maybe a solution for you. I'm not sure because I've no Oracle server to test it but I'll try to give you the way. In PL/SQL you are able to "execute" a string with execute_immediate : execute_immediate('insert into mytable (field1,field2) values(23,67898)'); Via a tool like Toad, write a small PL/SQL function (not a procedure) that just execute the string passed as parameter : create or replace EXECUTE_THIS ( PARAM_SQLCODE in varchar2) RETURN INTEGER AS begin execute_immediate(PARAM_SQLCODE); return 1; end EXECUTE_THIS And now, I hope you are able to execute a simple select statement to execute your code. In oracle you can get result of a function by using select on a virtual table called "dual" : insert dbport rejoin [ "select execute_this('" my_plsql-code "') as result from dual"] In my example, the result returned by oracle will be 1, allways. As I said I just made this piece of code without test. There are certainly syntax errors and I'm not sure that's a solution. Hope it will help you. -- Karim EL FOUNAS EasyBraine SA Campagne des Rites, 8 - 1421 Ophain Bois-Seigneur-Isaac Tél : +32 2 387 32 34 Mobile : +32 495 57 32 34 Email : karim.elfounas-easybraine.com

 [9/9] from: ale870::gmail::com at: 2-Jun-2006 18:43


Good idea Karim! A workaround for a bug (?) --Alessandro On 6/2/06, Karim El Founas <karim.elfounas-easybraine.com> wrote:

Notes
  • Quoted lines have been omitted from some messages.
    View the message alone to see the lines that have been omitted