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.

82 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. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. Really nice to visit your post. Thanks for giving this great experience to us.
    DBA course syllabus | DBA training courses

    ReplyDelete
  17. Ciitnoida provides Core and java training institute in

    noida
    . We have a team of experienced Java professionals who help our students learn Java with the help of Live Base Projects. The object-

    oriented, java training in noida , class-based build

    of Java has made it one of most popular programming languages and the demand of professionals with certification in Advance Java training is at an

    all-time high not just in India but foreign countries too.

    By helping our students understand the fundamentals and Advance concepts of Java, we prepare them for a successful programming career. With over 13

    years of sound experience, we have successfully trained hundreds of students in Noida and have been able to turn ourselves into an institute for best

    Java training in Noida.

    java training institute in noida
    java training in noida
    best java training institute in noida
    java coaching in noida
    java institute in noida

    ReplyDelete
  18. Best Sap Training Center in Noida

    CIIT is the biggest ERP SAP training institute in Noida with high tech infrastructure and lab facilities and the options of opting for multiple courses at Noida Location. CIIT in Noida prepares thousands of aspirants for ERP SAP at reasonable fees that is customized keeping in mind training and course content requirement of each attendee.

    ERP SAP training course involves "Learning by Doing" using state-of-the-art infrastructure for performing hands-on exercises and real-world simulations. This extensive hands-on experience in ERP SAP training ensures that you absorb the knowledge and skills that you will need to apply at work after your placement in an MNC.

    CIIT Noida is one of the best ERP SAP training institute in Noida with 100% placement support. CIIT has well defined course modules and training sessions for students. At CIIT ERP SAP training is conducted during day time classes, weekend classes, evening batch classes and fast track training classes.

    ReplyDelete
  19. Thanks for your informative article, Your post helped me to understand the future and career prospects & Keep on updating your blog with such awesome article.

    ReplyDelete
  20. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.. Believe me I did wrote an post about tutorials for beginners with reference of your blog. 

    java training in omr

    java training in annanagar | java training in chennai

    java training in marathahalli | java training in btm layout

    java training in rajaji nagar | java training in jayanagar

    ReplyDelete
  21. Thank you for this post. Thats all I are able to say. You most absolutely have built this blog website into something speciel. You clearly know what you are working on, youve insured so many corners.thanks
    python training in velachery
    python training institute in chennai

    ReplyDelete
  22. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Devops Training in Chennai
    Devops training in sholinganallur
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram

    ReplyDelete
  23. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies


    ReplyDelete
  24. I believe that your blog will surely help the readers who are really in need of this vital piece of information. Waiting for your updates.
    Selenium Training in Bangalore
    Selenium Training Institutes in Bangalore

    ReplyDelete
  25. Thanks for your efforts in sharing this information in detail. This was very helpful to me. Kindly keep
    continuing the great work.

    linuxhacks
    Article submission sites

    ReplyDelete
  26. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you.
    Keep update more information..


    Selenium training in bangalore
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training
    Selenium interview questions and answers

    ReplyDelete
  27. This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
    microsoft azure training in bangalore
    rpa training in bangalore
    best rpa training in bangalore
    rpa online training

    ReplyDelete
  28. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete
  29. I learned World's Trending Technology from certified experts for free of cost. I Got a job in decent Top MNC Company with handsome 14 LPA salary, I have learned the World's Trending Technology from Python training in pune experts who know advanced concepts which can help to solve any type of Real-time issues in the field of Python. Really worth trying instant approval blog commenting sites

    ReplyDelete
  30. Attend The Data Science Course From ExcelR. Practical Data Science Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Course.
    Data Science Course

    ReplyDelete
  31. I needed to thank you for this good read!! I definitely enjoyed every little bit of it. I have got you bookmarked to check out new stuff you study post…

    ReplyDelete
  32. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing.

    data science course

    ReplyDelete
  33. These are the ways that website designing companies in India are using lately. Trendy Online Solution is one of them and will help you with their years of experience in the field. The team in the company is highly professional and will complete your needs by understanding your requirements. So Contact us Now! thanks for ur effort and work
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

    ReplyDelete
  34. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    data science course in guntur

    ReplyDelete
  35. Great post I must say and thanks for the information. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.

    data science course in guntur

    ReplyDelete
  36. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.

    Oracle Training | Online Course | Certification in chennai | Oracle Training | Online Course | Certification in bangalore | Oracle Training | Online Course | Certification in hyderabad | Oracle Training | Online Course | Certification in pune | Oracle Training | Online Course | Certification in coimbatore

    ReplyDelete
  37. Thanks for sharing a useful information.. we have learnt so much information from your blog.... oracle training in chennai

    ReplyDelete
  38. It is a great post. Keep sharing such kind of useful information. oracle training in chennai

    ReplyDelete
  39. That is a good tip particularly to those fresh to the biosphere. Short but very accurate info… Many thanks for sharing this one. A must read post!

    Data Science Training in Hyderabad

    ReplyDelete
  40. I am looking for and I love to post a comment that "The content of your post is awesome" Great work! data science courses

    ReplyDelete
  41. Very good points you wrote here..Great stuff...I think you've made some truly interesting points.Keep up the good work.
    data science using python and r programming Guwahati

    ReplyDelete
  42. This post is so interactive and informative.keep update more information...
    Java Training in Tambaram
    java course in tambaram

    ReplyDelete