Currently, I am running 4 separate queries in order to retrieve specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g. :
At the moment, I am extracting the required info as follows.
Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303)) from table.msg_archive ma, table.msg_log ml where ma.message_id = ml.message_id and ml.message__cd = 'MP_XML' and ml.message_reference in (456123)
Once retrieved, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))
Example query 2: message_content, 2000, 581 > retrieves the starting point for a user id.
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581)) from table.msg_archive ma, table.msg_log ml where ma.message_id = ml.message_id and ml.message__cd = 'MP_XML' and ml.message_reference in (456123)
WITH table_data AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL UNION ALL SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL UNION ALL SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL [code].........
I can extract the data from before the first *, but I need to also be able to extract the data from between the 1st and 2nd *s, e.g. Z23, A32345, TEST, THIS and THAT from the sample data above.
I exported and imported data from one oracle database to another, but not all the data got loaded in to the destination database. Basically it is filtering the data. Can it be because of the reason that the sql developer may be an express edition?
Now i have to insert this xml into DB , the table consist of following columns ( row number , property name , value ) Expected out put is (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is
1. how to get the tag names and populate the property name column ? 2. The number of properties for a student can be variable , How can i deal with them ?
Below is the function code used to extract data from blob column. The function works fine when the blob data length < 2000 bytes. When it is more than, it is throwing an error as below.
Table name: mr_test Columns: id number seo blob
CREATE OR REPLACE FUNCTION fn_mr_blob(in_id IN number) return varchar2 IS len NUMBER;
[Code]....
ORA-01489: result of string concatenation is too long
when I replcae the
"SELECT myvar||trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;" with SELECT trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id; myvar1 := myvar1||myvar; ORA-06502: PL/SQL: numeric or value error: character string buffer too small
i have a software...i installed it and configured it to the database now i copied the listener file from a working server to this both have same name and configuration and databases with same port number.
but when i try to log in it throws a error
ORA-12154 TNS COULD NOT RESOLVE THE CONNECT IDRNTIFIER SPECIFIED
I checked my listener tns.ora files and all are correct but still i cant connect.
Im trying to display a word file from a blob column of table in reports 11g . In reports 6i OLE2 file format was available for that, but in 11g it became obsolete.
just realised the data guard configuration seems to be workin but the error kepps appearing on the alert log of both primary and physical standby that i configured. below is my primary and standby pfiles.
I'm creating a package function that would return the image from the table HR.PER_IMAGES.
Here's the table description of HR.PER_IMAGES IMAGE_ID NUMBER(15) IMAGE LONG RAW PARENT_ID NUMBER(15) TABLE_NAME VARCHAR2(30)
I have also created a simple PL/SQL code that would supposed to extract the value of the IMAGE column and store it in a variable then return it from a function.
DECLARE x LONG RAW; BEGIN SELECT image INTO x FROM per_images WHERE parent_id = :p_parent_id ; END;
However, I'm getting the "ORA-06502: PL/SQL: numeric or value error" message. I have tried retrieving an specific image using Oracle Reports Developer and when I queried it was able to render the image on the report page.
My standby database was working fine which was running in maximum performance mode .Suddenly i found that logs are not being applied though archived logs are received from primary database.i issued the command in mount mode: alter database recover managed standby database disconnect from session;
altered database this command runs successfully. but MRP process in not initiated which i confirmed with the query (select process,status from v$managed_standby;)
Using UTL_TCP package (for the first time; maybe there's smarter way to do that), I captured contents of a certain web page. The page contains (along with some text, images, etc.), a list of values I'd like to extract. Something like this:
PRVI miravira pond 23.7.2010 102,2221 NEKI miravira pond 23.7.2010 105,0996 DRUGI miravira pond 22.7.2010 101,3789
The result (output) of the PL/SQL procedure contains several hundreds of lines. I narrowed the output to a part which I'm interested in. It looks like this (a screenshot, because [code] or [pre] tags won't allow me to paint text, while the others don't preserve formatting):
Red values are what I'm looking for. The first one represents a date (July 23rd 2010), and the second one is amount (105,0996).
Now, what's the problem: the above mess doesn't look the same every day (probably because the rest of data that appears on a web page changes too). Therefore, SUBSTR (with hard-coded positions) that seems to be working today is wrong tomorrow. Moreover, IF condition I used might not provide desired part of a web page every time.
Here's the code (modified a little bit, unimportant for what matters):
declare c utl_tcp.connection; n number; buffer varchar2(255); l_x_dat varchar2(10); l_x_izn varchar2(10);
[code].....
L_X_DAT and L_X_IZN are values I need. SUBSTR parameters, as I said, are far from being good.Is there any smart way to extract those two values? What (Oracle) technology could be used here? Or should I just try to, somehow, set SUBSTR values correctly (dynamically)?
If there was a way to uniquely fetch those values (instead of getting the whole web page and digging for what I'm interested in), it would be extraordinary.
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; import java.util.List; [code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
I have a 10gR2 standby database.Normally when logs are created in primary they automatically get shipped to standby but sometimes I get FAL request rejected or connection lost contact error and the logs are not automatically shipped .
Then when I manually switch a logfile the RFS process copies the old logs into DR site(around 4 or 5 logs).What is the problem...How to resolve this???
In my DB there are special characters are stored like "Świętochłowice". So in Unix script while extracting these characters, I have used export LANG="universal.UTF-8" in order to make it English. which works fine.
But my question is how to retain this special character intact as, it is in DB?
So in UNIX script export LANG="universal.UTF-8" sqlplus -s uname/pwd@hostname/schema << ENDSQL | sed -e "s/Connected.//" -e "/^$/d" set pagesize 0 feedback off verify off heading off echo off serveroutput on size 10000 spool /path/out.txt Select name from tablename where is=12; spool off; exit ENDSQL
Output is "Swietochlowice" (makes sense),but how to get the output as "Świętochłowice" which is in DB? I have tried different NLS_LANG option, but no success.
i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY; alter tablespace APPLSYSD READ ONLY * ERROR at line 1: ORA-01230: cannot make read only - file 636 is offline ORA-01111: name for data file 636 is unknown - rename to correct file ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636' i have not this file on the OS
I have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234