Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle Stored procedures

Posted on 2001-07-12
5
244 Views
Last Modified: 2010-10-05
Hi.

I want to be able to, at runtime, populate a listbox with all the stored procedures in an oracle database.  Then, when I click one, I want to see the required parameters in another listbox.  (Just like the lists you get with the TStoredProc component.)

Thanks in advance,

MoonCalf.
0
Comment
Question by:MoonCalf
  • 4
5 Comments
 
LVL 6

Expert Comment

by:Jaymol
ID: 6277145
Hi MoonCalf.

Try this bit of code....

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, BDE;

type
  TForm1 = class(TForm)
    Database1: TDatabase;
    Button1: TButton;
    ListBox1: TListBox;
    procedure GetStoredProcNames;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.GetStoredProcNames;
var
  Database: TDatabase;
  Cursor: HDBICur;
  Name: string;
  Desc: SPDesc;
begin
  ListBox1.Items.BeginUpdate;
  try
    ListBox1.Items.Clear;
    Database1.Connected:=True;
    try
      Check(DbiOpenSPList(Database.Handle, False, True, nil, Cursor));
      try
        while DbiGetNextRecord(Cursor, dbiNOLOCK, @Desc, nil) = 0 do
          with Desc do
          begin
            NativeToAnsi(Database.Locale, szName, Name);
            ListBox1.Items.Add(Name);
          end;
      finally
        DbiCloseCursor(Cursor);
      end;
    finally
      Database1.Connected:=False;
    end;
  finally
    ListBox1.Items.EndUpdate;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  GetStoredProcNames;
end;

end.


You just need a form with a Button and ListBox on it.

This is NOT a generic procedure.  You'll have to reword it to make it work in any other instances.

Hope this helps you out,

John.
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6277153
Oh yeah, and a TDatabase component.

(I actually just pinched this from the TStoredProc code in the DBTables unit.)

John.
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6277164
Sorry, that was a bit buggy.....try this.


procedure TForm1.GetStoredProcNames;
var
  Cursor: HDBICur;
  Name: string;
  Desc: SPDesc;
begin
  ListBox1.Items.BeginUpdate;
  try
    ListBox1.Items.Clear;
    Database1.Connected:=True;
    try
      Check(DbiOpenSPList(Database1.Handle, False, True, nil, Cursor));
      try
        while DbiGetNextRecord(Cursor, dbiNOLOCK, @Desc, nil) = 0 do
          with Desc do
          begin
            NativeToAnsi(Database1.Locale, szName, Name);
            ListBox1.Items.Add(Name);
          end;
      finally
        DbiCloseCursor(Cursor);
      end;
    finally
      Database1.Connected:=False;
    end;
  finally
    ListBox1.Items.EndUpdate;
  end;
end;
0
 
LVL 6

Accepted Solution

by:
Jaymol earned 50 total points
ID: 6277315
Then add this as well, after adding another ListBox, ListBox2, to the form....

procedure TForm1.ListBox1Click(Sender: TObject);
var
  TmpSP : TStoredProc;
  TmpI  : Integer;
begin
  TmpSP:=TStoredProc.Create(nil);
  TmpSP.DatabaseName:=Database1.DatabaseName;
  TmpSP.StoredProcName:=ListBox1.Items[ListBox1.ItemIndex];
  TmpSP.Prepare;
  ListBox2.Clear;
  For TmpI:=0 to TmpSP.ParamCount-1 do
    ListBox2.Items.Add(TmpSP.Params[TmpI].Name);
  TmpSP.Free;
end;

And there you go!

John.
0
 
LVL 1

Author Comment

by:MoonCalf
ID: 6277325
Perfect.

Thanks John.

MoonCalf.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi XE10, MySQL Query 4 164
Multi-layered image in FireMonkey 9 48
How to Get Images From Server using App Tethering 11 39
Browsing a TTreeView in Delphi 5 7
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question