At work we are using Oracle (ick) for the database of a client (this is a migration from a previous company's project, so the database was set in stone, unfortunately). This has not caused any major headaches (save from the usual Oracle vs. MSSQL stuff), until now: We're at a point where five seperate developers are developing five seper...
Hi, I need to execute something like: select [very big SQL] where phone_number in(:SQL2) Is it possible to use bind variable for SQL2? I want to save the execution plan of the major SQL. Thanks. ...
I'm tuning SQL queries on an Oracle database. I want to ensure that all cached items are cleared before running each query in order to prevent misleading performance results. I clear out the shared pool (to get rid of cached SQL/explain plans) and buffer cache (to get rid of cached data) by running the following commands: alter system f...
Has anyone had any success getting MDBs to work with Oracle AQ? Using oracleaq.rar I was able to receive some messages from Oracle AQ, but it wasn't reliable and the authors urge not use it. ...
In all_objects theres a a column called SUBOBJECT_NAME, and the docs say that this is: Name of the subobject (for example, partition) If you do the following query: select * from all_objects where owner = 'MDSYS' and object_name = 'SDO_TGL_OBJECT_ARRAY' You find that MDSYS.SDO_TGL_OBJECT_ARRAY has a subobject called $VNS...
I am catching errors from a bulk insert operation like this: begin --bulk insert forall i in v_data.first .. v_data.last save exceptions insert into my_filter_table values v_data (i); commit; exception -- catch and print the saved-up DML errors. when X_DML_ERRORS then declare v_iteration...
Are there any good scripts that I could run against my Oracle database to test for SQL defects or maybe common performance issues? Edit: Everything in an Oracle database can be queried. From the PL/SQL packages, indexes and sql running stats. The performance books say look in this place and it will show some absolute values that need th...
I'm working with a legacy Java app that's pulling data from Oracle. One of the queries appears to no longer work: select {hier.*} from ecadmin.dept_hier_cache hier connect by prior parent_deptid = deptid start with deptid = '1234'; When I remove the '{}' brackets from around hier.*, everything works as normal. Now, as far as I can tel...
I'm trying to get a percentage of the itemid that are available in a certain area. Using my query, I get an error ORA-00937: not a single-group group function All the details: I have these two tables: ALLITEMS --------------- ItemId | Areas --------------- 1 | EAST 2 | EAST 3 | SOUTH 4 | WEST CURRENTITEMS ---...
I have this table: ALLITEMS --------------- ItemId | Areas --------------- 1 | EAST 2 | EAST 3 | SOUTH 4 | WEST The DDL: drop table allitems; Create Table Allitems(ItemId Int,areas Varchar2(20)); Insert Into Allitems(Itemid,Areas) Values(1,'east'); Insert Into Allitems(ItemId,areas) Values(2,'east'); insert ...
IF a create thousands of view, Does it hamper the database performance. I mean is there any problem with creating thousands of view in oracle. Please explain as I am new in this area...I am using oracle... ...
As far as I know (this page) Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. Is this a complete list of cases when indexes are created automatically in Oracle? ...
Hi, I am using Spring's JdbcTemplate and StoredProcedure classes. I am having trouble getting the stored procedure class to work for me. I have a stored procedure on an oracle database. Its signature is CREATE OR REPLACE PROCEDURE PRC_GET_USERS_BY_SECTION (user_cursor OUT Pkg_Types.cursor_type , section_option_in IN Varchar2 , se...
The following code works without problems: select donem, mekankodu, count(yayin_kodu) yc, SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) yys from ( select donem, bayi_adi, bayi_kodu, x.mekankodu, mekan_adi, mekan_tipi, yayin_kodu, yayin_adi, ...
Is there a option to see if existing table/record from a Oracle database is updated? ...
I have legacy oracle db with a sequence named 'PRODUCT_ID_SEQ'. And here the mapping of Product class for which I need generate correct ids: public class Product { @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "etailerRaw_seq") @SequenceGenerator(name = "etailerRaw_seq", ...
I'd like to run a sqlplus script from a cron job. I thought I could put a line like: CONNECT "myuser/mypass@mydb" within the script and then just execute it with: sqlplus @myscript However, when I do so, I get: SP2-0306: Invalid Option SP3-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus Am I misunderstandin...
Where can I find a generic editor (JSP using Oracle's ADF) for create, read, update, and delete on any table? Example usage: User selects the name of a table. User then adds a new row, or updates/deletes an existing row. User saves the changes. Foreign keys would appear as drop-down lists, and all others as af:inputText. (The user-f...
What query can I run to simply see whether a user has privelege to execute a stored procedure. lets say user is UserA and stored procedure name is my_stored_proc I want to know whether UserA has execute permission on my_stored_proc UserA is not the owner of the storedproc. Some other owner grants him the permission. ...
Hi, Im trying to execute a refresh on a materialized view, but I cant get the script to compile. CREATE OR REPLACE PROCEDURE REFRESH_MV AS BEGIN exec DBMS_MVIEW.REFRESH('my_mat_view_mv','C'); END REFRESH_MV; I get the message: ORA-06550: line 3, column 9: PLS-00103: Encountered the symbol "DBMS_MVIEW" when expecting one o...