Tagged Questions
-1
votes
1answer
49 views
populate table header and data in another table column [closed]
I have 2 tables: Departments and Employees, where Department_Id is primary key in the departments table and a foreign key in Employees table.
Departments Table:
Department_Id Department_Name
10 ...
0
votes
1answer
66 views
What are the criteria for a sql staying in the V$SQLAREA?
I needed to pick up details from a SQL result but 5 minutes after running the SQL it was no longer under v$sql and V$sqlarea. What are the criteria for Oracle to keep the query in those views?
0
votes
1answer
231 views
Massive update to Clob fields oracle 10g
I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL?
For example, in table t1 there is a clob field called VBase that needs to have the ...
1
vote
1answer
132 views
Find who will change a record in oracle DB
Someone is updating a record in a custom Oracle table, is there a way to find who is updating the record and or at least the table?
4
votes
1answer
671 views
Oracle subquery column aliasing syntax?
I have something like:
SELECT a, b
INTO v_a, v_b
FROM (
SELECT x, y
FROM whatever
UNION
SELECT z, w
FROM something
) AS subquery (a, b)
Which of course doesn't work in Oracle ...
0
votes
1answer
1k views
Returning Row Count in a Stored Procedure in Oracle
I'm mostly SQL Server person, so forgive me if this is too close to SQL Server, but in a nutshell using Oracle 10 I'm trying to build a procedure something like this:
PROCEDURE SP_INFO_EX_S(
...
3
votes
2answers
2k views
Using Same CASE WHEN Conditions For Multiple Query Columns
Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once?
See the example below.
SELECT
CASE ...
1
vote
2answers
139 views
Join on clustered tables shows greater cost than non-clustered tables?
I have created a cluster and have two tables emp and sep on the cluster.
When I do the simple query...
explain plan select * from emp_cluster join dep_cluster using (dno)
the cost is 26.
I ...
3
votes
2answers
2k views
How do I find an object that doesn't seem to exist in the objects table?
I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
0
votes
3answers
382 views
Oracle returns different results when executing a sql with bind variable or with a fix value
The following two statements (strongly simplyfied) returning a different result:
with a fix value for rownmun:
select * from table
where rownum <= 12;
with bind variable for rownum:
select * ...
0
votes
1answer
48 views
Oracle: calling udf that modifies the data. How to do it right?
Say I have a function :
create or replace
function test_function return integer
as PRAGMA AUTONOMOUS_TRANSACTION;
t_test integer :=1;
begin
update test1 set id = id+1 ; // table test1 has, for ...
1
vote
2answers
374 views
Is Using Execute Immediate Bad Practice in Oracle?
So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data.
As the sequence ...
7
votes
5answers
1k views
Oracle sort varchar2 column with special characters last
How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
1
vote
1answer
224 views
Using a sequence created in the same script in Oracle
SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective.
I've got a script I've written which:
Checks ALL_SEQUENCES ...
