Sitemap

Amicuk Programming Answers

Best approach for performing DMLs using stored procedures

-0001-11-30   Views:0

Advertisement

Hi, I have a really general question and would like to hear your say about this. I want my application to manipulate or read data using stored procedures (or packages in that manner) and not directly using queries against the DB. Let's say I have a t

Hi,
I have a really general question and would like to hear your say about this.
I want my application to manipulate or read data using stored procedures (or packages in that manner) and not directly using queries against the DB.
Let's say I have a table with many columns:
create table test (pkid number(10),col1 varchar2(30), col2 number(10), col3 date,...);For such a DML procedure, is it best to do something like
procedure do_update(i_pkid IN number,i_col1 IN varchar2, i_col2 IN number, i_col3 IN date,...) as
begin
   update test
   set col1=i_col1,
        col2=i_col2,
        col3=i_col3...
   where pkid=i_pkid;
   commit;
end;Or do a selective update, meaning update only a certain column every time, given only 1 column actually changes? (and how to do that - separate procedures for each column? [columns can be nulls])
Also, is it better to work with test.col1%type instead of specifying the data type in the procedure?
And one last question - If I have a table with 100 columns and I don't want to create a procedure with 100 parameters - the best approach would be to use a record?
I just need to be set on the way I start implementing things in order to do it well from the start.
Many thanks.
Edited by: Pyrocks on Nov 17, 2010 1:58 PM

The replay answer
Advertisement
Pyrocks wrote:
One last clarification (although it may be more related to c/c++ developers - maybe one of you will know):
We are working with C++ and VB against a SQLServer and my part is to translate all the existing procedures to Oracle in order to migrate the application to work with Oracle DB.
The existing procedures use an IN parameter for each column in the table and I would really like to use rowtype like you mentioned.
Since I'm not a c/c++/vb developer - is there an easy way of working with such types, or even User-Defined Types, from c/c++/vb (as in passing a rowtype record from c++ to a SP ?)
I'm not looking for the actual way - just want to know how hard it would be and how much code needs to be changed in order to be able to convince the developers that this is the RIGHT way to work.
PS. none of our developers have experience with ORACLE so they wouldn't know the answer...Not actually an Oracle server-side (SQL language or PL/SQL language) question - but a client one. And it has been a long time since I wrote a fat client using C/C++ or Delphi.
The OCI (<i>Oracle Call Interface</i>) supports advance (user defined) SQL data types. Has since Oracle 8i. So in that respect, yes the client can support custom SQL data types.
How well it does depends entirely on that client language's features wrt OCI integration. For example, Delphi 4 was release around Oracle 8i and supported custom SQL types. I would expect that most languages today (like Java and C#) will provide support for it.
As for usiong +%ROWTYPE+ - this is a PL/SQL clause as far as I know. Unsure whether it is supported by the OCI. What could support it is a pre-compiler like Pro*C. These enable you to mix pseudo SQL source code with client language source code. The pre-compilation step then replaces the pseudo SQL code with native client language calls to the OCI. The code is then compiled by that client language's compiler. Pre-compilers can pull all kinds of interesting "tricks" with their pseudo SQL code support.
The best would be to consult the applicable client language's manuals that describe the interface it supports (via OCI) to Oracle.

Go to See the other 8 answers

Best approach for performing DMLs using stored procedures

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have a really general question and would like to hear your say about this. I want my application to manipulate or read data using stored procedures (or packages in that manner) and not directly using queries against the DB. Let's say I have a t[More]

Insert performance inside a stored procedure

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have an insert statement that inserts approx 400,000 records. The insert executes in 7 seconds if I run it from my SQL command. But when the same insert is executed as part of stored procedure, it takes ~30 mins. This is not the case with all l[More]

DML in stored procedure

Category:DefaultRelease time:2015-10-11Views:130

Greetings: If I simply want run a DML statement containing a variable, do I need to use the DBMS_SQL package or is there an easier way. For example: PROCEDURE test_procedure IS data_table_name VARCHAR2(20) := 'Table1'; BEGIN DELETE FROM data_table_na[More]

