what the following output of the oracle function "dump" means?
query: select dump(:new.messaging_state) from dual
output:
Typ=2 Len=1: 193
I need this debug output because I want to know which value the program wants to write in the columns messaging_state.
The update fails because of a parent Key violation.
Oracle Version: 11gR2: 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 X86_64
Currently, I'm reading the online book Oracle Concepts, Chapter 3: 3 Indexes and Index-Organized Tables, section: Reverse Key Indexes in order to understand this topic.
As I understand for each pair of (key, rowid) in the index structure, the rowid for each row in the table obviously remains the same but the bytes of the key are reversed before the key is stored. So for example on a 32 bit machine (just an example) a key = 10 AB CD EF will be stored as FE DC BA 01 , am I right?
According to the documentation, this becomes interesting in RAC environments in order to remove a hot spot from the index (when multiple instances repeatedly modify the same block) with the disadvantage that in some cases there cannot be Index Range Scan any more as data in the index is not sorted by column key when it is stored.
I was just curious to see how bytes of each key are reversed and after a bit googling I found an article where Tom Kyte shows with an example by using dump function (which as I understand gives the internal representation of a given expression) the difference in the sequence of bytes. Here is the link
[URL]
So based on his instructions I tried to do my own test, yet I don't get the same result, that is, the bytes are not reversed for me once we rebuild the index by REVERSE key word.
I'm going to write down here the test that I did, where is/are my error(s)
Test Case:
(I use a copy of the employees table in hr sample schema)
SQL> CREATE TABLE emp_test AS SELECT * FROM hr.employees;
Table created.
SQL> CREATE INDEX emp_test_idx ON emp_test(first_name);
[code]...
Now, a test SQL Query using the index we've just defined (just for giving an example)
SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';
FIRST_NAME DUMP_RESULT
-------------- ----------------------------------
Kelly Typ=1 Len=5: 4b,65,6c,6c,79
[code]...
So, according to the above Execution plan, Oracle does an Index Range Scan using the index that I defined on my table that is, emp_test_idx. According to the output of dump, the key (first_name) in that index is stored (in terms of bytes) as 4b,65,6c,6c,79
SQL> SELECT chr(to_number('4b', 'xx')) ||
2 chr(to_number('65', 'xx')) ||
3 chr(to_number('6c', 'xx')) ||
4 chr(to_number('6c', 'xx')) ||
5 chr(to_number('79', 'xx')) first_name
6 FROM DUAL;
FIRST_NAME
--------------------
Kelly
Which as we can see corresponds to the first name 'Kelly', the first name we specified in the above SQL query.
Now let's rebuild the index
SQL> ALTER INDEX emp_test_idx REBUILD REVERSE;
Index altered.
SQL>
Once the index keys have been reversed, I run the very same query in order to see the difference
SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';
[code]...
So the second time after the index has been reversed, I still get the very same sequence of bytes, that is, 4b,65,6c,6c,79, whereas I expected to get 79,6c,6c,65,4b (that is, the reversed order of the initial bytes sequence)
Here table - tac has
row1
-----
X
X
X
B
[code]...
I want ouptut like using sql query with out using dense_rank function,
row1 row2
X 1
X 1
X 1
B 2
[code]...
What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.
View 49 Replies View RelatedI have a report which comes from a table-function. This produces hundreds of lines of output which the user would like to be able to select and then export just the subset of lines.Looking at the Forum's (this article and this one) this seems possible if you are working with a table where you can re-query the original dataset with just a specific rowset required however as the table-function is generating output on the fly it is hard to rerun the query to reselect the same output for redisplay. Also most of the reports are working with interactive reports - whilst this is just static output. Is there a way to redisplay the information which has already been shown in an filtered way with check boxes?Is there then a way to have hidden fields which are not shown when the check boxes are selected but which can be shown in the "filtered" view? I am using Apex 4.0 at the moment on an Oracle 10g instance.
View 0 Replies View RelatedI have a question on Round Function
Round(###.###,1)
Will the round function output depend on 2nd digit after decimal point also or not?
In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join
CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS
[code]...
I want to know whether it is possible to use export dump from Oracle 11.1.0.6 WIN 2003 SP2 Server to Oracle 11.1.0.6 Windows 7 standalone Machine (Test machine).If yes how to do it as i have tried but it is returning errors.So i tried to do it by pre-creating Tablespaces but even then its failing.
Note : I am Using EXPDP and IMPDP
my problem is that whenever i want to import a dump file(oracle 10g) oracle just import 4 tables and then goes into hang state(not responding) i'm using old import method (not datapump).
View 1 Replies View RelatedIs there is any way of reading from oracle dump file?
View 16 Replies View RelatedIf I want to import 10g export dump file in to the 9i database, I am connecting to 10g database from 9i database using
exp user/password@10gdb ....
However is there any option like executing 9i catexp.dat on 10g database and do the export from 10g database itself to be imported into 9i?
Can we convert or export oracle 9i dump to 8i dump ?
View 1 Replies View RelatedWhile trying to import a schema using Data Dump, I am facing the following issue - UDI-00018 - Import utility version can not be more recent than the Data Dump server.Following is the version information of the source and target DB and the utilities :
Source DB server : 10.1.0.2.0
Export utility : 10.1.0.2.0
Import utility : 10.1.0.2.0
Target DB server : 10.1.0.2.0
Export utility : 10.2.0.1.0
Import utility : 10.2.0.1.0
I am trying to import a dump into my oracle 10.2.0.3.0 database on my win 7 professional laptop. The dump is exported from my win xp desktop pc running Oracle 10.2.0.1.0
Below is the error i get:
Import: Release 10.2.0.3.0 - Production on Fri Nov 12 15:57:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: system/password@orcl
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Import file: EXPDAT.DMP > F:PersonalDPISIMBA.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username:paymaster
Enter table<T> or partition<T:P> names. NULL list means all tables for user
Enter table<T> or partition<T:P> name or . if done:
when I press enter key, the console hangs and a window appears with "Console Window Host has stopped working" then the console closes prematurely.
How we can overwrite existing dump file for expdp in oracle 10g because everytime we excute expdp and dmp file exist we get below error
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:scott_emp.dmp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
We have one feature in 11g reuse_dumpfiles=y ,which doesnt work in 10g, I want something which can overwrite existing dumpfile in 10g?
What is the Default Path of Log File after Import Dump in Oracle 10g.
View 1 Replies View RelatedHow to import constraints only from a dump file using Oracle data pump.
View 1 Replies View RelatedIs it possible to import a dump file using impdp data pump utility on oracle 10g where the export dump was taken using traditional exp utility and vice versa.
View 1 Replies View RelatedDECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.
Test Case
--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);
--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...
The thing is I am not getting any output when i run your procedure.I need to set any nls language? .. even i try to run below qry from the another thread,
column french format a20
with t as (
select 'fish' txt from dual union all
select 'dog' txt from dual union all
[code]...
My output is blank for the french values. I am sure that I missing something, But i dont know what I am missing .currently my CHARACTER SET WE8MSWIN1252
i have a problem with oracle reports 6i. the output has 1000s of lines. in my output first to 999th line is fine..bt then 1000th line is blank and again the data continues to be shown from 1001th line till 2000. and again i see a blank. how can i get rid of these blank lines very 1000th line.
View 3 Replies View RelatedEMP table
create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);
insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);
commit;
select * from emp1;
actual output :
empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000
Expected output :
empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000
I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.
I have to return only one row based on dept values for one empno. how to do in sql or plsql?
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);
[Code]....
My expected output should be like below:
Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40
how I can achieve the expected output using a single sql query
When I run my report , I generate it in pdf format, and some fields in my report have persian characters so
1- I use Tahoma font, and
2- I have change my window registery to america_america.ar8mswin1256 also
3-I have modified UIFONT.ALI (but when I add tahoma font in this file , then I check the font in property menu of pdf file , its type is not change to type3---- I'm not sure about the modfication in this file----)
4- I have added windows/font path in report_path in registry
5- I made sure that there is tahoma.ttF in this path
but after opening PDF file ,those fields which are in Persian language (persian characters) are dipalyed in strange font.
I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.
Is there any method to convert following output in to ASCII format ??
select ename from scott.emp;
ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
Oracle10g to Sybase12.5 Migration:- How a Oracle dump file can be converted to any text file/xls file which will be loaded in sybase database later through BCP.
means
1.Exporting objects as dump file from Oracle.
2.Is there any tool/process available that can convert this into csv/txt/xls file.
3.This files can be loaded in sybase.
I have a SQL Loader Log File Output. I would like to delete Table Information from the output file.Following is my code.
//PATH OPENED AS TEXT BY STREAM READER
System.IO.StreamReader strm = File.OpenText(batchFileName);
//OUTPUT
[Code]....
i am generating report from oracle forms using web.show_document .pdf output is coming in seperate url.entire piece of code is working fine.now i want to store this pdf out put in client machine whether path will be specified by user.
View 3 Replies View RelatedI have new machine with oracle 11g and i have exported dump from oracle 10g . Now i need to import that dump on oracle 11g.
View 3 Replies View Related
