In my database at several places developers have used dynamic sql instead of static. And they are saying reason for this is to improve the performance. Can someone tell me can if dynamic sql can really increase the performance in stored procedure or plsql block? Which will execute faster and why ? 1. begin execute immediate ...
As a follow-up to the question "Get resultset from oracle stored procedure", is there a way to show the results of a stored procedure that returns a REFCURSOR in a grid (instead of the plain text Script Output window) within SQL Developer? EDIT: The answer helped, but I'm still having a problem displaying the result set in the "View Val...
I am trying to perform SET operations in Oracle across remote databases. I am using the MINUS operator. My query looks something like this. SELECT NAME FROM localdb MINUS SELECT NAME from remotedb@dblink This is throwing up a ORA-12704 error. I understand this warrants some kind of conversion or a NLS Setting. What should I try nex...
Dear all, I'm not the first to have these issues, and will list some reference posts below, but am still looking for a proper solution. I need to call a stored procedure (Oracle 10g database) from a C# web service. The web server has an Oracle 9i client installed and I am using Microsofts System.Data.OracleClient. The procedure takes ...
I need to rebuild the following object in an Oracle database...can somebody tell me how I would go about doing this? Thanks in advance. OBJECT ID = 576 OBJECT_NAME OBJECT_TYPE SMON_SCN_TIME_TIM_IDX INDEX ...
Hi guys, I'll try and explain what I'm trying to achieve quickly, since I have no idea how to explain it otherwise! We have a table here that shows all employment history for all employees, I want the "Start_Date" of the current post ("Current_Flag" = 'Y'). As well as that, I want the "End_Date" of the post before that (was going to fi...
Hi! I would like to get my guery execution time from Oracle DB. I dont want the time when Oracle finish to print results. In MySQL it is easy to get execution time from shell, but in SQL Plus there are no such thinks. Thanks for any help! ...
I have a problem similar to the stackoverflow question posed in the link below except that I need to exclude certain fields from the comparison but still include it in the result set. I'm penning the problem as locally symmetric difference. For example Table A and B has columns X,Y,Z and I want to compare only Y,Z for differences but...
Hi everybody, I am doing some tests here and write some SQLs. I try to write some reusable code and therefore I want to declare some variables at the beginning and reuse them in the script, like this: DEFINE stupidvar = 'stupidvarcontent'; SELECT stupiddata FROM stupidtable WHERE stupidcolumn = &stupidvar; I tried so far: Use a DEC...
As everybody working with Oracle knows, it an empty Varchar2 will result in a NULL value when put into a Varchar2 column. I (and a coworker as well) thought we had read about a parameter that could be set in the database to change that behavior and actually differentiate between null values and empty Strings. Is there such a parameter ...
We have a table with 6 million records, and then we have a SQL which need around 7 minutes to query the result. I think the SQL cannot be optimized any more. The query time causes our weblogic to throw the max stuck thread exception. Is there any recommendation for me to handle this problem ? Following is the query, but it's hard for ...
In an organization that has two applications each with its own Oracle database instance, what are the disadvantages of consolidating the two databases into one database with two schemas? Backups and replicating the database are bigger and slower, probably. What else? Some background: The two databases are the "gold source" for their ...
What are the advantages and disadvantages of having a single instance compared to multiple instances when multiple databases are intended to be created? ...
Using Oracle, is there any way I can execute a query to determine what relationship a specific table has with any other tables in my database? I'm fiddling with the all_constraints table as of now. ...
I want to put some same value in some fields where data is not present, for that do I need to query each field and see if there is data present or not and accordingly put the data, or there is some other work arround. Like Name Age City N 22 J K K 23 L Here I want to put data on those fi...
Hi! I need to check, if procedure parameters are null, and if not, to use it in WHERE clause. For example: sqlquery := 'SELECT * FROM table WHERE a_col = a AND'; IF b IS NOT NULL THEN sqlquery := sqlquery || ' b_col = :b'; END IF; IF c IS NOT NULL THEN sqlquery := sqlquery || ' c_col = :c'; END IF; And so on. Then I need to use O...
I have a web application in Spring that has a functional requirement for generating a CSV/Excel spreadsheet from a result set coming from a large Oracle database. The expected rows are in the 300,000 - 1,000,000 range. Time to process is not as large of an issue as keeping the application stable -- and right now, very large result sets c...
Is it possible to do SET OPERATIONS using more than one column in the SELECT clause? For example: SELECT NAME, AGE FROM tableA MINUS SELECT NAME, AGE from tableB ...
I was hoping one of you Oracle experts would be able to give me a hand with this. I have the following SQL Server script, but I need to rewrite it for Oracle: USE mydb GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE trigger mydb . [CONNECTERTRIGGER] on mydb . [DtreeNotify] FOR INSERT AS BEGIN IF @@ROWCOUNT=0 RETURN SET I...
Here's a scenario with a product we purchased. THe product allows us to create custom fields, but these fields are stored as ROWS in a CUSTOM table. I want to write a query that will connect to more than one custom field and fetch a single row. Let me give you an example. 1) PERSON TABLE (ID int, NAME varchar2(30)); 2) CUSTOMFIELDS TA...