Hi, I have a package that gets invalidated on a regular basis and found this in the code: ALTER SESSION CLOSE DATABASE LINK; Can this invalidate package states? Though I can't seem to replicate it. create or replace package body invalid_package_state_test is procedure test is TEMP VARCHAR2(1) := NULL; begin SELECT 'Y' INTO TEM...
Friends, I'm working on a requirement where I need to call a Java API from Apex. The solution I put forward was to create a Java class, store it the database and add a PL/SQL wrapper and then use it. Which is pretty much what is described here. Whilst I am happy with this, I am interested to know if this is the only method? are ther...
Hi, I have a situation in oracle where I have two tables - Table A and Table B. Table A has the following columns (aid, owner_id, app_id) and Table B has the following columns (bid, aid, emp_no) Table B has a foreign key (aid) back to Table A. Based on the above tables, I am trying to write a query where an emp_...
my primary key entity look like below @GeneratedValue(strategy= GenerationType.TABLE) private Long id; when i run, i get error could not get or update next value;nested exception is org.hibernate.exception.SQLGrammerException:could not get or update next value but when i just change to @GeneratedValue private Long id; no error...
Hi, I have a table "AuctionResults" like below Auction Action Shares ProfitperShare ------------------------------------------- Round1 BUY 6 200 Round2 BUY 5 100 Round2 SELL -2 50 Round3 SELL -5 80 Now I need to aggregate results by every auction with BUYS after ne...
Table has surrogate primary key generated from sequence. Unfortunately, this sequence is used for generating keys for some other tables (I did not designed it and I cannot change it). What is the fastest way to select last n inserted records in Oracle, ordered by id in descending order (last inserted on top)? n is some relatively sma...
The value of parameters 'NLS_CHARACTERSET' and 'NLS_NCHAR_CHARACTERSET' is UTF-8 for source database from where i am reading data, and AL32UTF8 and UTF-8 for target database where i am writing data. I am reading data from a text file which has english, european and asian characters, I am using UTF-16 code page to read from source flat fi...
Hi. We need to pull some tables from an Informix SE database, truncate tables on Oracle 10g, and then populate them with the Informix data. Does a bulk import work? Will data types clash? I'd like to use a simple Java executable that we can schedule daily. Can a Java program call the bulk import? Is there an example you can provide? T...
I've discovered that the partitions used on a particular table are hurting query performance and would like to remove the partitions from the table. Is there an easy way to do this? The table in question has 64 partitions. Based on some initial investigation, I've come up with the following options. Is there a better way? Copy data int...
Is it possible to redirect a TNS declaration to another one? For my current project, I require "flipping" from server to server in certain circumstances. To do this I use 3 TNS entries. One to hold the TNS name that my application will connect to: # application access to DB DB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST ...
We have a very strange problem in out application, all of a sudden we started noticing upside down question marks being saved along with other text typed in to the fields on the screen. These upside down question marks were not originally entered by the users and it is unclear where they come from. We are using Oracle 10g with Asp.Net. ...
How to Spool UTF-8 format data in Oracle database into text file with all UTF-8 Chars comming porperly. UTF-8 Characters example Chinese characters. I am trying to spool data from oracle data base which is UTF-8 enabled and trying to spool the same data into txt or cvs.Instead of the chinese charcters i am getting ????. ...
I have two tables: create table Number( num number(5)); create table Entry(id number(3), name varchar(50)); How can I increment the num field of Number table in Oracle whenever I insert something in the Entry table? ...
I have a sequence : CREATE SEQUENCE test_seq START WITH 10000001 INCREMENT BY 1; and a table : create table Entry( id number(5), name varchar(50) ); I need to increment the value of sequence after inserting a row in the Entry table. So i did this: CREATE OR REPLACE TRIGGER test_trigger after INSERT ON Entry REFERENCING NEW AS NEW...
Oracle 10g DB. I have a table called s_contact. This table has a field called person_uid. This person_uid field is a varchar2 but contains valid numbers for some rows and in-valid numbers for other rows. For instance, one row might have a person_uid of '2-lkjsdf' and another might be 1234567890. I want to return just the rows with valid...
Hi all I have a oracle procedure which should return a concatenated string of all the parameters create or replace procedure tin_builder (type in varchar2, tin_serial in number, rand_digit in varchar2, tin out varchar2 ) is BEGIN tin := type || TO_CHAR(tin_serial) || rand_digit ; END Now i want to call the proc...
I have oracle 10g installed on windows server 2003. I have 22,000,000 records in single table and this is a transactional table, increasing of records in same table approx. 50,000 per month. My question is that whenever I run query on it always my query too slow. Is there any method by which I can improve the performance of the query, l...
Hi I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it is created successfully. But when i execute: execute temp_proc; it shows ORA-00900: invalid SQL st...
Why can't we use :new and :old columns in a statement level trigger? ...
In dot net i can use dll file so that my code is safe from others. But is there any ways so that none can see the code of some of my procedures and triggers ... ...