Monday, September 27, 2010

Dynamic typing in PL/SQL

Introduction

PL/SQL is a statically typed language. This means that datatypes must be declared and checked at compile time. There are also occasions when you really need the capabilities of dynamic typing and for those occasions, the Any types were introduced in PL/SQL (back then in 9i). These dynamic datatypes enable you to write programs that manipulate data when you don't know the type of that data until runtime. You determine the type of the value at runtime through introspection (using gettype function, as you will see in example).

You cannot manipulate the internal structure of Any types, you must use procedures and functions for that.

We will use following family members of Any:
AnyData (can hold a single value of any type, whatever it's built-in scalar datatype or user-defined object type).
AnyType (can hold a description of a type -- you will see).

In following example I create three user-defined types that are representing some kind of transport mean. The subsequent PL/SQL code then uses SYS.AnyType to define a heterogeneous array of transports.

CREATE OR REPLACE TYPE airplane_o AS OBJECT(
        engine_type VARCHAR2(35),
        lift NUMBER                                       
)
/
CREATE OR REPLACE TYPE car_o AS OBJECT(
        engine_power NUMBER,                             
        color VARCHAR2(35)       
)  
/
CREATE OR REPLACE TYPE train_o AS OBJECT (
       engine_type VARCHAR2(35),
       speed NUMBER                
)
/

SET SERVEROUTPUT ON;

DECLARE    
    TYPE transports_t IS VARRAY(6) OF SYS.AnyData;
    transports transports_t;
    airplane airplane_o;
    car car_o;
    train train_o;    
    ret_val NUMBER;
BEGIN
    
    transports := transports_t(
                    AnyData.ConvertObject(
                        airplane_o('turboprop', 2300)), 
                    AnyData.ConvertObject(
                        airplane_o('jet', 3500)),                              
                    AnyData.ConvertObject(
                        car_o(55, 'red')),
                    AnyData.ConvertObject(
                        train_o('electric', 80)),
                    AnyData.ConvertObject(
                        train_o('steam', 45)),
                    AnyData.ConvertObject(
                        airplane_o('ramjet', 9000))                                    
                              );
                
    
    FOR i IN 1..transports.COUNT LOOP        
        IF transports(i).GetTypeName = 'HR.AIRPLANE_O' THEN            
            ret_val := transports(i).GetObject(airplane);
            --ret_val can be success or no_data...I did not check this.
            DBMS_OUTPUT.put_line('Airplane: ' || 'engine type: ' || 
                        airplane.engine_type || ', lift: ' || airplane.lift || 'lbs');
        ELSIF transports(i).GetTypeName = 'HR.CAR_O' THEN
            ret_val := transports(i).GetObject(car);
            DBMS_OUTPUT.put_line('Car: ' || 'engine power: ' || 
                        car.engine_power || 'KW, color: ' || car.color);
        ELSIF transports(i).GetTypeName = 'HR.TRAIN_O' THEN
            ret_val := transports(i).GetObject(train);    
            DBMS_OUTPUT.put_line('Train: ' || 'engine type: ' || 
                        train.engine_type || ', speed: ' || train.speed || 'KMh');
        END IF;
        
    END LOOP;    
    
END;

Execution of this program in Toad generates following output:


Now I will comment important points in this code.
28 - 41: Here are heterogeneous transports stored in a VARRAY. airplane_o, car_o, train_o are constructors of an object, and AnyData.ConvertObject cast this objects into instance of AnyData.
45, 50, 54: Here we introspect current object (in the loop) and get its type.
46, 51, 55: Retrieve the specific object. We are ignoring return code.

48, 52, 56: Once I had the object in a variable, I can write its properties in DBMS_OUTPUT.

31 comments:

  1. The information you posted here is useful to make my career better keep updates..If anyone want to become an oracle certified professional reach FITA Oracle Training Institutes in Chennai, which offers Best Oracle Training in Chennai with years of experienced professionals.

    ReplyDelete
  2. I get a lot of great information from this blog. Recently I did oracle certification course at a leading academy. If you are looking for best Oracle Training in Chennai visit FITA IT training and placement academy which offer best SQL Training in Chennai.

    ReplyDelete
  3. Big Data Training Chennai

    I get a lot of great information from this blog. Thank you for your sharing this informative blog. Just now I have completed hadoop certification course at a leading academy. If you are looking for best Hadoop Training in Chennai visit FITA IT training and placement academy which offer Big Data Training in Chennai.

    ReplyDelete
  4. Cloud Computing Training Chennai

    The information you posted here is useful to make my career better keep updates...If anyone want to get Cloud Computing Training in Chennai, Please visit FITA academy located at Chennai Velachery which offer best Cloud Computing Course in Chennai.

    ReplyDelete
  5. I get a lot of great information from this blog. Thank you for your sharing this informative blog. Just now I have completed hadoop certification course at a leading academy. If you are interested to learn Hadoop Training Chennai visit FITA IT training and placement academy which offer Big Data Training Chennai.

    ReplyDelete
  6. Your posts is really helpful for me.Thanks for your wonderful post. I am very happy to read your post.
    Mysql Training in chennai | Mysql Training chennai | Mysql course in chennai | Mysql course chennai

    ReplyDelete
  7. In coming years, cloud computing is going to rule the world. The cloud based CRM tool provider like Salesforce have massive demand in the market. Thus talking salesforce training from reputed Salesforce training institutes in Chennai will ensure bright career prospects for aspiring professionals.

    ReplyDelete
  8. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    cloud computing training in chennai | cloud computing training chennai | cloud computing course in chennai | cloud computing course chennai

    ReplyDelete
  9. Oracle is a relational database management system produced by oracle corporation. Nowadays most of the multinational companies used this oracle database for storing and managing their data's and programs. So learning Best Oracle Training in Chennai is one of the best idea to make a bright career.

    ReplyDelete
  10. Excellent information. HTML5 is a markup language used for designing responsive website and it is also used for structuring and presenting the website content.
    HTML5 Training | PHP Course in Chennai




    ReplyDelete
  11. Nice information. Android is one of the most popular mobile operating system developed by Google. Learning Android Training Chennai is useful to make a wonderful future in mobile application development field.

    ReplyDelete
  12. Nice information. Salesforce is a most powerful CRM tool used for manage all customers including potential customers. Salesforce Training in Chennai offering this course with experienced professionals.

    ReplyDelete
  13. Really nice post. Unix is a multiuser and multi tasking operating system at the same time. Unix Training Chennai offering real time Unix course at reasonable cost.

    ReplyDelete
  14. Really nice post. SEO is one of the digital marketing techniques used for improve the website ranking in search engine result page. To know more details please call 9003623340.
    Regards..
    SEO Training in Chennai

    ReplyDelete
  15. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  16. Now a days cloud based technologies are getting popular like wild fire. So as the training programs related to these technologies. Thanks for providing an useful information.

    Hadoop Training Chennai
    Salesforce Training in Chennai

    ReplyDelete
  17. Latest Govt Bank Railway Jobs 2016

    Hey Dear, Great Post. Extremely well written. It helped me. SO, I would like to thank you for this....................

    ReplyDelete
  18. Latest Govt Bank Jobs Notification 2016



    Nice content many thanks! We think your posts are excellent as well as hope there will be more in future/......

    ReplyDelete
  19. Nice Article! Mostly I have gathered knowledge from the blogger, because its provides more information over the books & here I can get more experienced skills from the professional, thanks for taking your to discussing this topic.
    Regards,
    cognos Training in Chennai|Cognos Course in Chennai|Cognos Institute in Chennai

    ReplyDelete
  20. Nice Article! Mostly I have gathered knowledge from the blogger, because its provides more information over the books & here I can get more experienced skills from the professional, thanks for taking your to discussing this topic.
    Regards,
    Oracle DBA Training in Chennai|Oracle Training|Oracle Training Institute in Chennai

    ReplyDelete
  21. I have read this content it is very nice with unique content and keep updating us.
    Salesforce Training in Chennai | salesforce course in Chennai | FITA Velachery | FITA Training

    ReplyDelete
  22. I want to do algorithm that have combination between loop,selection and temporary value.. This assignment very hard to do.. I hope I can give the answer to me.. .. That all. . R Programming Training | DataStage Training | SQL Training | SAS Training | Android Training | SharePoint Training

    ReplyDelete
  23. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharng this information,this is useful to me...
    Android training in chennai
    Ios training in chennai

    ReplyDelete

  24. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..please sharing like this information......
    Web Design Development Company
    Mobile App Development Company

    ReplyDelete
  25. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...

    Android App Development Company

    ReplyDelete
  26. We share that's CCNA related question It is useful to us my interview. Thanks for that it's terrific post.
    Java Training in Chennai
    Java Training in Chennai with Placement

    ReplyDelete
  27. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

    ReplyDelete
  28. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  29. it is really amazing...thanks for sharing....provide more useful information...
    Mobile app development company

    ReplyDelete
  30. I am expecting more interesting topics from you. And this was nice content and definitely it will be useful for many people.
    Fitness SMS
    Salon SMS
    Investor Relation SMS

    ReplyDelete