arrays not working in 5.50.0.16

[lnardozi]

Why does this line work if I'm connected normally, but not if I'm connected using NET8?
stpPrimaryKeys.ParamByName('TABLE_NAME').AsArray.AllocObject('varchar2array');

Thanks,
Louis Nardozi

[Alex]

Oracle objects, arrays, refs are not supported in ODAC with Net option.
Pls. see "Working with ODAC Net" topic of ODAC help for more info.

[lnardozi]

If I can't use an array or an object, is there something else I can use to pass 1..n rows of information into a stored procedure? Can I pass a table as a paramerter and use .AsTable?

I have a stored procedure that takes 3 varchar arguments and I'd like to send in some kind of list in each of the three positions. Any advice?

Thanks,
Louis

[Alex]

You can use PL/SQL Table. see ODAC help and PLSQLTable demo project.

[Víctor]

You can use PL/SQL Table. see ODAC help and PLSQLTable demo project.

Hello, I have a similar situation. After viewing the examples mencioned, i can use the PL/SQL Table in a OraSql component but not in a OraStoredProc component.

In the OraSQL i have

DECLARE
i INTEGER;
c integer;
BEGIN
i:= 0;
FOR r IN (SELECT secuencia FROM vigencias_tarifa WHERE id_tarifa =:prangos(1) or id_tarifa =:prangos2(1)) loop
i := i+1;
END LOOP;
:pcontador := i;
END;

where :prangos is plsql table of integer and prangos2 plsql table of string

In the other side, i have an oracle stored procedure with a parameter of type t_rangos_str, which was created as TABLE OF VARCHAR2(200). When i try too run this using a ORAStroredProc component and such parameter as a plsql table of string i get the message "PLS-00306 wrong number or types of arguments".

Please Help

[BalthaZar]

I have the same problem.

I looked at the example but I can't use it in a stored procedure.
I couldn't figure out how to declare the stored procedure in Oracle so that it has the same properties as the example.

I tried this at first:
type t1 is table of varchar2(10);

and used it as parameter. But in the component, it is shown as table (not PL/SQL Table as in the example)

When I used this:
type t1 is varray(10) of varchar2(10);

Then it came as an array which I still couldn't use as in the example.

How do I declare a stored procedure parameter so that I can use it with the NET option enabled?

[BalthaZar]

Ok, I did it

Oracle side:

Code: Select all

CREATE OR REPLACE PACKAGE hr.test
AS
  TYPE type1 IS TABLE OF VARCHAR2 (100)
    INDEX BY BINARY_INTEGER;

  PROCEDURE pArray (p1 IN type1, pOut OUT VARCHAR2);
END test;
/

CREATE OR REPLACE PACKAGE BODY hr.test
AS
  PROCEDURE pArray (p1 IN type1, pOut OUT VARCHAR2)
  AS
  BEGIN
    pOut := p1(1);
    pOut := pOut || ' ' || p1(2);
    pOut := pOut || ' ' || p1(3);
  END pArray;
END test;
/

Delphi side:
I put a TOraStoredProc on the form and a button. I set the StoredProcName of the TOraStoredProc and in the click event of the button:

Code: Select all

Tform1.Button1Click(Sender: TObject);
begin
  with OraStoredProc1 do
  begin
    Prepare;
    ParamByName('p1').ItemAsString[1] := 'This';
    ParamByName('p1').ItemAsString[1] := 'is a';
    ParamByName('p1').ItemAsString[1] := 'test';
    ExecProc;
    ShowMessage(ParamByName('pOut').AsString);
  end;
end;

PS: I have written the code here and it is not a copy/paste. So it may have errors.

[BalthaZar]

Is there a way to clear the array parameters?

I use an array parameters and I put 3 elements in it and call the procedure.
Then I want to reuse the same procedure but this time I want to put 2 elements in the array parameter. But when I call the procedure, there is the 2 new elements that I want and the 3rd element is the one I set in the previous call.

How can reset the storedprocedure's array parameters?