I am using Version 2.0.0.0 Final (May 23rd, 2007)

If I have a stored procedure package in my Oracle (10g) database that has only one procedure, the parameters of that procedure are not retrieved when the catalog is refreshed. If I add a second procedure to the package (with or without parameters), the parameters of the first procedure are recognized.

In the code below, the two parameters for RebuildLocalTable() did not appear in the Designer until I added the second procedure.

PACKAGE "EFAC"."INSTRUCTORADAPTER" IS

PROCEDURE "REBUILDLOCALTABLE" ( "ADDRESSTYPE" IN VARCHAR2, "EMAILTYPE" IN VARCHAR2);

procedure "DUMMY";

END "INSTRUCTORADAPTER";

Could you confirm the DBDriver version you are using? (C:\Program Files\Solutions Design\LLBLGen Pro v2.0\Drivers\Oracle10g)

daelmo wrote:

Could you confirm the DBDriver version you are using? (C:\Program Files\Solutions Design\LLBLGen Pro v2.0\Drivers\Oracle10g)

The DBDriver version is 2.0.7.123.

I am running Windows Vista and am using the Personal Oracle Database 10g Release 10.2.0.3.0 version of Oracle.

Hello,

did you try to create a new lgp file and refresh the store procedure?

jbb wrote:

Hello,

did you try to create a new lgp file and refresh the store procedure?

Yes. I also went through several iterations of removing the stored procedure from the project, refreshing the catalog, and adding it back. The only thing that worked was to add the second procedure.

I'll see if I can reproduce it. Our test package for this feature contains multiple procs hence we never saw this.

I can't reproduce it. Very strange you run into this.

my test package on HR:

CREATE PACKAGE HR.HR_TEST_PACKAGE is TYPE refcursor is ref cursor;
  procedure pr_Test(DeptNo in NUMBER, Emps out refcursor);
 -- procedure pr_DateTest(DateVal in out Date, EmpId in NUMBER);
end;
;

and the implementation:

CREATE OR REPLACE PACKAGE BODY "HR"."HR_TEST_PACKAGE" is
  procedure pr_Test(DeptNo in NUMBER, Emps out refcursor) is
 BEGIN 
 OPEN Emps for select * from Employees WHERE Employees.Department_ID=DeptNo; 
 END pr_Test; 

/* 
procedure pr_DateTest(DateVal in out Date, EmpId in NUMBER) is
BEGIN
  SELECT Employees.Hire_Date INTO DateVal FROM Employees WHERE Employees.Employee_ID = EmpId;
END pr_DateTest; */
end;

When you use these packages on the example db 'HR', do you see the same behavior?

I tested it again just now, commenting out the DUMMY procedure and refreshing the catalog. The parameters have disappeared from RebuildLocalTable().

I wonder if the problem is related to your having output parameters, whereas all of my parameters are input only.

My current package specification looks like this:

PACKAGE "EFAC"."INSTRUCTORADAPTER" IS

  PROCEDURE "REBUILDLOCALTABLE" (
    "ADDRESSTYPE" IN VARCHAR2, 
    "EMAILTYPE" IN VARCHAR2);

--procedure "DUMMY";

END "INSTRUCTORADAPTER";

and the body looks like this:

PACKAGE BODY "EFAC"."INSTRUCTORADAPTER" IS

    --Local record schema
    type LocalRecord is table of Instructor%rowtype;

  PROCEDURE "REBUILDLOCALTABLE" (
    "ADDRESSTYPE" IN VARCHAR2, 
    "EMAILTYPE" IN VARCHAR2) IS

    LocalData LocalRecord;
    AddressTypeParm Address.ADDRESSTYPE%type;
    EmailTypeParm EmailAddress.EMAILTYPE%type;
    
    BEGIN -- executable part starts here
        Utility.DISABLECONSTRAINTS('Instructor');
        
        --Clear local table
        delete from Instructor;
        
        --Retrieve Banner data
        AddressTypeParm := AddressType;
        EmailTypeParm := EmailType;
        
        select *
        bulk collect into LocalData
        from
            (select <fieldlist>
              from <tables with joins>
                                                 <a couple of UNION ALL statements as well>);

        --Insert records into local table
        forall r in LocalData.first .. LocalData.last
            insert into Instructor values LocalData(r);
            
        Utility.ENABLECONSTRAINTS('Instructor');
     END "REBUILDLOCALTABLE";

--procedure "DUMMY" IS
--BEGIN
--NULL;
--END "DUMMY";

END "INSTRUCTORADAPTER";

Strange thing, but it could be the case, although it still is strange,

the query executed to obtain the parameters is this one: SELECT OBJECT_NAME AS PROC_NAME, PACKAGE_NAME, ARGUMENT_NAME, POSITION, DATA_TYPE, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM ALL_ARGUMENTS WHERE OWNER='schema' AND DATA_LEVEL =0 ORDER BY PACKAGE_NAME, OBJECT_NAME, POSITION

so for 'schema' you can read 'SCOTT' for example.

Does this query executed on your schema reveal the parameters of your proc?

I'm currently in the process of downloading the latest 10gR2 db installer, so I can test this in the same environment as you did ( I did test it on 9i with the 10g ODP.NET which succeeded). I hope to have more testresults later today or early tomorrow morning (CET)

Otis wrote:

