Here is the situation. I have one table that contains records based on records in many different tables (t1 below). t2 is one of the tables that has information pulled from it into t1. t1 table_oid --which table id is a FK to id --fk to other table store_num --field t2 t2_id Here is what I need to find: I need the ...
I need to trim New Line (Chr(13) and Chr(10) and Tab space from the beginning and end of a String) in an Oracle query. I learnt that there is no easy way to trim multiple characters in Oracle. "trim" function trims only single character. It would be a performance degradation if i call trim function recursivelly in a loop using a function...
I have two select statements that I am trying to port from Oracle to Postgres: 1) (Note: this is a subselect part of a bigger select) SELECT 'Y' FROM CRAFT MA, CONFIG MAC, ARMS SM WHERE MCI.MS_INVENTORY_NUMBER = SM.MS_INVENTORY_NUMBER (+) AND MCI.AB_BASE_ID = MA.AB_BASE_ID_LAUNCH AND SM.ACT_AC_TYPE = MAC.ACT_AC_TYPE AND SM.MAC_ID = M...
I would like to use essentially the same query in both T-SQL (SQL Server 2000/2005) and PL/SQL (Oracle 10g). Though it is not trivial, it is reasonably vanilla code, but it works in SQL Server yet fails in Oracle. My goal is to generate a count of rows for each unique upper/lower case combination of a particular field, omitting any field...
Hello. I am running a asp .net web service on IIS7. The latter is running on a Windows 2008 R2 Server. IIS7/the web service is configured for asp .net impersonation. The worker process owner is set to NETWORK SERVICE. The web service invokes sqlplus.exe impersonated with his/her domain account on the server. I can see that through the...
I'm working on what is turning out to be a fairly complex SELECT query. I have several hierarchical queries being nested in a single SELECT and it is getting to be quite difficult to manage. I'm running into a few places where my inline views need to be executed in more than one place, so it seems like a reasonable idea to execute thos...
Hi, Is there any option available for restoring a column which is set unused by ALTER TABLE command? ...
Hi. Currently I'm making sort of SQL command line interface for web-based application. It should act roughly like sqlPlus. I have encountered a problem how to execute sql's. They can be both as SQL and/or PL/SQL. First way I thought that I can split them (by ';' or ';/') and detect separately if it's sql select or delete/update/insert ...
Is Commit a DML instruction in PL\SQL? ...
We need to store a select statement in a table select * from table where col = 'col' But the single quotes messes the insert statement up. Is it possible to do this somehow? ...
I have a SSRS report which is using Business Object Provider. The dll has a connection with Oracle server. Whenever i run the report i keep on getting this error message: "Oracle.DataAccess.Client.OracleException ORA-12514: TNS:listener does not currently know of service requested in connect descriptor " Anyone knows why? ...
I have a table in Oracle for users. I am going to install new schema and want to backup all users with passwords and other fields. I tried exp and int utilities, but imp doesn't recover anything. I created temporary user in USERS table. Then I did backup with command: exp user_owner/password file=file.dmp table=USERS rows=yes indexes=n...
Hello I'm writing a combination Java/Perl program that parses XML files into an Oracle SQL database. There are two tables in the database - one that holds the data from the XML files and another that holds information about the files themselves (file name, creation time, etc.). Basically, when a new XML file comes along, the Java progra...
I am looking for a fast sql sentence for determine when a field exist or not in a table . actually i am using this sentence Select 1 from dual where exists (select 1 from all_tab_columns where table_name = 'MYTABLE' and column_name = 'MYCOLUMN') I think there must be...
I am porting an Oracle function into Postgres PGPLSQL.. I have been using this guide: http://www.postgresql.org/docs/8.1/static/plpgsql.html CREATE OR REPLACE PROCEDURE DATA_UPDATE (mission NUMBER, task NUMBER) AS BEGIN IF mission IS NOT NULL THEN UPDATE MISSION_OBJECTIVE MO SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED, ...
(This is similar to a question I asked earlier: http://stackoverflow.com/questions/2277731/porting-oracle-procedure-to-postgresql) I need to port: /* || The following private procedure will execute a dynamic pl/sql || statement passed to it. */ CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.EXECUTE_STMT (stmt VARCHAR) IS v_num_...
I am trying to convert my clob object to string to display that on my JSTL page for that I am writting the following code public String convertClobToString(Clob clob){ String toRet=""; if(clob!=null) { try { long length=clob.length(); toRet=clob.getSubString(1, (int)length); ...
Where can I get a complete list that maps the .Net Culture ID (used in the CultureInfo class) to Oracle's NLS_LANGUAGE and NLS_TERRITORY parameters? Oracle's .NET Developer’s Guide makes a start at this but want a complete list so that I can set this automatically for my database session in my code. ...
I have an ORACLE schema containing hundreds of tables. I would like to delete the data from all the tables (but don't want to DROP the tables). Is there an easy way to do this or do I have to write an SQL script that retrieves all the table names and runs the TRUNCATE command on each ? I would like to delete the data using commands in ...
I tried to grant CONNECT to a user through a role: CREATE ROLE my_role IDENTIFIED BY "passwd"; GRANT CONNECT TO my_role; CREATE USER my_user IDENTIFIED BY "passwd"; GRANT my_role TO my_user; When I try this in 10g it works fine, while in 11g login is rejected: ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied Gran...