Re: [PLSQL] Passing some underfined parametes to stored proc. Maybe XML ?

Andrey Koubychev wrote:
> Hello guys,
>
> I have the following sutiation:
> I have to write a program that exports LDIF files to Oracle databases. In
> short, LDIF files are simple text files with records of value = pair. I need
> to parse it and update corresponding record in tables. For example, when I
> parse couple record in LDIF file I could get something like:
>
> Type = User
> ID = 2342
> Name = John
>
> Type = Equipment
> Color = black
>
> Now , I need to update corresponding data in database. I want to make my
> program is flexible as possible and not depend from table structure
>
> I want to do the following :
> For each type I will create stored proc. with name Set_Type which will
> accept own different paramters name and types.... But now , my program needs
> to know about parameters - it's quantity, names... And I want to avoid that.
> One of the ugly solutions would be that all stored procs have one parameter
> (string), my exe paste all parameters on one comma separated string and pass
> to stored proc. Stored proc parse it and update fields with new values.
> Deep inside I feel that I need to pass XML , but I'm not really familiar
> with XML capabilities of Oracle.
>
> I would like to hear your advises about the methods I could use.
>
>
> --
> Best regards,
> Andrey Koubychev
>
> sorry for multipost :)
>
>

If you put your procedure(s) in a package, you can overload the procedures to accept different arguments. In the example case you gave in your post, you could have two Set_Type procedures:

Set_Type(inType varchar2, inID number, inName varchar2) Set_Type(inType varchar2, inColor varchar2)

As long as the functions have a different number of arguments or they have different datatypes, Oracle will be able to figure out which to call based on the arguments given in the call. Of course, that means you couldn't have a function defined as follows (would conflict with second procedure above):

Set_Type(inType varchar2, inName varchar2)

It's the same set of rules in as C++ function overloading. Received on Wed Nov 27 2002 - 10:46:20 CST