In Oracle SQL Developer, there's a "SQL" tab for each table. This tab contains most of the SQL code (CREATE TABLE, CREATE TRIGGER, etc) that's needed to recreate the table. Is this information available programatically from the database system, or is this an application feature of SQL Developer? If the former, what commands/statements ...
I am running this statement: select trim(a),trim(b) from table x; Even though I used the trim() statement, my output looks like this: A B ___ ____ kunjramansingh ...
Suppose I have two Oracle databases. We'll call them database A and database B. Now suppose I have a table in database A that's defined like this: CREATE TABLE foo ( foo_id INT PRIMARY KEY, some_text VARCHAR2(10), other_table_id INT CONSTRAINT some_fk_constraint FOREIGN KEY (other_table_id) REFERENCES ...
Part of an ASP.Net 2 datasource: SelectCommand="SELECT BU.P_GEAC_CORP_CD AS Corp_Code, BU.Business_unit as Abbreviation, CC.DEPTID AS Cost_Center, CC.DESCR AS Description FROM fstst.PS_P_CATR_BUDPT_VW CC, fs...
I'm running Oracle 11g on Linux and I'm trying to run a script which will create my database. This script runs fine on windows, but when I test it on Linux, I get the following error: SP2-0556: Invalid File Name The problem may be that the path to the file name has a space in it. I'm going to simplify the problem down to one of the ...
Would WHERE substr(my_field,1,6) = 'search' or WHERE my_field LIKE 'search%' be faster in Oracle, or would there be no difference? ...
Initial Question How do you create or replace a role (that might or might not exist) in Oracle? For example, the following does not work: CREATE OR REPLACE ROLE role_name; GRANT SELECT ON SCM1_VIEW_OBJECT_VW TO role_name; Any way to do this without PL/SQL? Update #1 The following does not compile: CREATE OR REPLACE FUNCTION crea...
I am working with database tables that contains PII and sensitive information. Some of the data is PII and company sensitive information. The design document may not always be available to the developer especially when the data is being exposed by a view outside of the database (Oracle database link) to another program. Is there good n...
I have created public synonym as suggested in my other question about creating view at system level. Having said that I have created individual public synonym out of the view so that I don't have to connect to the individual domain anymore. My problem now is how to create a master kind of public synonym to capture all those synonyms whic...
Hi I'm writing a database procedure to archive data in an Oracle 9i table that contains BLOBs. Because the database size has grown too large, our strategy is to export the BLOBs to the file system (Windows 2000 server), where they can be backed up on tape, then to truncate the database table. Here's my procedure that saves a BLOB to di...
I've a case in which I need to refer to another database user. I've to hard code database user name in view while referring to it. SELECT * FROM eg001t3.DUAL; // example. Is there a way to refer to that db user (eg001t3) from view dynamically or based on a database setup? ...
SELECT (b.descr || ' - ' || c.descr) description FROM tbl1 a LEFT JOIN tbl2 b ON a.ACCOUNT = b.ACCOUNT LEFT JOIN tbl3 c ON a.product = c.product WHERE a.descr50 = ' ' ; table1 has only 7622 rows with descr50 = ' ' but this select is returning 7649 rows. Could you please help me in this? thanks in advance ...
Hi, I have the following Problem in a PL/SQL Script: I am trying to print an TABLE TYPE for debugging purposes, but don't know how. I tried the following: dbms_output.put_line (V_TEMP_TABTYPE(1)); and dbms_output.put_line (V_TEMP_TABTYPE); which doesn't work (PLS-00306: wrong number or types of arguments in call to). So, how can I pr...
Does altering the JOB_QUEUE_PROCESSES to 0 on an Oracle DB block the Oracle jobs from being scheduled too? I found in http://download-west.oracle.com/docs/cd/A97630_01/server.920/a96521/jobq.htm some information about it, principally, the following: The JOB_QUEUE_PROCESSES initialization parameter controls whether a coordinat...
For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1. How do we get the number of records affected by a stored procedure? ...
DbCommand command = new OracleCommand( "insert into hardware (HardwareID) VALUES (6);", myConnection); command.ExecuteNonQuery(); Hardware is a NUMBER(7, 0). I am trying to make this simple Oracle INSERT work using C#. However, I keep getting an 911 error saying that there is an invalid character. What am I doing wrong? I ca...
The error is : System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. I'M using 8.1.7 Any help will be appreciated. ...
I have add a null value to parameter list for inserting values to a table, which accepts some null values. Here is my example codes: bool sql = true; // .... List<IDbDataParameter> parameters = new List<IDbDataParmeter>(); // adding values... object objVal = 1; parameters.Add( sql ? new SqlParameter("@colIntA", objVal) : ...
select (t1.a + t2.b) sum from (select (aa + bb) a from table_x where cc = 'on') t1, table_y t2 where t1.id = t2.id The problem is that when t1 is not found, the final result will be null; How can I make the default value of t2.b to 0, when t1 is not found? Thx in advance. ...
Can someone enlighten on why is that oracle does not support an autoincrement feature for primary keys? I know the same feature can be achieved with the help of sequence and triggers, but why oracle didn't introduce the autoincrement keyword which will internally create a sequence and a trigger. I bet guys in oracle would have definitel...