Hi, Trying to run some sql in a pl/sql procedure. Select field from schema.view; I get a compile error Error(22,18): PLS-00201: identifier 'schema.view' must be declared From the error, it seems that my user does not have access to the table. I can run the same statement in a query window. Is there a permission I need to gran...
Does anyone know an easier way to work with user defined types in Oracle using cx_Oracle? For example, if I have these two types: CREATE type my_type as object( component varchar2(30) ,key varchar2(100) ,value varchar2(4000)) / CREATE type my_type_tab as table of my_type / And then a procedure in package my_package as follows:...
I love Oracle SQL Developer so I find myself using it a lot to complete my daily tasks. The thing is that my collection of custom snippets only keeps growing so I though it'd be interesting to learn how to backup them. Before asking blindly I did a bit of work trying to figure out how. I've performed a search within the application's fo...
I am trying to build a query such that some column are built up from a previous matching row. For example with the following data: CREATE TABLE TEST (SEQ NUMBER, LVL NUMBER, DESCR VARCHAR2(10)); INSERT INTO TEST VALUES (1, 1, 'ONE'); INSERT INTO TEST VALUES (2, 2, 'TWO1'); INSERT INTO TEST VALUES (3, 2, 'TWO2'); INSERT INTO TEST VALUES...
Hi all, I'm currently using Oracle db11g on Red Hat Enterprise Linux 5.0. I make an incremental level 0 one time a week and incremental level 1 everyday. I can restore this backup on my new Linux server without any problems because I have all archive logs generated after level 1 backup. However, if online redo log is not yet filled (I ...
I am working with an Oracle 10g Database. I have the following two tables: T_DEBTOR : - ID_DEBTOR - HEADER T_ELEMENT : - ID_ELEMENT - ID_DEBTOR - INSURER These two tables are joined using the ID_DEBTOR field. I want to update the T_ELEMENT.INSURER value with the associated T_DEBTOR.HEADER only if HEADER is not nu...
Helo, I am using oracle 10g and the output coming when SELECT command is in a zig zag format. How I can arrange coulumns in correct order? Is there any command? ...
I am working on migration of data from an old system to a new system. As part of migration, the data from the legacy system, (stored in files) is pumped into MS SQL Server. Now my app runs on Oracle. I'm having a problem with the date/timestamp. The timestamp format in MS SQL Server data is: 2008.12.23 00:00:00 Oracle expects:...
I have a query with a sub query in the select that needs to get a value from a certain table if it exists. I don't want to paste the query here for business reasons, but here's a simplified example of what I am trying to do: select a, b, (select x from z) as c from table where ... The query runs fine in TOAD, but when I go through t...
On Oracle 9i, why does the following produce the result 'abc' select 'abc ' || (select txt from (select 'xyz' as txt from dual where 1=2)) from dual while this produces 'abc xyz': select 'abc ' || (select txt from (select count(*), 'xyz' as txt from dual where 1=2)) from dual Why does adding count(*) to the subquery r...
I have a table in Oracle containing two columns that I'd like to query for records containing a unique combination of values, regardless of the order of those values. For example, if I have the following table: create table RELATIONSHIPS ( PERSON_1 number not null, PERSON_2 number not null, RELATIONSHIP number not null, ...
How to view a BLOB data, can i export it to textfile? . i am using Oracle SQL developer 5.1. When i tried select utl_raw.cast_to_varchar2(dbms_lob.substr(COLNAME)) from user_settings where = it says the following error ORA-06502 PL/SQL : numeric or value error : raw variable length too long the BLOB contains text in XML format ...
In order to load data (from a CSV file) into an Oracle database, I use SQL*Loader. In the table that receives these data, there is a varchar2(500) column, called COMMENTS. For some reasons, I want to ignore this information from the CSV file. Thus, I wrote this control file: Options (BindSize=10000000,Readsize=10000000,Rows=5000,Errors...
Hi folks, I need to change the sum(decode()) expressions that are like SUM(Decode(vcon.WAGON_TYPE_CODE,'MS',1,0)) to something that counts rows with vcon.WAGON-TYPE-CODE = 'MS' but only when wag.ACI-TAG-NO is distinct. So if two columns look like this vcon.WAGON_TYPE_CODE wag.ACI_TAG_NO MS HI1111 SS ...
Hi, In retrieving data from a Oracle database that contains BLOB fields (Office's .doc files etc), do I have to do something particular to send it via web services? It is sent like a string and I'm trying to convert the string into an array of bytes and write it to a file to recreate, client-side, the .doc file. What is the correct app...
Hi, Basing on your experience with Oracle, what will be the best type and settings for index that you would set on a column of DATE type? I don't necessarily need to go for partitioned index. It is a logging kind of table. You don't really care about unique id as a primary key (in fact date is close enough to be uniques most of the ...
Hi, I want to INSERT various users into a Oracle db with a stored procedure. A user (table "user") has, say, name, surname and date of birth: CREATE TABLE "USER" ( "Name" VARCHAR2(50), "Surname" VARCHAR2(50), "Dt_Birth" DATE, ) A stored procedure to create a user is pretty simple: CREATE PROCEDURE Insert_User(p_user, ...
With reference to http://stackoverflow.com/questions/980324/oracle-variable-number-of-parameters-to-a-stored-procedure I have s stored procedure to insert multiple Users into a User table. The table is defined like: CREATE TABLE "USER" ( "Name" VARCHAR2(50), "Surname" VARCHAR2(50), "Dt_Birth" DATE, ) The stored proced...
Hi- I have two different schemas in Oracle (say S1, S2). And two tables in those schemas(say S1.Table1, S2.Table2). I want to query these two tables from schema S1. Both S1 and S2 are in different databases. From DB1 - Schema S1, I want to do something like this, select T1.Id from S1.Table1 T1 , S2.Table2 T2 Where T1.Id = T2....
I want to specify language for the JDBC connection before it is actually created. For example if I specify wrong L/P credentials in DriverManager.getConnection(url, user, password) I need to get ORA error localized to the language I selected. I use Oracle thin client and setting NLS_LANG environmental variable did not work. :-( ...