I want to select all rows of a table followed by a random number between 1 to 9: select t.*, (select dbms_random.value(1,9) num from dual) as RandomNumber from myTable t But the random number is the same from row to row, only different from each run of the query. How do I make the number different from row to row in the same execution...
I have a query select * from myTable ...and I want to wrap this query inside a stored procedure, and have the store procedure output the results of this query. How do I do it? In ms-sql, i can store my query as a string to a string variable. And then do "Execute (variable)". Why no such thing in Oracle? ...
Given: Oracle 10.2g is the database I have a table called emp. emp has a VARCHAR2 column called SECRET. SECRET might contain a plaintext string, or it might contain an encrypted string, but I can distinguish one from the other A function called DECRYPT already exists that, given the encrypted string, will return an unencrypted string. H...
In postgres you can switch on query logging, resulting in a file containing all queries issued by any client. Is there a similar possibility in Oracle XE? How do I switch it on and where do I find the resulting file? ...
I have a foreign key constraint in my table, I want to add ON DELETE CASCADE to it. I have tried this: alter table child_table_name modify constraint fk_name foreign key (child_column_name) references parent_table_name (parent_column_name) on delete cascade; Doesn't work. EDIT: Foreign key already exists, there are data in fo...
How can I check for a substring in a string in Oracle without using LIKE? Let's say I want to select all users from a table that have the letter "z" in their last name: SELECT * FROM users WHERE last_name LIKE "%z%"; That would work, but I don't want to use LIKE. Is there some other function I could use? ...
Is there a way that one can test if a row has been locked for update in Oracle? As an example, suppose the following query, performed by one user: select * from SOME_TABLE where THE_ID = 1000 for update; With another user I want to check if the row with THE_ID = 1000 is locked. If I try an update or something the second user gets blo...
I'm looking for any information source describes how a database like oracle manages memory ...
Hi, I have found this answer useful: Accent and case insensitive COLLATE equivalent in Oracle, but my question is regarding LIKE searching with a version 9 Oracle db. I have tried a query like this: SELECT column_name FROM table_name WHERE NLSSORT(column_name, 'NLS_SORT = Latin_AI') LIKE NLSSORT('%somethingInDB%', 'NLS_SORT = Latin_AI...
I have a partitioned table like so: create table demo ( ID NUMBER(22) not null, TS TIMESTAMP not null, KEY VARCHAR2(5) not null, ...lots more columns... ) The partition is on the TS column (one partition per year). Since I search a lot via the timestamp, I created a combined index: create index demo.x1 on demo (ts, k...
Hey all, I've recently started a new position as a developer and I'm having a bit of trouble with PL/SQL. I've used MS SQL for a number of years but I'm finding PL/SQL a bit trickier. One of the things I used to do when writing functions and stored procedures in MS SQL was to put reoccuring result sets into a table variable so I would...
I have a Java web application connecting to an Oracle database running on another machine (not sure if this is relevant or not). I am using DBCP for connection pooling. The web application is running in JBoss 4.2.2 and we are defining our datasource as a bean in Spring. We are using Hibernate for ORM. We are getting errors occasional...
Following on from my last question (http://stackoverflow.com/questions/1573326/table-variables-in-oracle-pl-sql)... Once you have values in an array/table, how do you get them back out again? Preferably using a select statement or something of the like? Here's what I've got so far: declare type array is table of number index by b...
We had the following query in sql server: SELECT b.columnB, b.displayed_name AS displayName, c.type_cd, c.type_desc, b.month_desc AS month FROM table1 a, table2 b, table3 c WHERE b.region_code *= a.columnA AND c.program_type_cd *= a.program_type_cd which, in oracle, got converted to: SELECT b.columnB, ...
I am on Oracle 10g. I have a table that contains all the files stored in the system during the past year. I want to make statistical monthly deposits, grouping them by file size. eg 0-1m 1m-10m 10m-100m 100m + So my results would look like : Month, 0-1m, 1m-10m, 10m-100m, 100mplus 2009-03, 999, 999, 999, 999 I want to use Oracle's ...
I've spent hours searching the web for an answer to this question... Here's what I currently have: select * from order_header oh where tran_date = sysdate-1 Thanks in advance. ...
Hi, To start with here is the bigger picture of the task I'm trying to do. I need to create a xml file from the results of the particular SQL request and store it in a file on the client computer. For that I have a SQL script that does the DBMS_XMLGen with xslt, which I'm going to run from a command line with sqlplus and pipe the outpu...
Hi, I am trying to connect oracle 10g database through n hibernate, can anyone help me the preliminary steps to be done to establish the connection, I've connected sq l server 2000 easily and its working fine, now i try to connect oracle but i am getting error "cannot open connection". please help me in this regard. Thanks in advance. ...
What ORM frameworks for .NET and Oracle Do You Like Best? ...
Why doesn't it work? SELECT a.* FROM dual a JOIN (SELECT * FROM dual WHERE 1=1) b ON (1=1); I get "ORA-00900: invalid SQL statement". Is there a way to use WHERE clause inside the subquery? Edit: Version 9.2 SELECT * FROM v$version Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production The following executes j...