oracle10g

Oracle nested tables and column names

I'd like to do the following in Oracle 10g (this is a contrived example to show the concepts, not real code) create table orders (order_id NUMBER); insert into table orders values (1); insert into table orders values (2); insert into table orders values (3); TYPE NUMBER_ARRAY_T is TABLE of NUMBER; PROCEDURE VALIDATE_O...

Difference between 'Execute' and 'Execute as script'

Hi Guys. I'm currently experiencing what I believe is strange behavior when using Oracle with TOAD. I have a query: SELECT COUNT(as_at_date) FROM job_log WHERE as_at_date = TO_DATE('24/11/2009', 'dd/mm/yyyy'); When I try to run this using Toads 'Execute statement' button I get zero rows returned. However when I use '...

How to resolve Oracle error ORA-01790?

I have two select statements joined by "union". While executing that statement I've got: Error report: SQL Error: ORA-01790: expression must have same datatype as corresponding expression 01790. 00000 - "expression must have same datatype as corresponding expression" Maybe you can give me an advise on how to diagnose this problem? ...

Does anyone have a sample data migration script (Oracle 10g to Oracle 10g, but different schemas)?

I am trying to write a data migration pl/sql script to transfer some of the data in one schema to a different schema on another server. The second database started as a subset of the original database, but we have modified the schema. So I can't just use the following for each table: Insert into DB2.table_name select * from DB1.table_n...

Python unable to acquire Oracle environment handle in Apache

I can run my django application using the developer server, but it does not run under apache. The error is: Unable to acquire Oracle environment handle Adding an ORACLE_HOME environment variable pointing to my 10g client did not fix the issue. NOTE: the ORACLE_HOME did not end with a slash. Also, this is using the XAMPP version of...

how to loop accepting user input with pl/sql?

I want to be able to insert a variable number of rows into a table based on user input? eg. Please enter value, enter "done" when no more values: value 1 Please enter value, enter "done" when no more values: value 2 Please enter value, enter "done" when no more values: done 2 Rows inserted successfully. I'm not sure how to store the ...

How to read xml respone using in oracle sql

How to read xml respone using in oracle sql(I have one node status. I want read the status node and insert into a table) plz help me regards ram ...

how to sort_area_size increase

how set sort_area_size in oracle 10g and what size should be as i have more than 2.2m rows in single table. and please tell me the suggested size of SORT_AREA_RETAINED_SIZE as my queries are too much slow they takes more than 1 hours to complete. (mostly) please suggest me the way by which i can optimize my queries and tune the database...

web form insert and trigger before insert

I have a oracle table called: create table InsertHere( generate_id varchar2(10), name varchar2(100) ); I have to insert data in this table from a web from. In the web form there are two elements: type and name . I have to generate 'generate_id' from the type user has selected. Task : Before insert into the Insert...

Oracle 10g temp tables

I'm trying to convert the permanent tables used in a stored procedure to global temp tables. I've looked at the stats on these permanent tables and some have tens of millions of rows of data and are on the order if gigabytes in size (up to 10 GB). So, CREATE TABLE my_table ( column1 NUMBER, column2 NUMBER, etc... ) TABLESPAC...

Oracle: Export & Dump file only creates tables, no data

I have in Oracle Exports a schema with 100 tables database size 30 GB. The dump file Size is 2 mb. When I import the dump file (2 mb) into a new database, all the tables are empty but the user tablespace is 30 GB although all tables are empty? ...

Random number generation in Oracle

In C# I can generate random number from a string like this: public string CreateRandomNumber(string normal) { string abnormal = (new Random(normal.GetHashCode())).Next().ToString(); return abnormal; } But I want to do the same (kind of) operation inside the oracle database. So, how can I generate a random number from a string ...

Oracle shared memory error

The following query generates the error below in 10g: select DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE','TEST') from dual; Got: ORA-04031: unable to allocate 128 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sql area","qeeOpt: qeesCreateOpt") ORA-06512: at "SYS.DBMS_METADATA", line 1546 ORA-06512: at "SYS....

insert one account for each row and every dates and payments associated with that account base on the each sequence from JAN-DEC

/* This script was suppose to insert one account for each row and every dates and payments associated with that account base on the each sequence from 1- 12 representing each month , Instead the script is inserting one account for each row with the same payments in the entire records\ is not working right . INSERT INTO TAIWOS.TEMP...

PL/SQL - use same pipelined function twice in the same query

I'm trying to use a pipelined function to save on time and reduce redundancy in my queries. The function in question returns data from a reference table based on some input. Records in the main data table I am selecting from have multiple columns that all refer to the reference table. The problem I run into is that when I try to use the ...

Problem with Crystal report and connection to database - oracle

hi i have Oracle 10g database that connect to my C# program, and i have crystal report that connect to this database. in my computer all work excelent !!, in customer computer the program works - except the crystal report. what can be the problem ? thank's in advance ...

Enable constraint on Oracle flashback table

I ran a data copy function in Toad that disabled all constraints and then failed. That left me with a bunch of disabled constraints. Most of them I've been able to enable successfully, but the flashback ones are giving my stored proc fits. Questions: 1) Is it necessary to reenable the constraints on the flashback tables? My assumpt...

inserting one account for each row and every dates and payments associated with that account in one row each in (ORACLE)

This how the script run from the begin to the end INSERT INTO TAIWOS.TEMP_CAPONE_DATE_JUL2--------- This is working fine SELECT FACS_ACCT_NUM, PROMISE_DATE, PROMISE_AMT, '0' FROM MGMT.FACS_PDC_PROMISE WHERE CLIENT_CD IN ('CAP20J', 'CAP20U', 'CAP21E', 'CAP20E', 'CAP21J', 'CAP22E', 'CAP22J', 'CAP22U', 'CA...

How to obtain an scroll_insensitive resultSet from a callableStatement in Java JDBC?

Hi, I have a stored procedure in an Oracle 10g database, in my java code, i call it with: CallableStatement cs = bdr.prepareCall("Begin ADMBAS01.pck_basilea_reportes.cargar_reporte(?,?,?,?,?); END;", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); cs.setInt(1, this.reportNumber); cs.registerOutParameter(2, Oracle...

Keeping a DHTML tree expanded in Oracle APEX

Friends, I have created a list item to be used as the primary navigation within my apex application. When the application is run, the tree can be expanded and you can click on a leaf node and are taken to the correct page however on arrival at the new page, the tree is back at it's starting position\completely collapsed. My question...