Orastoredproc out params with error

[akrein]

hi, i'm a newbie here,my environment are delphi 7, ODAC 6.10,oracle 10g.
i tried to make a transaction form using sequence number, then i want to catch the value from sequence number in order to increment transaction number,and insert into master and detail table.

my scenario like this:

SQL> conn scott/tiger
SQL> drop sequence order_lines_seq;
SQL> drop SEQUENCE orders_seq;
SQL> drop index INDEX orli_orde_fk_i;
SQL> CREATE TABLE orders (
2 id NUMBER(10),
3 description VARCHAR2(50),
4 CONSTRAINT orders_pk PRIMARY KEY (id)
5 );
SQL> CREATE SEQUENCE orders_seq;
SQL> CREATE TABLE order_lines (
2 id NUMBER(10),
3 order_id NUMBER(10),
4 description VARCHAR2(50),
5 CONSTRAINT order_lines_pk PRIMARY KEY (id),
6 CONSTRAINT orli_orde_fk FOREIGN KEY (order_id) REFERENCES orders(id)
7 );
SQL> CREATE INDEX orli_orde_fk_i ON order_lines(order_id);
SQL> CREATE SEQUENCE order_lines_seq START WITH 100;

CREATE OR REPLACE procedure insert_order(
l_order_id out orders.id%TYPE)is
BEGIN
INSERT INTO orders (id,description)
VALUES (orders_seq.NEXTVAL,'Dummy order description.')
RETURNING id INTO l_order_id;
INSERT INTO order_lines (id,order_id, description)
VALUES (order_lines_seq.NEXTVAL, l_order_id, 'Dummy order line description');
COMMIT;
END;


Then i used Orastoredproc with procedure name insert_order,but when executed or change active into true, the error message came out: Sql statement doesn't return row

But when i double click on orastoreproc then look at tab: params, press execute button, the value can came out with increment number.

how to catch the value that came out from sequence number in order to increment the Transaction number?
does anyone can help my problem....thanks in advance helps...

Regards, Akrein

[Plash]

You should call the Execute method of TOraStoredProc instead of Open. After that you can read the parameter value:

Code: Select all

OraStoredProc.Execute;
i := OraStoredProc.ParamByName('L_ORDER_ID').AsInteger;

[akrein]

Sorry for the delay, the problem still occured when prepared with the error message:.
Debugger Exception notification
Project Mainproj.exe reised exception class EAccessViolation with message 'Access violation at address 0064467D in module 'Main Proj.exe'. Read of address 00000080'. Process stopped.Use Step or Run to continue.

The error accured when

dm.orastoredproc1.prepare;

The error still occured event when i don't used prepare, but using

dm.orastoredproc1.execute;

On design mode, when i change orastoredproc1 active into true the error occured sql statement doesn't return rows.

does anyone can help me? where's the problem?
Does stored procedure is the problem coz i don't use cursor?

thanks

[Challenger]

I have tried your stored procedure with the following code:

Code: Select all

  Ora := TOraSession.Create(nil);
  Ora.Server := 'ora';
  Ora.Username := 'test';
  Ora.Password := 'test';
  Ora.Connect;

  OraStoredProc := TOraStoredProc.Create(nil);
  OraStoredProc.StoredProcName := 'insert_order';
  OraStoredProc.Prepare;

It works. Please send us a small sample to reproduce the problem. And specify the version of Oracle and Delphi you are using.

[akrein]

Hi, Thanks for the reply.

When i try to create orastoredproc on program mode like the example you give above, it's works fine, the problem occured when using orastoredproc on design mode, maybe i should change into program mode then.

But what's make me curious is,in my case, why orastoredproc can't run smoothly on design mode? but it's works fine on program mode?

On design mode what i means are, i click on component orastoredproc and put into form, then fill the property orastoredproc.name with insert_order then set active into true, then the error occuredL: sql statement doesn't returns no rows.


Thanks for the advance helps.

[Challenger]

This is correct behavior. Setting the Acitve property to True forces to dataset opening. As your query does not have refcursor out parameters you get this error. To get the list of parameters in design time you have to open the editor.