Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Dynamic SQL calling stored procedures

    We need to know if it is possible to write a Pro*C program capable of executing PL/SQL stored procedures using dynamic parameters and dynamic procedure names.

    Imagine that program receiving XML messages, which determines the procedure name to execute and its INPUT / OUTPUT parameters.

    Have anybody done something similar? Do you know where to get sample source code?

    thanks in advance.

    Joan.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The short answer is "YES", it is just a Small Matter Of Programming (SMOP).
    I wonder if/why Pro*C is a real hard and fast requirement.
    I doubt that "dynamic procedures" are really required or exist.
    I suspect the PL/SQL procedures will exist & be somewhat fixed, constant and well defined; depending upon the number and variety of XML input formats.
    I suspect that the program accepting the XML input will contain logic to determine the "correct" PL/SQL procedure to be invoked to INSERT the XML into the database and pass into the PL/SQL the variable input data.
    We do similar processing here but use PERL instead of Pro*C.

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    It MUST be C language for many reasons, but mainly because that program will run as a BEA Tuxedo Service, in the middleware layer, so only 'C' or COBOL languges are possible (Java under webloginc is not possible for other reasons). Also I think that parsing XML in 'C' will be faster than in any other language.

    So, could anybody show me any sample code doing that?... Imagine simply that the 'C' prog. receive an XML telling the Procedure name, their input parameters, and the returns datatypes he expects.

    Example XML

    <Input>
    <Operation>CalculeCurrency</Operation>
    <Parameters>
    <account>8849398827392</account>
    <contry>00332</country>
    <Parameters>
    <Input>
    <Output>
    <Parameters>
    <currency>integer</currency>
    </Parameters>
    </Output>

    the need is to build the procedure call dynamically, and generate the XML response.

    tnx.


    Originally posted by anacedent
    The short answer is "YES", it is just a Small Matter Of Programming (SMOP).
    I wonder if/why Pro*C is a real hard and fast requirement.
    I doubt that "dynamic procedures" are really required or exist.
    I suspect the PL/SQL procedures will exist & be somewhat fixed, constant and well defined; depending upon the number and variety of XML input formats.
    I suspect that the program accepting the XML input will contain logic to determine the "correct" PL/SQL procedure to be invoked to INSERT the XML into the database and pass into the PL/SQL the variable input data.
    We do similar processing here but use PERL instead of Pro*C.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the need is to build the procedure call dynamically, and generate the XML response.

    I guess we just view the problem & possible solution from different perspectives. If I had to solve this type of problem, I'd use PL/SQL functions ( not PROCEDURES). There would be one FUNCTION for each type of expected/legal requested operations; such CALCULATE_CURRENCY. Each function would return VARCHAR2 variable; which would be XML formated text. The Pro*C code would parse the incoming XML, decide which function to invoke, provide it with the input arguments, and wait for the results.

    Again, just a Small Matter Of Programming (SMOP).
    HTH & YMMV

    HAND!

Posting Permissions

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