Returning a PL/SQL table to Delphi TDataSet

[josmos]

Hello,

I would like to access a PL/SQL
table into a Delphi TdataSet object.

I use a stored procedure which contains
the PL/SQL table in OUTPUT and in the
SQL editor of the query I have another table.

When running the query,
I get the following error:

'ORA-06513: PL/SQL table out of range for host language array ORA-06512: at line 8'
Here you can see my PL/SQL codes (the package and the procedure),
the PL/SQL of the query and the Delphi code.

Thanks!

-------------------------------------------------------------------
--------------------------- PL/SQL Package -----------------------
-------------------------------------------------------------------
PACKAGE PCK_Commentaires_Vides
IS

Type Typetable_Possibilites IS TABLE OF VarChar (300)
Index by BINARY_INTEGER;

Tb_Resultats_Sortie Typetable_Possibilites;

End PCK_Commentaires_Vides;

-------------------------------------------------------------------
----------------------- PL/SQL Stored Procedure -------------------
-------------------------------------------------------------------
PROCEDURE P_Commentaires_Vides_Test(
Tb_Resultats_Sortie OUT PCK_Commentaires_Vides.TypeTable_Possibilites
)
IS

-- Declaration des tableaux--

Tb_Resultats PCK_Commentaires_Vides.TypeTable_Possibilites;

-- Curseurs --

Cursor Sans_Commentaires_Table is
Select Table_Name
From All_Col_Comments
Where Owner = 'DB'
and Comments is Null
and Column_Name in ( Select Column_Name
From All_Col_Comments
Where Owner = 'DB'
and Comments is Not NULL)
Order by Column_Name;

Cursor Sans_Commentaires_Colonne is
Select Column_Name
From All_Col_Comments
Where Owner = 'DB'
and Comments is Null
and Column_Name in ( Select Column_Name
From All_Col_Comments
Where Owner = 'DB'
and Comments is Not NULL)
Order by Column_Name;

-- Variables --

nom_colonne VarChar2 (255);
nom_table VarChar2 (255);
Compteur Integer;

-- Code --

Begin

Compteur := 1;

Open Sans_Commentaires_Colonne;
Open Sans_Commentaires_Table;

Fetch Sans_Commentaires_Colonne into nom_colonne;
Fetch Sans_Commentaires_Table into Nom_Table;

Loop

Tb_Resultats(Compteur) := 'Comments on ' | | Nom_Table | | '.' | | nom_colonne | | ' is null';

Fetch Sans_Commentaires_Colonne into nom_colonne;
Fetch Sans_Commentaires_Table into Nom_Table;

If Sans_Commentaires_Colonne%Notfound
Then
Exit;
End IF;

Compteur := Compteur + 1;

End Loop;

Tb_Resultats_Sortie := Tb_Resultats;
Tb_Resultats_Sortie(0) := Tb_Resultats.Count;

END P_Commentaires_Vides_Test;
/

-------------------------------------------------------------------
--------------------- OracleQuery1 SQL Code -----------------------
-------------------------------------------------------------------
--:TABLE_QUERY is declared in the Variable reference as a Pl-SQL --
--Table of strings. Table Size: 1000 String Size: 300 --
-------------------------------------------------------------------
-- The error messages are: --
-- ORA-06513: PL/SQL table out of range for host language array --
-- ORA-06512: at line 8 --
-------------------------------------------------------------------

Declare
TB_RESULTATS_SORTIE PCK_Commentaires_Vides.Typetable_Possibilites;

Begin

DB.P_COMMENTAIRES_VIDES_TEST(TB_RESULTATS_SORTIE);

:Table_Query := TB_RESULTATS_SORTIE;

End;

-------------------------------------------------------------------
--------------------------- Delphi Code ---------------------------
-------------------------------------------------------------------

procedure TForm1.FormCreate(Sender: TObject);

Var
Table_Sortie: Array [0..1000] of String;
Compteur, Val_Compteur: String;

begin

With OracleQuery1 Do
Begin

Execute;
Session.Commit;

Table_Sortie := GetVariable('TABLE_QUERY');

End;

Val_Compteur := StrToInt(Table_Sortie[0]);

For Compteur := 1 To Val_Compteur Do
Begin

Memo1.Lines.Add(Table_Sortie[Compteur]);

End;

end;

 

[Marco Kalter]

What is the table size of the :Table_Query variable as declared in your TOracleDataSet? Is it big enough to hold the data?

------------------
Marco Kalter
Allround Automations

 

[lgomez]

The :TABLE_QUERY size is (Table Size: 1000 String Size: 300) and the PL/SQL table is made of varstring2 (300) and has got about 750 lines.

 

[Marco Kalter]

I notice you are using element 0:

Tb_Resultats_Sortie(0) := Tb_Resultats.Count;

What happens if you omit that (or place it at element 1)?

------------------
Marco Kalter
Allround Automations

 

[lgomez]

Thanks!

That was the problem! Now my program is okay!