[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Oracle Stored procedures

Posted on 2001-07-12
5
Medium Priority
?
251 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 200 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month7 days, 21 hours left to enroll

607 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