Which one to use : Pro*C or PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Which one to use : Pro*C or PL/SQL

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    149
    Hi,
    I was asked to develop a program that would process and extract data to a text file from Oracle. I'm more familiar with
    stored procedures and I would like to have all processing done within stored procedure and the results would be written to a file using a Pro*C program. A programmer at my workplace said that it's better to using Pro*c to do everything since its much faster compare to stored procedure. Is this true? Please comment on this. THanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it is true, pro*c is much faster and powerful
    however pl/sql in 8i has improved a lot so it now can handle array processing like pro*c as well, but I guess C is always faster

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    PRO*C is a client-side tool accessing the database
    with embedded SQL/embedded PL/SQL

    Developing database-oriented programs
    is directly in PL/SQL much easier.
    The deploymant to different OS is much easier,
    The PL/SQL-Language is very easy to handle and
    has no POINTERS and has a very easy and secure DATA-Definition.

    Fast, what is fast?
    When you compare a PRO*C-Program using SQL'S and then a PL/SQL-PRocedure the procedure could even be faster due to less client-server-communication.

    Developing a Program with PL/SQL should be 2-3 times faster as with PRO*C mixed.

    Orca

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    The pity is PL/SQL is interpreted not compiled which make it slower no matter what

    We had this Datawarehouse extracter made by PRO*C then there was this guy who made a PL/SQL procedure to do the same. PRO*C took 45 minutes, PL/SQL took 2.5 hours

  5. #5
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    I haven't use Oracle9i but have read that it does pl/sql compilation into native C code. Does this greatly improved pl/sql performance against using Pro*C? (Haven't used Pro*C either)

  6. #6
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Ok, loading Data in Datawarehouse not depends
    on the Language it depends on the Possibilty
    to insert data not row, but blockwise
    It's just an effctive way in bindung data
    and reducing overhead.
    In PL/SQL exists Bulk-operations too,
    have you used them?

    Orca

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    plz read my first post, I said pl sql supports now array processing == bulk operations

  8. #8
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    OK!
    But i think ist not correct to say C is always faster.
    when i would have a program without db-access you would be totally correct.

    Moving Businesslogic from the Client to the server is
    ALWAYS faster.
    In case of DWH i am your opinion, it's easier to do that in PRO*C. But the best results i still had wit embedded PL/SQL when the insert-logic is complex.
    Have a nice day
    Orca



  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    One Example
    we had a batch-program written in PRO*C
    accessing and calculating Ticket-data in a DWH.
    The batch lastst 40H!
    Then we moved the whole thing to PL/SQL
    -> 3H!!
    Orca

  10. #10
    Join Date
    Sep 2016
    Posts
    1
    Hi ,
    Can you please share code with me. We have similar problem to move large set oracle to DWH as csv files. We used PL/SQL procedure , but we faced the issue like temp table space exceed.

    If possible please share the code for data movement.That will be more helpfull to me

    Mah@rac

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width