Autoincrement in Oracle
What are the other ways of achieving autoincrement in oracle other than use of triggers? ...
oracle
Oracle - Select where field has lowercase characters
Hello, I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in all upper case, but somehow some values in these two fields are mixed case. I want to run a query that will show me al...
How to install system packages into oracle?
I am creating a copy of our testing Oracle DB (10g both) and run into a problem in that our testing database has UTL_SMTP and UTL_FILE system packages installed and my local OracleXE version does not. Our system administrator has gone home for the day and given his state earlier I really would rather not bother him. How do I get these ...
How to limit number of rows returned from oracle at the jdbc data source level?
Hi, Is there a way to limit the rows returned at the oracle datasource level in a tomcat application? It seems maxRows is only available if you set it on the datasource in the java code. Putting maxRows="2" on the datasource doesnt apply. Is there any other way limit the rows returned? without a code change? Thanks, Dan ...
Oracle User Management
Does anyone have experience working with Oracle User management? Any idea if it can be easily used to register users and roles into Oracle OID? And how I can use the defined roles in my application (i.e. should I hard code them)? ...
Need ideas on outputting table data to a CSV using PL/SQL in a dynamic fashion
The task is to take a list of tables which is changeable. Write a piece of PL/SQL that when executed outputs every tables rows into individual csv files. So if 5 tables. You will get 5 CSV files with the relevant table data in it. The CSV should be | delimited and have " around each value (for easy import to excel) All I know is the ...
Continue on error in loop
The loop below is callign a proc that does various 'things' If it should throw an exception it also 'raises' it. I want to catch it and ignore it and allow the loop to continue processing the next value in the array. Thanks WHILE indx IS NOT NULL LOOP table_dump_csv(tableList(indx), tableList(indx) || '.csv'); indx := table...
Hibernate and Oracle native functions
I have an entity that maps to an external oracle table which is one of the primary data sources of my application. This entity is modelled using hibernate. The oracle table now has a complex function defined the calculates some special values. I need to call this funtion somehow - almost as another accessor on the entity. What would yo...
Connecting to Oracle from ASP.NET is very slow - how to diagnose?
I have an ASP.NET application that's connecting to an oracle database. Performance on some queries in dev is very slow - on the order of 90 seconds to connect to the database, run a a query (actually, call a SP in a package) and return a single row of results. I've run a profiler on the app, and just about 100% of the time is spent in ...
Is it possible in SQL Server to create a function which could handle a sequence?
We are looking at various options in porting our persistence layer from Oracle to another database and one that we are looking at is MS SQL. However we use Oracle sequences throughout the code and because of this it seems moving will be a headache. I understand about @identity but that would be a massive overhaul of the persistence code....
I need to store postal codes in a database. How big should the column be?
I expect the column to be a VARCHAR2, in my Oracle Database. US Zips are 9. Canadian is 7. I am thinking 32 characters would be reasonable upper limit What am I missing? ...
Metasolv APIs
Hello all, I work in the tool called MetaSolv Solution which is of Oracle. I want to have APIs used to create 'Procuct specification' & 'Product catalog'. Provide me help in this. You can also give me the links where i can get these APIs. ...
PL/SQL: Fetching from a Cursor that is passed between two functions
Hi everyone, I have a quick question about fetching results from a weakly typed cursor and was wondering if anyone had come across this problem before? My set up is as follows; Inner function; create or replace FUNCTION A_CURSOR_TEST_INNER ( varCursor OUT SYS_REFCURSOR ) RETURN NUMBER AS varStatus NUMBER; BEGIN OPEN varCursor ...
ORACLE 10.2 Pro*C precompiler not reading header file
I'm pre-compiling a C program containing Pro*C code with ORACLE 10.2 and AIX 5.2 The ORACLE precompiler reads the $ORACLE_HOME/precomp/admin/pcscfg.cfg file which contains the definition of the sys_include variable (set to /usr/include) The Pro*C compiler complains that it doesn't know what the size_t type is and the ORACLE header file...
Oracle join operator
How to rewrite this: select tab1.id, tab2.id, tab3.id from tab1, tab2, tab3 where tab1.col1 = tab2.col1(+) and tab2.col2 = tab3.col2(+); using OUTER JOIN syntax? ...
Oracle DB query formating problem
Hi, I have a problem with formating the data when doing an query to an Oracle database. What I want to do is to export some data into the formatbelow into a textfile; 1IN20071001 40005601054910101200 1 65 First number (1 above) = Company number (position 1-5, blanks infront) IN or UT = IN for clockin and UT f...
sqlplus access and email access using shell script..
I am fairly beginner level at shell scripts and following are the details.. Am looking for the best way to fire sql queries and and carry out some logic based on that data. I've used the following snippet.. shellvariable=sqlplus $user/$passwd <<END select count(1) from table1; end EOF if[$shellvariable -ne 0] then <> fi Is there a be...
connecting SQL SERVER 2005 from Oracle
hi i am running Oracle and SQL SERVER 2oo5 on the same server. Plstform win 2003 Enterprise. i have created a System file DSN. Make the entry in listener.ora and tnsnames.ora. but when i m tring to ping my dsn system dont responds. and when i m trying to create a database link using my dsn, it gives error. What to do ? thanks for hel...
Speed up NHibernate Calls to Oracle?
Are there any optimizations to consider when connecting to Oracle with NHibernate? My calls to Oracle are taking an extremely long time. If I run the NHibernate query (copied from my output window in Visual Studio) directly against Oracle, it comes right back in under a second. Here is my config file for NHibernate. I can post the mappin...
Network Outage Causes Stored Procedure Querying Across DB Link to Hang Forever
A number of stored procedures I support query remote databases over a WAN. The network occasionally goes down, but the worst that ever happened was the procedures failed and would have to be restarted. The last couple weeks it's taken a sinister turn. Instead of failing the procedures hang in a wierd locked state. They can't be kille...