the query executed to obtain the parameters is this one: SELECT OBJECT_NAME AS PROC_NAME, PACKAGE_NAME, ARGUMENT_NAME, POSITION, DATA_TYPE, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM ALL_ARGUMENTS WHERE OWNER='schema' AND DATA_LEVEL =0 ORDER BY PACKAGE_NAME, OBJECT_NAME, POSITION

so for 'schema' you can read 'SCOTT' for example.

Does this query executed on your schema reveal the parameters of your proc?

I ran this query with the 'DUMMY' procedure commented out and the parameters do appear in the result set.

The two rows returned for this package are:

    PROC_NAME   PACKAGE_NAME    ARGUMENT_NAME   POSITION    DATA_TYPE   IN_OUT  DATA_LENGTH DATA_PRECISION  DATA_SCALE
1   REBUILDLOCALTABLE   INSTRUCTORADAPTER   ADDRESSTYPE 1   VARCHAR2    IN          
2   REBUILDLOCALTABLE   INSTRUCTORADAPTER   EMAILTYPE   2   VARCHAR2    IN          

ScottRoberts wrote:

Otis wrote:

the query executed to obtain the parameters is this one: SELECT OBJECT_NAME AS PROC_NAME, PACKAGE_NAME, ARGUMENT_NAME, POSITION, DATA_TYPE, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM ALL_ARGUMENTS WHERE OWNER='schema' AND DATA_LEVEL =0 ORDER BY PACKAGE_NAME, OBJECT_NAME, POSITION

so for 'schema' you can read 'SCOTT' for example.

Does this query executed on your schema reveal the parameters of your proc?

I ran this query with the 'DUMMY' procedure commented out and the parameters do appear in the result set.

The two rows returned for this package are:

    PROC_NAME   PACKAGE_NAME    ARGUMENT_NAME   POSITION    DATA_TYPE   IN_OUT  DATA_LENGTH DATA_PRECISION  DATA_SCALE
1   REBUILDLOCALTABLE   INSTRUCTORADAPTER   ADDRESSTYPE 1   VARCHAR2    IN          
2   REBUILDLOCALTABLE   INSTRUCTORADAPTER   EMAILTYPE   2   VARCHAR2    IN          

Thanks, I'll dig deeper into this and will see if I can repro it with a proc with 2 varchar2 INPUT params and no other proc in the package.

I can't reproduce it.

Package def (on scott), on oracle 10g R2, same driver version.

CREATE OR REPLACE PACKAGE "SCOTT"."PROCTESTER" AS
    PROCEDURE "PR_INSERTDEPT" (DEPTNO IN NUMBER, DNAME IN VARCHAR2, LOC IN VARCHAR2);
END;

body

CREATE OR REPLACE PACKAGE BODY "SCOTT"."PROCTESTER" AS
    PROCEDURE "PR_INSERTDEPT" (DEPTNO IN NUMBER, DNAME IN VARCHAR2, LOC IN VARCHAR2)
    IS
    BEGIN
        INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC) VALUES (DEPTNO, DNAME, LOC);
    END;
END;

I believe that I have figured out what the issue is. (The package body is irrelevant, so I'm omitting that code here.)

I added your package into my database, changing the name of the table in the insert statement to one of my tables and changing the schema name to my schema:

CREATE OR REPLACE PACKAGE "EFAC"."PROCTESTER" AS
    PROCEDURE "PR_INSERTDEPT" (DEPTNO IN NUMBER, DNAME IN VARCHAR2, LOC IN VARCHAR2);
END;

The parameters showed up in the Designer.

I then removed your initial NUMBER parameter, just to make sure that I was comparing apples to apples (only 2 VARCHAR2 parameters):

CREATE OR REPLACE PACKAGE "EFAC"."PROCTESTER" AS
    PROCEDURE "PR_INSERTDEPT" (DNAME IN VARCHAR2, LOC IN VARCHAR2);
END;

The parameters showed up in the Designer.

I then copied my original package declaration and body, using only a single insert statement in the body of the package:

CREATE OR REPLACE PACKAGE "PROCTESTER" AS
  PROCEDURE "REBUILDLOCALTABLE" (
    ADDRESSTYPE IN VARCHAR2, 
    EMAILTYPE IN VARCHAR2);
END;

The parameters did not show up.

I then changed the name of the procedure to "TESTPROC":

CREATE OR REPLACE PACKAGE "PROCTESTER" AS
  PROCEDURE "REBUILDLOCALTABLE" (
    ADDRESSTYPE IN VARCHAR2, 
    EMAILTYPE IN VARCHAR2);
END;

And the parameters did show up.

And then I had the answer. I am using the same procedure name "REBUILDLOCALTABLES" in several packages. This package is the only one that has parameters for that procedure. All of the others have no parameters. So, it seems that the importer is somehow confusing this procedure with one in another package (although, you'll recall, if I add a second procedure to this package, the parameters did show up).

I hope that I have used the smileys correctly. (I don't use them that often.) Thanks for your quick responses and I want to say that I'm enjoying using the product. It's a lot more fun than the DataSet designer in VS (and faster and more flexible).

I have another question regarding stored procedures, but I will put it in another forum since it's a design question.

Heh

Yes, you ran into something which is supported by oracle but not by LLBLGen Pro: overloading of procs. LLBLGen Pro just supports 1 proc per name, so if you have multiple overloads of a proc, one will be used.

Thanks for the positive feedback, Scott

Sorry, I just realized I should have created a new thread instead

So I have moved my question here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14088

With regards, Helene