This discussion is archived
6 Replies Latest reply: Dec 5, 2012 9:24 PM by 801904 RSS

select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?

801904 Newbie
Currently Being Moderated
Hello

I am porting application from Microsoft SQL Server 2008 and C#.NET to Oracle 11g R2 and Java.

Microsoft SQL Server allows the following SQL:

select * from my_CSharp_function_name('filename');

the above SQL will present a table of rows and columns back to SQL Server client or back to stored procedure such as this:

a,b,c
d,e,f
g,h,i

etc

Can this be done using Java and PL/SQL ?

I realize there is Oracle External table feature but I don't think this feature will solve my problem because the Java code performs some preprocessing and transformations on the input file and the input files do not have fixed known structure than I can describe in Oracle External table.

thanks in advance
  • 1. Re: select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?
    rp0428 Guru
    Currently Being Moderated
    >
    I am porting application from Microsoft SQL Server 2008 and C#.NET to Oracle 11g R2 and Java.

    Microsoft SQL Server allows the following SQL:

    select * from my_CSharp_function_name('filename');

    the above SQL will present a table of rows and columns back to SQL Server client or back to stored procedure such as this:

    a,b,c
    d,e,f
    g,h,i

    etc

    Can this be done using Java and PL/SQL ?
    >
    No - there is no Oracle equivalent. Also, even using external tables Oracle can only access files that are located on the server, not the client.
    >
    the Java code performs some preprocessing and transformations on the input file and the input files do not have fixed known structure than I can describe in Oracle External table.
    >
    Then how do you plan to insert the data into a table that is required to have a 'fixed known structure'? All you could do is create a table with one column of VARCHAR2(4000) and put the entire record in it.

    If you provide a realistic example of what you actually need to do instead of just making one up it will be easier for someone to help you.
  • 2. Re: select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?
    801904 Newbie
    Currently Being Moderated
    Thanks.

    all input files are located on the server, not client.

    re my scenario, since you asked.

    The max number of columns of each input text file is limited to max of 100 (it can be easily changed up or down when function is created). Each file column is a limited to max varchar(200). This can also easily be changed, when schema is create for the function.

    If the input file only has (for example) 20 columns then the remaining 80 columns of the resultset are 'synthesized' with values "unused" by the C# code. The application ignores all "unused" columns returned.

    This is all done by C# method, runs inside MS-SQL as a stored function returning resultset with 100 columns and unlimited number of rows.
    The C# method also splits the columns (using comma or pipe delimiter or ", and ", delimiters) and it performs some other input format processing on the input file data and finally presents the "cleansed" input file as fixed a set of 100 columns in a resultset - to stored procedure. This is all achieved at SQL by just: select * from c_sharp_function('input-file-name');

    The calling stored procedure or client app performs data load of the returned resultset and does further processing since it just sees the input file as a read only table with a fixed set of columns. Everything is read in as a varchar at first and later converted to the correct data types.

    The rest is done using dynamic SQL.

    If this can not be done with Java stored inside Oracle 11g R2 then I need to transfer this post to the Oracle database forum.

    Please advise.
    Thanks.
  • 3. Re: select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?
    rp0428 Guru
    Currently Being Moderated
    >
    The max number of columns of each input text file is limited to max of 100 (it can be easily changed up or down when function is created). Each file column is a limited to max varchar(200). This can also easily be changed, when schema is create for the function.

    If the input file only has (for example) 20 columns then the remaining 80 columns of the resultset are 'synthesized' with values "unused" by the C# code. The application ignores all "unused" columns returned.

    This is all done by C# method, runs inside MS-SQL as a stored function returning resultset with 100 columns and unlimited number of rows.
    The C# method also splits the columns (using comma or pipe delimiter or ", and ", delimiters) and it performs some other input format processing on the input file data and finally presents the "cleansed" input file as fixed a set of 100 columns in a resultset - to stored procedure. This is all achieved at SQL by just: select * from c_sharp_function('input-file-name');

    The calling stored procedure or client app performs data load of the returned resultset and does further processing since it just sees the input file as a read only table with a fixed set of columns. Everything is read in as a varchar at first and later converted to the correct data types.
    >
    If that is all the initial step is why not just use Java to load the data into a standard Oracle table defined with 100 columns of VARCHAR2(200)?

    The Java can run outside the DB. If you need to run it inside the DB then create a Java stored procedure that does what your c# function does now.

    Java can just read the file a record at a time in a loop and you can use the String.split method to split the record using a pipe delimiter and then insert the data into a standard Oracle table.

    Then your other processes can do with it what they want.
    >
    The appropriate forum would be the SQL and PL/SQL forum.
    SQL and PL/SQL
    >
    The rest is done using dynamic SQL.
    >
    That is generally a bad idea since that means using PL/SQL. You should ALWAYS use SQL whenever possible since it is simpler and always faster than PL/SQL. Oracle doesn't need temporary tables the way that sql server does. In Oracle the use of a temporary table is the exception and they are only needed for special use cases.

    If you do decide to post in the other forum the appropriate forum would be the SQL and PL/SQL forum and you should provide your four digit Oracle version (result of SELECT * FROM V$VERSION).
    SQL and PL/SQL

    Since the question may involve both Java and SQL I suggest you leave both threads open but cross-link them; that is, in each thread post the link to the other thread so people (like me that are active in both forums) can follow the entire set of replies.
  • 4. Re: select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?
    801904 Newbie
    Currently Being Moderated
    thanks.

    Since its a porting exercise I wanted to leave as little architecture changed as possible.

    But now (based on your answers) it looks like it will be more like a "re-design"/"re-write" instead of "port" because of large functional feature differences between MS-SQL & C# and Oracle & Java.

    Much appreciated.
  • 5. Re: select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?
    rp0428 Guru
    Currently Being Moderated
    >
    But now (based on your answers) it looks like it will be more like a "re-design"/"re-write" instead of "port" because of large functional feature differences between MS-SQL & C# and Oracle & Java.
    >
    Not trying to dampen your spirit or enthusiaism but you should expect more of the same type of 'negative?' feedback in the other forum when you mention sql server, temporary tables and/or dynamic sql. Temp tables and dynamic sql are best used for special cases where they are really needed.

    If you search the sql and pl/sql forum for 'sql server' or global temporary table' you will find hundreds of threads by people coming to Oracle form sql server and wanting to try to do things the 'sql server' way. That is a fundamental mistake. So don't be surprised if the first reaction in to the forum expresses the same sentiment.

    Unless this is just a one time throwaway you really do need to redesign the architecture to take advantage of Oracle's functionality.

    The focus should be on the problem you need to solve rather than on the solution you might desire to implement.
  • 6. Re: select * from table ( myJavaMethod('filename') ); in Oracle 11g R2 ?
    801904 Newbie
    Currently Being Moderated
    thanks, yes, i have read the topics (and books too), so i am aware that a "port" is not likely to work because it's for a sizable 2TB+ data mart with >4000 users so it must run very well.
    It runs well enough on MS-SQL 2008 right now so we can't have the new Oracle solution run worse, right? 8^)

    Its 100% true that SQL Server is more in-tune with temporary tables than Oracle.

    Its not good, nor bad, its just different.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points