Oracle EXDI: Binding the result of "insert into .. returning..into.."

Hello all,

I need to insert a bunch of blobs into a table. The "classical" way to
do this would be:
1. insert a row with an empty blob
2. select the inserted blob with a suitable "where"-clause, to get the
blob proxy
3. upload the blob data

To avoid the overhead introduced by the select statement, I wanted to
use an "insert into .. returning .. into .." statement. However, it
seems that OracleSession>>bindPrepare wants to bind the return variable
to the input, which yields an error (ORA-00932 inconsistent datatypes).

Example code (BlobHolder has three accessors: name, bytes, proxy):

        | con ses |
        con := OracleConnection new.
        con username: 'coast';
                password: 'coast';
                environment: '//ivws30/db'.
        con connect.
        [ con begin.
                ses := con getSession.
                [ blobHolder := BlobHolder new.
                        blobHolder name: 'Dummy'.
                        blobHolder bytes: #[ 1 2 3 ].
                        ses prepare: 'insert into testblobs1 (name, blob) values (:name,
EMPTY_BLOB()) returning blob into :proxy'.
                        ses answerLobAsProxy.
                        ses bindInput: blobHolder.
                        ses bindOutputNamed: blobHolder.
                        ses execute.
                        blobHolder writeFrom: 1 with: blobHolder bytes.
                        con commit
                ] ensure: [ ses disconnect ]
        ] ensure: [ con disconnect ].

OracleSession>>bindPrepare unfortunately binds :proxy as input, and
execute raises the mentioned error.

Looks like a bug to me. Or is there something I missed?

Cham Püschel wrote:
> Hi Holger,
>
> The only way I was able to do this is with PLSQL. As far as I know the
> returning clause is not standard SQL.
> You only have to change the prepare: into preparePLSQL:
> and bind the variable :proxy  with
> OracleSession>>bindVariableAsLob:value:type:

Thanks, that helped a lot :)

Hi Holger,

The only way I was able to do this is with PLSQL. As far as I know the
returning clause is not standard SQL.
You only have to change the prepare: into preparePLSQL:
and bind the variable :proxy  with
OracleSession>>bindVariableAsLob:value:type:

On the other hand if your blobs aren't to big  (> x MB I don't know the
actual size but 1 Mb works) you can directly insert them with the
"normal" binding.
see:
| con aSession values |
     con := OracleConnection new.
     con username: 'coast';
         password: 'coast';
         environment: '//ivws30/db'.
con connect.

aSession := con getSession.
[aSession prepare: 'create table test(d1 number, str blob)'; execute;
answer.]
     ensure: [aSession disconnect].


aSession := con getSession.
[aSession prepare: 'insert into test (d1, str) values (?, ?)'.
values :=  Array with: #(1 2) with: (Array with: (ByteArray new:
1000000) with: (ByteArray new: 1000000)).
aSession bindInput: values.
aSession execute.
aSession answer.]
     ensure: [aSession disconnect].

This way is extremely fast we never use anything else.


Holger Kleinsorgen wrote: