Hello, I've researched here and elsewhere but haven't found an answer for the following. I'd like to get a list of all procedures available to my application's Oracle account (AFAIK they're part of one package), and have tried the following command in sqlplus: SELECT * from user_procedures; However this only returns one row/procedur...
I have a column whose values consist of a prefix and then some value, e.g.: ABC-Car ABC-Dog ABC-Suit CBR-Train In my query I would like to trim off certain prefixes, e.g. if I trimmed off 'ABC-' it would give me the following: Car Dog Suit CBR-Train The only problem is, all characters are valid for the part after the prefix. So it...
Hi, I have a stored procedure that has an OUT parameter, indicating an error code. If the error code is not 0, then I raise an error DECLARE BEGIN foo (err_code); IF (err_code <> 0) THEN raise_application_error(...); END; So far so good, but here's my question. This piece of code (shown above) is executed by sqlplus, which is...
I have to admit that I just, well, screwed the pooch on a production database. When running an update query in SQL Developer, I did not realize that only a portion of the query was highlighted. If you have any experience with SQL Developer, this means that SQL Developer will only execute that sub-section of a query -- in this case, app...
So I have this SQL query, <named-query name="NQ::job_exists"> <query> select 0 from dual where exists (select * from job_queue); </query> </named-query> Which I plan to use like this: Query q = em.createNamedQuery("NQ::job_exists"); List<Integer> results = q.getResultList(); boolean exists = !results.isEmpty(); ...
I was trying to do: String sql = "INSERT INTO CURRENT_WEATHER_US VALUES("+city_code+", "+object.city+","+object.region+","+object.country+","+object.wind_chill+", "+object.wind_direction+", "+object.wind_speed+","+object.humidity+","+object.visibility+", "+object.pressure+","+object.rising+", "+object.sunrise+","+object.sunset+...
Hello everyone, We are building an extranet loan status check website using ASP.NET MVC with a WCF backend. Its a pretty standard design with the MVC site using a WCF service reference to get customer objects. The ervice uses an Oracle backend + http binding, and won't be hosted on the same server as the MVC site (so we can't use tcp b...
I have a table called W_US and i want to create W_UK in oracle with same schema as for W_US. ...
I was wondering if SQL Server has an equivalent to Oracle PL/SQL Package? It is really nice to build your sprocs, functions, etc into a Package. ...
This is a theoretical question, I was wondering if there is a good way of finding out which condition in the WHERE statements matched. Say I have a query like this: SELECT * FROM table WHERE COND1 OR (COND2 AND COND3) OR COND4 Is there any way of knowing which of the conditions made a given row match (or unmatch)? One ...
Hay, My system needs to execute several major SQL`s (on Oracle DB) using the same connection (asynchronous). What`s the best practice for this issue? 1. open single connection and execute every SQL statement on different thread (does it thread safe?) 2. create new connection and “open + close” it for every SQL statement Thanks, Hec ...
Task Retrofit an existing application to use a multi-tenant approach. It shall be possible to create tenants and each user's session should reference exactly one active tenant. Each tenant should only be able to see and update his partition of the database schema. Approach Create an Oracle application context that contains the tenan...
Hi, I currently have 2 schemas, A and B. B has a table, and A executes selects inserts and updates on it. In our sql scripts, we have granted permissions to A so it can complete its tasks. grant select on B.thetable to A etc,etc Now, table 'thetable' is dropped and another table is renamed to B at least once a day. rename someot...
I created a trigger as below: CREATE OR REPLACE TRIGGER trigger_test AFTER INSERT ON trigger_1 FOR EACH ROW DECLARE t_identifier VARCHAR2(10); t_name VARCHAR2(20); BEGIN t_identifier := (:NEW.IDENTIFIER); t_name := (:NEW.NAME); INSERT INTO trigger_2(IDENTIFIER,NAME)VALUES(t_identifier,t_name); COMMIT; END; I am trying to insert a row ...
I would like to ask if anybody has any knows how to use the OPTION keyword. I have encountered this on an old C source code that I have been reading. OPTION SELECT ROWID FROM TABLE_1 WHERE PRODUCT_CODE = ANY(SELECT PRODUCT_CODE FROM PRODUCT_TABLE WHERE PRODUCT_GROUP='value a') FOR UPDATE NOWAIT; SELECT ROWID FROM TABLE_2 WHERE PRODUCT_...
Hi Folk, I'm trying to see if exists something to create a sequence with partition logic. I need a sequence number that depend on other primary key ex: id_person sequence id 1 | 1 1 | 2 2 | 1 3 | 1 1 | 3 so the sequence must depend on the id_person partition. Is there something like this on or...
Having used SQL Server 2008 Change Tracking feature, I'm looking for a similar solution for Oracle. I'm mainly wondering if such a feature already exists in Oracle or if the standard solution is to use triggers and 'tracking' tables. I've googled, but I think 'change tracking' aren't the right keywords? ...
I am currently embroiled in a battle of wills regarding the installation and setup of oracle 10.2.0. I am following this guide. Having received errors at every step of the way I've finally got to the very end of the guide only to receive this error message after running catproc.sql: PL/SQL procedure successfully completed. Package bo...
Hi, I've been reading about implicitCachingEnabled and MaxStatements with the oracle jdbc driver. I've tried adding implicitCachingEnabled="true" into the server.xml for the datasource definition but it makes no difference. I've also noted other posts admittedly from a long time ago, where people have failed to get this setting to wor...
I have this SQL statement: SELECT ABX.ABX_APO_NUMBER, COUNT(A1.PROCESS_MODE) AS NUM_PLANNING, COUNT(A2.PROCESS_MODE) AS NUM_SETUP, COUNT(A3.PROCESS_MODE) AS NUM_OUTPUT FROM ABX, USER_INSTANCE U, ACTIVE_PROCESS A1, ACTIVE_PROCESS A2, ACTIVE_PROCESS A3 WHERE U.ABX_APO_NUMBER (+) = ABX.ABX_APO_NUMBER AND A...