This discussion is archived
14 Replies Latest reply: Dec 9, 2012 8:53 PM by BillyVerreynne RSS

can i use create function for MSSql scalar and table valude function.

947771 Newbie
Currently Being Moderated
Hi,

1) Can i use create function for MSSql scalar and table valued function?
2) How many type of user defined function are there in oracle 11g express?
3) And can i reture any "type" form user defined function?


yourse sincerely
  • 1. Re: can i use create function for MSSql scalar and table valude function.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:
    Hi,

    1) Can i use create function for MSSql scalar and table valued function?
    Yes, but not in the fashion that T-SQL does.
    2) How many type of user defined function are there in oracle 11g express?
    None really. As it comes without user content (except for a demo schema). You are the user. You add the content.

    How many user defined functions can you create? How much space do you have for the data dictionary?
    3) And can i reture any "type" form user defined function?
    Yes. There is an abstract class (advance data type) called AnyData. Using it would be an exception though.
  • 2. Re: can i use create function for MSSql scalar and table valude function.
    947771 Newbie
    Currently Being Moderated
    Hi,

    i have found some code , to get set of rows form a function to be used in joins.
    i want to clarify that, is there any other good way of getting set of rows to be used in joins (with respect to performance)?
    because i will be using this through out the database, so i was little concerned.
    as the code is reusable so i do not want to make stored procedure.


    CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
    (
    TEST_ID NUMBER(9),
    TEST_DESC VARCHAR(30)
    )
    /

    Once object type is created, we will create named table type of above object type.

    CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
    /

    Now let us write function code to return the result set.

    CREATE OR REPLACE FUNCTION FN_GET_ROWS
    RETURN TEST_TABTYPE
    AS
    V_Test_Tabtype Test_TabType;
    BEGIN
    SELECT TEST_OBJ_TYPE(Test_Id, Test_Desc)
    BULK COLLECT INTO V_Test_TabType
    FROM
    Test
    ;
    RETURN V_Test_TabType;

    EXCEPTION
    WHEN OTHERS THEN
    v_Test_TabType.DELETE;
    RETURN v_Test_TabType;
    END;
    /



    SQL> select p.*
    2 from emp e
    3 join table( FN_GET_ROWS() ) p on (p.Test_Id = e.testid);

    yours sincerely
  • 3. Re: can i use create function for MSSql scalar and table valude function.
    BluShadow Guru Moderator
    Currently Being Moderated
    944768 wrote:
    Hi,

    i have found some code , to get set of rows form a function to be used in joins.
    i want to clarify that, is there any other good way of getting set of rows to be used in joins (with respect to performance)?
    because i will be using this through out the database, so i was little concerned.
    as the code is reusable so i do not want to make stored procedure.
    Is this a good way... erm... not really.
    It's reading data from the database and storing it in expensive PGA memory.
    It then prevents any queries that are using that data from using indexes etc. on that data, because now it's stored in a basic collection type in memory instead.
    It also contains a horrendous WHEN OTHERS exception, meaning that if there is an error reading the data for some reason, then it silently returns no data as if there was no problem, except for there being no data.
    The code shown no valid reason for doing this, when you could just as easily join to the original data in the queries when you need it.
    If the query itself were more complex you could simply create a view and use that view in your queries throughout your code. At least then the SQL from that view would be incorporated into the SQL's using it, and the query execution plans determined correctly with indexes etc. being used correctly.

    Certainly the code is a nice little demonstration of using collections, object types, and using collection in SQL queries... but there's no justification for any of it.
  • 4. Re: can i use create function for MSSql scalar and table valude function.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:

    i have found some code , to get set of rows form a function to be used in joins.
    Horrible code. Perfect example of how NOT to write Oracle code that is robust, performant and scalable.
    i want to clarify that, is there any other good way of getting set of rows to be used in joins (with respect to performance)?
    Standard SQL. If that SQL cursor's output needs to be consumed by an external client, then pass a reference handle to the client (called a ref cursor).
  • 5. Re: can i use create function for MSSql scalar and table valude function.
    947771 Newbie
    Currently Being Moderated
    Hi,

    1)i wanted to use function because , the function has some " if else" and computation.

    2) is the sentence having pga memory is saying that, if two connections call the same fucntion using indexs will block some thing?

    yours sincerely
  • 6. Re: can i use create function for MSSql scalar and table valude function.
    947771 Newbie
    Currently Being Moderated
    Hi,

    Standard SQL. If that SQL cursor's output needs to be consumed by an external client, then pass a reference handle to the client (called a ref cursor).

    can i have small ex of above, even small algorithm will do.

    yous sincerely
  • 7. Re: can i use create function for MSSql scalar and table valude function.
    Paul Horth Expert
    Currently Being Moderated
    944768 wrote:
    Hi,

    1)i wanted to use function because , the function has some " if else" and computation.
    What's that got to do with it? Plain SQL can do If/else in the form of CASE statements and can do computation as well.

    >
    2) is the sentence having pga memory is saying that, if two connections call the same fucntion using indexs will block some thing?
    No, it's saying that memory blocks aren't indexed.
  • 8. Re: can i use create function for MSSql scalar and table valude function.
    BluShadow Guru Moderator
    Currently Being Moderated
    944768 wrote:
    Hi,

    1)i wanted to use function because , the function has some " if else" and computation.
    SQL can include CASE statements and has WHERE clauses for restricting data and conditionally manipulating data.
    2) is the sentence having pga memory is saying that, if two connections call the same fucntion using indexs will block some thing?
    No, not blocking, but PGA memory is 'expensive' as it's a server resource that uses physical (or viritual) server memory. The data is already on the disks and SQL is very efficient at manipulating data based on the information it has stored about it. Loading the data from disks into memory to manipulate it, just so you can write it back to the disks is slower and consumes memory. If several processes do similar things, then the server will start to run out of memory, resulting in switching to virtual memory (if applicable), which would mean the data is being read from the disks to be written to virual memory on the disks, to be processed in virtual memory (lots more disk I/O) and then to be written back to the disks. Overall, that's a lot of disk I/O and that's a potential bottleneck for queries.
    As a general rule of thumb, don't load data into memory in bulk to process it, when SQL is perfectly capable of processing it as a streaming throughput without consuming large volumes of memory.
  • 9. Re: can i use create function for MSSql scalar and table valude function.
    AlbertoFaenza Expert
    Currently Being Moderated
    944768 wrote:
    Hi,

    Standard SQL. If that SQL cursor's output needs to be consumed by an external client, then pass a reference handle to the client (called a ref cursor).

    can i have small ex of above, even small algorithm will do.
    Google a bit searching "oracle ref cursor example" and you can find plenty of examples.

    Regards.
    Al
  • 10. Re: can i use create function for MSSql scalar and table valude function.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:
    Standard SQL. If that SQL cursor's output needs to be consumed by an external client, then pass a reference handle to the client (called a ref cursor).
    can i have small ex of above, even small algorithm will do.
    Simplistic PL/SQL template:
    create or replace procedure FunkyFoo( c in out sys_refcursor, param1 integer, ... ) is
    begin
      ..conditional processing..
      case  ..
        when some-condition then
          open c for select * from emp where deptno = param1;
       when some-other-condition then
          open c for select * from emp where deptno = param1 and date_joined >= param4;
        ..
      end case;
    end;
    On the client, you create the following anonymous PL/SQL block:
    begin
      FunkyFoo( 
        c => :myCursorVar,
        param1 => 1234,
       ..etc..
      );
    end;
    You now parse this anonymous block as a cursor. You bind you variables (called host variables in Oracle to it). You execute the call. Your host variable myCursorVar now contains a reference cursor (pointer to the cursor created by the FunkyFoo call).

    You process myCursorVar as you would any other cursor. Fetch from it. Display and process the data/rows fetched. When no more data, close the ref cursor.

    And that is it in a nutshell.

    Documentation at http://tahiti.oracle.com
  • 11. Re: can i use create function for MSSql scalar and table valude function.
    947771 Newbie
    Currently Being Moderated
    thank u,
    the answer fits in few cases only,

    one case is ,
    if my function returns only one value then can i use oracle "create function" with return type as predefined types like integer,varchar2 etc ?

    with respect to performance.

    yours sincerely

    Edited by: 944768 on Dec 6, 2012 8:34 AM

    Edited by: 944768 on Dec 6, 2012 8:34 AM
  • 12. Re: can i use create function for MSSql scalar and table valude function.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:

    the answer fits in few cases only,
    The answer demonstrates the basic concept of how clients need to use SQL cursors created by using PL/SQL as an abstraction layer - and this concepts fits ALL such cases where client wants cursor from Oracle via PL/SQL.
    one case is ,
    if my function returns only one value then can i use oracle "create function" with return type as predefined types like integer,varchar2 etc ?
    with respect to performance.
    With respect to what performance issue? And why now suddenly change the question to wanting to use a function to return number or string data types? How does this now relate to wanting PL/SQL to return a cursor?

    A function can return scalar and non-scalar values. A function can return atomic values (like a single number, or a non-scalar list of numbers). A function can return a complex data structure consisting of scalar and non-scalar values.

    However, in PL/SQL a function should NOT be used to read rows from the database, cache the rows in a PL/SQL server memory (PGA), and then return that to the client.

    It does not perform. It does not scale. It is also bloody dangerous to the general health of the server as such code is playing silly buggers with very expensive server memory.

    A PL/SQL function can also be used as a pipe line, returning data not directly via expensive PGA, but via the SQL engine instead and using its cursor interface (allowing data output to be "gradual" and not return all the data with a single mass copy when popping the call stack). THIS IS HOWEVER AN EXCEPTION. There is very seldom the need to craft PL/SQL pipeline table functions - and is often abused, and poorly demonstrated with nonsensical code samples here.
  • 13. Re: can i use create function for MSSql scalar and table valude function.
    947771 Newbie
    Currently Being Moderated
    i started the topic with functions only because i wanted to use oracle functions, but after reading i found there are many ways to implement it
    .so i decided to put it on forum that which way i should use so that it can perform well with speed and maintenance.

    my one situation is answered about returning scalar values.
    Q1)That means even if i return predefined types like integer, varchar2 then also PGA is used ?

    i have one more situation in this case it returns table
    this query gets latest product's report no with its scanned copy and details like id of scanned copy of report , scanned report can be updated latter also.

    in select part of query i have used * but in reality i have used cases to get the latest report of any product first from head office and if it does not exits there
    then from zonal office and then from retail offices.
    function it self has some latest logic based on date and approvals given to the report(i found it can be solved using group by but scanned copy can be updated latter also and in any manner that made forced me to use this function and same function can be used at many other places.
    with this, this function is not only works for three level it has many more levels.

    Select * From product jj
    join udf_latestreport(department,performance) headoffice
    on headoffice.pid=jj.pid
    join udf_latestreport(department,performance) zonaloffice
    on zonaloffice.pid=jj.pid
    join udf_latestreport(department,performance) retaloffices
    on retaloffices.pid=jj.pid

    Q2) So please suggest me solution in oracle.

    yours sincerely

    Edited by: 944768 on Dec 7, 2012 8:35 PM

    Edited by: 944768 on Dec 9, 2012 1:30 AM
  • 14. Re: can i use create function for MSSql scalar and table valude function.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:

    Q1)That means even if i return predefined types like integer, varchar2 then also PGA is used ?
    The data type does not determine where the variable is stored. A string (called a varchar2 in Oracle) can be stored in stack space, heap space, on disk, in a memory mapped file, in a shared memory, in an atom table, etc.

    It is the who and what is defining and using that string, that determines where and how it is stored.

    The Oracle sever supports 2 languages in PL/SQL. The PL (Programming Logic) language is a procedural/declarative language. It is NOT SQL. SQL is integrated with it. The PL/SQL engine uses private process memory (PGA). So PL/SQL variables exist in the PGA (but there are exceptions such as LOBs).

    Q2) So please suggest me solution in oracle.
    Sounds to me you are looking at how to implement a T-SQL style function as an Oracle function, and once implemented, do joins on the function.

    Do not use PL/SQL in SQL in place of a SQL select. It is not T-SQL.

    One cannot use PL/SQL to create functions along the style of T-SQL, where the function executes a SQL using some conditional logic, and then return as if the function was a native SQL select.

    T-SQL is an extension to the SQL language - making it a hybrid and very impure language implementation. PL is based on ADA - part of the Pascal family of languages. The E-SQL (embedded SQL) approach used in languages like C/C++, Cobol and Ada, has been transparently done in PL/SQL. You can write and mix PL code and variables with SQL code. And the PL/SQL engine figures out how to make the call from the PL/SQL engine to the SQL engine.

    But PL/SQL is not "part" of the SQL language and does not "extend" the SQL language in a T-SQL fashion.

    So you need to check your SQL-Server preconcepts in at the door, as they are not only irrelevant in Oracle, they are WRONG in Oracle.

    The correct way in Oracle, in a nutshell - Use the SQL language to do data processing. Use PL/SQL to manage conditional process flow and the handling of errors.

Legend

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