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::gmail 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:gmai:l 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