I have connection pooling implemented in spring using Oracle Data Source. Currently we are facing an issue where connections are becoming invalid after a period of time. (May be Oracle is dropping those idle connections after a while). Here are my questions: Can Oracle database be configured to drop idle connections automatically after...
I have to study and modify a big and poorly documented codebase written in pl/sql. I use Oracle SQL Developer to navigate through it, but it become tedious because there is not a "go to declaration" nor "find usages" option so I have to go manually to the referred package to find the function or procedure I want to understand, or grep an...
Hi. I wanted to drop/kill connections made to specific schema of a database. Could you please sugest a prudent way to do this? Cheers ...
Hi All, Im Using toad for Oracle to run a query which is taking much too long to run, sometimes over 15 minutes. The query is pulling memos which are left to be approved by managers. The query is not bringing back alot of rows. Typically when it is run it will return about 30 or 40 rows. The query needs to access a few tables for its...
Hi All, I am writing the below queries in oracle: DBMS_OUTPUT.....'Ashish' Select col1 into val1 from tab_1 DBMS_OUTPUT.....'Ubale' when I run this procedure I get the output as "Ashish" only why? also what will be the value of v_val1 variable Note: the table does not contain any records ...
The following Oracle SQL code generates the error "ORA-02287: sequence number not allowed here": INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA'); SELECT * FROM Customer where CustomerID=Customer_Seq.currval; The error occurs on the second line (SELECT statement). I don't really understand the problem, becaus...
I am trying to concatenate some fields to return a single string for each row from an oracle table. This is in 10g. Here is my query: SELECT t.value || '|' || t.label || '|' t.label_abbrv || '||' "mylist" FROM list_value t WHERE t.value BETWEEN 195001 AND 195300; I'm getting the "FROM keyword not found where expected" error. Th...
So many times I just need a quick connection to an Oracle DB, where SQLPLUS handles the job. I imagine when people start using Oracle, the first thing they are told to do is to install Toad or SQLDeveloper. Even so, sometimes you don't want to wait for those tools to load, if you are performing some simple queries. I have a script tha...
Here's the background: Version: Oracle 8i (Don't hate me for being out of date. We are upgrading!) SQL> describe idcpdata Name Null? Type ----------------------------------------- -------- --------------------------- ID NOT NULL NUMBER(9) DAY ...
I am receiving the following error: ORA-12406: unauthorized SQL statement for policy I am executing a stored procedure that executes the following two Oracle Label Security stored procedures: SA_COMPONENTS.CREATE_GROUP SA_LABEL_ADMIN.CREATE_LABEL In the same transaction, but not the same procedure, I am trying to insert into a tabl...
Hi, Is there a way to initialize an object from a database query that returns a datatable? What I mean is, I have an oracle stored procedure that returns a refcursor for data from a table. In my code, I have an object for that table (not using an ORM). Is there an easy way to initialize the object from the data in the datatable, or d...
I've googled this error but came up with no solution, this happened when I installed oracle database 11g, I tried to reach https://localhost:1158/em and this error pops up in firefox Secure Connection Failed An error occurred during a connection to localhost:1158. SSL received a record that exceeded the maximum permissible length. (...
...and how should those permissions be granted. I work in a large IT dept with 70+ applications, some in SQL server and most in oracle. Each system has a prod, QA and Dev instance. We (I'm a developer) have readonly access to prod/qa, which I'm fine with. In SQL server development instances devs are given db_owner, which works totall...
I just installed the Oracle Instant Client for Mac OSX with the SQLPlus extension and have the following problem. I can connect to a database instance and schema and I can do queries but when I try the "describe command" it just hangs. For example: SQL> SELECT COUNT(*) FROM APPROVABLETAB; COUNT(*) ---------- 8 SQL> desc APPROVABLE...
I have some one-many normalized data that looks like this. a | x a | y a | z b | i b | j b | k What query will return the data such that the "many" side is represented as a CSV string? a | x,y,z b | i,j,k ...
I have one table with the following Column. BoxNumber Status 580 4 581 4 582 4 583 4 584 2 585 2 586 4 587 4 588 4 589 4 590 2 591 2 I...
Hi all, I hope I'm able to explain the problem that is puzzeling me. I have the following hierarchical data set (this is just subset of 34K records) PARENT_ID CHILD_ID EXAM TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J TUDA12982 TUDA12983 N TUDA12983 TUDA1532...
Hi community, After having used an application for over 10 years, and been constantly limited by its lack of extensibility, we have decided to rewrite it fully from scratch. Because the new architecture differs from the old application, the database is also different. Here comes the problem: Is there any industrial process for migrating...
I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I need to get the absolute parent from a given child. Say, I have child 4, it has to give me parent: 1 I already looked to CONNECT BY , but I can't find the solution. ...
Do you need to create an index for fields of group by fields in an Oracle database? For example: select * from some_table where field_one is not null and field_two = ? group by field_three, field_four, field_five I was testing the indexes I created for the above and the only relevant index for this query is an index created for fie...