Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Calling a Stored Procedure from ProC that takes a "Table" input argument

    Hello folks, new to this forum and running into an issue maybe someone can provide advice on as I haven't done any ProC work in about 6 years.

    Basically, I have a stored procedure which I'm trying to call from ProC (inside a C++ program).

    One of the input parameters is a custom defined "table" type. I've been searching online, but I can't find any documentation or an example on how to create the host variable for a custom type from ProC. Here's more or less what I have:

    Code:
    create or replace TYPE MyType as table of varchar2(2);
    
    CREATE OR REPLACE PROCEDURE MyProcedure(
    arg1 IN varchar2,
    arg2 IN MyType, -- nullable
    arg3 OUT number,
    arg4 OUT varchar2)
    And my "first attempt" at calling it from ProC:

    Code:
    EXEC SQL BEGIN DECLARE SECTION;
    const char *Arg1 = "HelloWorld";
    char Arg2[10][2]; 
    long int Arg3 = 0;     
    char Arg4[2];                                                                             
    EXEC SQL END DECLARE SECTION;    
     
    EXEC SQL CALL MyProcedure (:Arg1, :Arg2, :Arg3, :Arg4);
    Obviously I'd populate the Arg2 with something useful before calling the SP, I'm just trying to get a basic prototype working.

    Now, when I compile this, I get some errors:

    Code:
    PCC-W-02344, Host variable array size mismatch. Using minimum: 1
    PLS-S-00306, wrong number or types of arguments in call to 'MYPROCEDURE'
    PLS-S-00000, Statement ignored
    PCC-S-02346, PL/SQL found semantic errors
    But since I don't know if I'm doing it correctly in the first place, I don't even know if those errors mean anything.

    So if anyone could point me to an example on how to do this, or address my specific errors, that would be great. I also need to know how to make Arg2 be "Null" in some cases.

    Thanks.

    Additional info:

    Oracle Version: Oracle Database 10g 10.2.0.4.0
    OS Version: Red Hat release 4 (Nahant Update 7)

  2. #2
    Join Date
    Feb 2012
    Posts
    2

    Smile

    I believe I have solved this. The issue was one of scoping. The type and procedure seem to need to be bound together by a package.

    Here is a working example:

    Code:
    CREATE OR REPLACE PACKAGE MyPackage AS
      TYPE MyType is table of real index by binary_integer;
    
      PROCEDURE MyProcedure(
        Arg1 OUT REAL,
        Arg2 IN MyType
      );
    end MyPackage;
    /
    
    CREATE OR REPLACE PACKAGE BODY MyPackage AS
      PROCEDURE MyProcedure(
        Arg1 OUT REAL,
        Arg2 IN MyType
      )
      IS
      BEGIN
        Arg1 := Arg2(1);
      end;
    end MyPackage;
    /
    And from ProC

    Code:
        EXEC SQL BEGIN DECLARE SECTION;
        float   Arg1;
        float Arg2[1];
        EXEC SQL END DECLARE SECTION;
    
        Arg2[0] = 5.0;
    
        EXEC SQL EXECUTE
            begin
                MyPackage.MyProcedure (:Arg1, :Arg2);
            end;
        END-EXEC;
    Hopefully this benefits someone else who runs into the same issue!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •