How to determine the version of oc4j being used?
I have an oc4j installation bereft of any release notes or version documentation. In the absence of such documents, how do I know for sure, which version of oc4j I am using? ...
oracle
How to debug ORA-01775: looping chain of synonyms?
I'm familiar with the issue behind ORA-01775: looping chain of synonyms, but is there any trick to debugging it, or do I just have to "create or replace" my way out of it? Is there a way to query the schema or whatever to find out what the current definition of a public synonym is? Even more awesome would be a graphical tool, but a...
Oracle - ODBC connection using MS Access error (ORA-12154)
I am trying to use MS access to connect to a Oracle database. I keep on getting the following error message: ORA-12154: TSN- could not resolve the connect identifier secified The Oracle Drivers OracleClient10g can verify that the database server exists. I have a section in my tsnnames.ora file that looks like this: UBASEP10G = (DESC...
Does Oracle allow an ORDER BY within an IN clause?
Oracle is giving me an error (ORA-00907: missing right parenthesis) when I run this query: select * from reason_for_appointment where reason_for_appointment_id in ( select reason_for_appointment_id from appointment_reason where appointment_id = 11 order by appointment_reason_id ) However, when I run just the subq...
SQL Query Help - task ordering, grouping and status + date conditions problem
Hopefully I can do the problem justice, because it was too difficult to summarise it in the title! (suggestions are welcome in the comments) Right, so here's my table: Tasks task_id (number) job_id (number) to_do_by_date (date) task_name (varchar / text) status (number) completed_date (date) ...
What could cause an ORA-00936 - Missing Expression with the following sql?
We're seeing the error message ORA-00936 Missing Expression for the following SQL: Note that this is just a cut-down version of a much bigger SQL so rewriting it to a inner join or similar is not really in the scope of this: This is the SQL that fails: select (select count(*) from gt_roster where ROS_ROSTERPLAN_ID = RPL_ID) from gt_ro...
How do I Suppress "PL/SQL procedure successfully completed" message in sqlplus?
Is there a way that you can have SERVEROUTPUT set to ON in sqlplus but somehow repress the message "PL/SQL procedure successfully completed" that is automatically generated upon completed execution of a plsql procedure? ...
schema dump on legacy oracle database using db:schema:dump using rake
Does anyone know of any specific DSL implementations used to import legacy Oracle database schemas. I have tried to just run db:schema:dump on my existing db I want to port to a new ruby app. However, the rake dies about halfway through with out any error. It kinda just locks up. I started looking for the best way to tackle this and foun...
How do I log/trace Oracle stored procedure calls with parameter values?
We're looking for a way to log any call to stored procedures in Oracle, and see what parameter values were used for the call. We're using Oracle 10.2.0.1 We can log SQL statements and see the bound variables, but when we track stored procedures we see bind variables B1, B2, etc. but no values. We'd like to see the same kind of informa...
How do you rate Oracle Portals as a platform?
Hi there, A client has asked whether or not I can provide some support for his intranet which runs on Oracle Application Server Portal. Not having used this technology before I thought I'd ask if anyone else has and what they thought of developing for it. I'm a c#/ASP.NET developer so I note with apprehension that there's no mention o...
An example for using DBMS_AQ.POST and explanation of its utility
The DBMS_AQ package has a method called POST. The documentation says, This procedure posts to a list of anonymous subscriptions, allowing all clients who are registered for the subscriptions to get notifications of persistent messages. This feature is not supported with buffered messages. and This call provides a be...
What to look for when downgrading from Oracle 10g to 9i?
I having to downgrade my Oracle instance from 10g (10.1.0.2.0) to 9i (9.2.x.x.x). Not having planned on ever doing this, I didn't document 10g dependencies. What are some of the dependencies on 10g that I will have to address? Is there any type of query that I could perform to detect dependencies? Of course I'm hoping for a magic bull...
alter index across schemas
I am synchronizing tables using a stored PL/SQL procedure as part of a web application - when they hit a button it does the sync. The PL/SQL is executing as user A, but needs to disable indexes and sync a couple of tables in schema B. I can't get user A to 'alter index B.indexName unusable' despite granting it 'alter any index', and in...
Select from a nested query in Oracle 9i
In MS SQL 2005 or T-SQL, you can do something like: SELECT T.NAME, T.DATE FROM (SELECT * FROM MyTable WHERE ....) AS T I failed to try the similar SQL on Oracle 9i DB. In MS SQL, the nested SQL is treated as a temporary/dynamic view created on fly and destroyed afterward. How can I do the similar thing in Oracle? I really don't wan...
Is there anyway to declare a TYPE without a Property in Oracle 10gR2
I want to create a base object that has only methods. The object would be QUEUABLE_OBJECT_TYPE and it will have an ENQUEUE method(s). The Payload of these messages (properties) would be added by subtyping this object. I get an error that makes it sound like you cannot. PLS-00589: no attributes found in object type "QUEUABLE_OBJECT_T...
How do I let my DBA pause and resume a stored procedure that is updating every row in a large table?
I have a table of about a million rows and I need to update every row in the table with the result of a lengthy calculation (the calculation gets a potentially different result for each row). Because it is time consuming, the DBA must be able to control execution. This particular calculation needs to be run once a year (it does a year-en...
Is there a way to do full text search of all oracle packages and procedures?
I would like to search through all of my procedures packages and functions for a certain phrase. Since it is possible to retrieve the code for compiled procedures using toad I assume that the full text is stored in some data dictionary table. Does anyone know where that would be? Thanks a lot ...
Oracle listener state blocked
I have a webpp which works fine when under a light load. However, when we run a lot of threads each with their own database connection, then we start getting the error ORA-12519: TNS:no appropriate service handler found After looking online I found that running lsnrctl services was a good diagnostic step, so I did that. The result f...
ORA-01775: looping chain of synonyms but there are no synonyms
Can't figure out why I'm getting 'SQL Statement ignored' and 'ORA-01775: looping chain of synonyms' on line 52 of this stored procedure. Got any ideas? Thanks, Tony CREATE OR REPLACE PACKAGE PURGE_LOG_BY_EVENT_DAYS AS TYPE dual_cursorType IS REF CURSOR RETURN dual%ROWTYPE; PROCEDURE log_master_by_event_days (event_id NUMBER, purge_r...
Why does Char(1) change to Char(3) when copying over an Oracle DBLINK?
I have 2 databases, and I want to transport an existing table containing a CHAR column from database A to database B. Database A is Oracle 9i, has encoding WE8ISO8859P1, and contains a table "foo" with at least 1 column of type CHAR(1 char). I can not change the table on database A because it is part of a third party setup. Database B...