Performance of a stored procedure

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, 10.2.0.1 I have a procedure that takes quite an amount of time to finish its job. CREATE OR REPLACE PROCEDURE Cpmstxtquery_arr AS   f1   utl_file.file_type;   col  VARCHAR2(2000) := 'col'; BEGIN   f1 := utl_file.Fopen('CPMS','paymaster.txt','[More]

Performance of Stored procedures against Prepared Statements

Category:DefaultRelease time:-0001-11-30Views:130

What will be the exact difference in the performance between implementing stored procedures and prepared statements ?Short answer: it depends. There will probobaly be very little difference in performance difference for a sipmle insert / update etc.[More]

Bulk Insert Through Stored Procedure performance issue

Category:DefaultRelease time:-0001-11-30Views:130

Hello, i am new to oracle. i am writing a stored procedure through which i want to insert 1 billion record in a table. but it takes days to insert it . please tell me how can i improve performance of my stored procedure. because same stored procedure[More]

Comparing performance of 2 stored procs

Category:DefaultRelease time:-0001-11-30Views:130

What would be the best way of comparing the performance of 2 stored procedures (both have different approaches implemented) on oracle 9i ?You can try to compare execution time and resource usage using Tom Kyte runstats tool:http://asktom.oracle.com/~[More]

What happens to the report if the underlying stored procedure to execute the report take atleast 3 hrs to run

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a report which is calling a stored procedure.. Stored procedure exceutes 4-5 stored procedure and then returns the count each procedure it ran using union all statement... The stored procedure takes around 3-4 hrs to run because it is look[More]

Reg Stored Procedures

Category:DefaultRelease time:-0001-11-30Views:130

Hi Guys, I have a requirement. My requirement is user has two prompts. Once he gives values to these prompts, these values must be passed as parameters to stored procedure. This stored procedure will populate a temporary table. The report have to get[More]

Flags needs to set to run stored procedure fast

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I need to understand what all flags that we need to set run stored procedure fast. e.g. set no count on command is one option, please redirect me to other command so that I can use those for each stored procedure.That is the only general option,[More]

Editing Stored Procedures (SQL Server?)

Category:DefaultRelease time:-0001-11-30Views:130

This may seem like a dumb question but I just finished my project for our Oracle data warehouse and now I need to move on to SQL Server to upgrade those procedures... does SQL Developer not support the editing of SPs for SQL Server? Are the SPs for S[More]

Hot
Hello. I am using iTunes 10.7 on a 64-bit Windows 7 laptop. Since Wednesday iTunes has been crashing whenever I try to enter the iTunes Store. I get the message: "iTunes has stopped working: A problem caused the program to stop working correctly. Win [More]
I'm migrating from Snow Leopard Server to Mountain Lion Server. The article "OS X Server: Upgrade and migration" (http://support.apple.com/kb/HT5381) says "Make sure that any DNS or DHCP servers on which your server depends remain running d [More]
OK, so how long do I have to wait to get an email from Apple with this alleged link I keep reading about to upgrade the LP content? I had previously purchased three LPs from iTunes (Muse: The Resistance, Pearl Jam: Backspacer, are two LPs I bought in [More]
The video screen (and its controls) doesn't display. All the other parts of the screen display correctly.Yep, looks that way to me, too. There's (right this second) '''147''' folks on this thread: ->https://support.mozilla.com/en-US/questions/789534 [More]
Hi SAP Experts, We have a requirement to change the exchange rate type in Versions. In fact, exchange rate type P has been configured in Version 0. This version is being used in Costing Variant PPC1. Price updations are being made with this costing v [More]
Last night I was able to backup.  I updated to the newest version of iTunes last night, and now the backup option is no longer under library.  Any idea what may have happened, and where I go now to backup to a disc?Apple removed it in iTunes 10.4. Us [More]
We are analyzing a bug in our application which seems to be caused by some kind of jpa entity class cache in weblogic (or some other feature causing indirectly the same thing). The bug can't be reproduced every time, but it is happening very often af [More]
I have all my photos in PSE 5.0 windows. I bought a mac and PSE 6.0. how do I import the photos with the tags as keywords into the new version of the software on the mac?You can't convert from the Organizer to Bridge. Bridge is only a browser, you do [More]
I had some compatability issues with my macbook and the 2wire modem I had so I bought an airport extreme. I have a pc in the living room as well. I have the airport extreme set up and the pc is wired to the airport and the ethernet cable from the 2wi [More]
I get the error: "Cannot use this in a static context", but why?? public class This {     public int add(int a){         a = a+a;         return a;     public static void main(String[] args) {         this.add(2); }You simply need an instance to [More]