TSQLStoredProcedure parameters

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
Ignacio Soler

TSQLStoredProcedure parameters

Post by Ignacio Soler » Tue 15 Feb 2005 10:30

Hello,

I am trying to execute a PROCEDURE with 1 parameter.

I got the next error:
Parameter PASSI not found.

Any idea?
TIA.

CREATE OR REPLACE PACKAGE meffmes.global_pkg
AUTHID CURRENT_USER
as
PROCEDURE setroles(PASSI IN varchar2);
END;
/
CREATE OR REPLACE PACKAGE BODY meffmes.global_pkg as
PROCEDURE setroles(PASSI IN varchar2) IS
BEGIN
if PASSI = '123456' then
begin
dbms_session.set_role('TIPX_RW_ROLE');
end;
end if;

END;
END global_pkg;


and the method is the next.

astored.SQLConnection:= Connection.Connection;
astored.PackageName:='global_pkg';
astored.StoredProcName:='setroles';
if astored.ParamCheck then
astored.ParamByName('PASSI').AsString:=password;
astored.ExecProc;

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Wed 16 Feb 2005 10:15

Sorry we cannot reproducts your problem with DbxOda 2.50, Delphi 7. Please check if parameters created after you assign StoredProcName. Please check if package and package body are in valide state.

davidsdudley

Oracle package being called from TSQLStoredProc Object.

Post by davidsdudley » Fri 08 Jul 2005 14:43

I see the packagename property on this component.

However it does not have a drop down on it like the storedprocname property does. If I set the package name and SchemaName...
The storedprocname property
1) does not limit the list to the schema I chose
2) does not show the procedure I have created in the package body.

I know the package was created and works
If I set the packagename (by typing it) and the Storedprocname (by typing it)... it does not pull into the Params my output param
If I try to set the Active property to True. I get an "identifier . must be declared" error message.
:evil:

How should packages work with the DBXpress and the DbxOdba 2.50.4 with Delphi 2005?

davidsdudley
Posts: 4
Joined: Fri 08 Jul 2005 14:52

Re: Oracle package being called from TSQLStoredProc Object.

Post by davidsdudley » Fri 08 Jul 2005 14:56

davidsdudley wrote:I see the packagename property on this component.

However it does not have a drop down on it like the storedprocname property does.
- Actually I misspoke... The PackageName DOES have a drop down like the storedprocname... It's just not showing the package. I can see it thru TOAD

davidsdudley
Posts: 4
Joined: Fri 08 Jul 2005 14:52

Anyone?

Post by davidsdudley » Thu 21 Jul 2005 14:07

Does anyone have a suggestion as to what's wrong?

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Thu 21 Jul 2005 15:29

What is the version of Delphi do you use? Does it have Service pack?
Please check that TSQLStoredProc.SQLConnection propery is assigned and TSQLConnection is configyred in design-time.
Regarding to your first question, please check that your procedure and package are valid on Oracle server with other tools or components (Borland Oracle driver, for example)

Send us please small demo project to demonstrate the problem to DbxOda support address and include script to create server objects.

davidsdudley
Posts: 4
Joined: Fri 08 Jul 2005 14:52

Oracle Packages

Post by davidsdudley » Thu 21 Jul 2005 15:45

Paul wrote:What is the version of Delphi do you use? Does it have Service pack?
Please check that TSQLStoredProc.SQLConnection propery is assigned and TSQLConnection is configyred in design-time.
Regarding to your first question, please check that your procedure and package are valid on Oracle server with other tools or components (Borland Oracle driver, for example)

Send us please small demo project to demonstrate the problem to DbxOda support address and include script to create server objects.
Paul:
I'm using Delphi 2005 Service Pack 3
I have tried the OracleConnection type and it works fine; it pulls up list of packages and then I can pull up stored procs from within it.

However the Core Lab version does not.

By the way, what's the difference between "Oracle (Core Lab)" and
"Oracle Net (Core Lab)" ? When I try the Net type I get an "Invalid SID" when I try to connect.

Guest

Re: Oracle Packages

Post by Guest » Mon 25 Jul 2005 11:51

davidsdudley wrote: Paul:
I'm using Delphi 2005 Service Pack 3
I have tried the OracleConnection type and it works fine; it pulls up list of packages and then I can pull up stored procs from within it.

However the Core Lab version does not.

By the way, what's the difference between "Oracle (Core Lab)" and
"Oracle Net (Core Lab)" ? When I try the Net type I get an "Invalid SID" when I try to connect.
So if the Standard Oracle DBXPress driver works, is there anything I can try or is this something that you guys at Core Lab need to work on?


Does anyone know the difference between Oracle (Core Lab) and Oracle Net (Core Lab) driver DLL's for DBXpress?

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Mon 25 Jul 2005 14:15

We cannot reproduce your problem with Delphi 2005 Service Pach 3, DbxOda 2.50.4
"Oracle Net (Core Lab)" can work without installing Oracle client please see Readme.html how to configure "DataBase" parameter.

You must enter ::, where take from SERVICE_NAME parameter in tnsnames.ora. For example DataBase='server:1521:orcl920'

Code: Select all

ORA920 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl920)
      (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES=100)
(DELAY=1))
    )
  )

davidsdudley
Posts: 4
Joined: Fri 08 Jul 2005 14:52

Oracle NET

Post by davidsdudley » Mon 25 Jul 2005 14:21

Paul wrote:We cannot reproduce your problem with Delphi 2005 Service Pach 3, DbxOda 2.50.4
"Oracle Net (Core Lab)" can work without installing Oracle client please see Readme.html how to configure "DataBase" parameter.

You must enter ::, where take from SERVICE_NAME parameter in tnsnames.ora. For example DataBase='server:1521:orcl920'

Code: Select all

ORA920 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl920)
      (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES=100)
(DELAY=1))
    )
  )
OK.

Is there any advantage/disadvantage of using Oracle Net vs Oracle within the Corelab version?

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Tue 26 Jul 2005 07:13

Advantage is a simplicity of destributing your program. But Oracle Net connection has some restriction. Please see "Net mode limitations" in Readme.html

Post Reply