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 need a procedure to get the records from the table for every SKU of XML message from the table and take one record for each SKU based on order precedence.And send the set of output records to the .net code. The order precedence is
If MODEL is not null that record should be high precedence and consider max lead_time If MODEL and VK_UNIT is NULL then the precedence goes to STATE_ID.
The parameters in the procedure are
CREATE OR REPLACE PROCEDURE sku_proc ( p_bu_id IN number, p_model IN varchar2, p_vk_unit IN XMLTYPE, p_country_id IN varchar2, [code].......
Query to get the data from the table based on the parameters.
Based on the above query assume we got the following records.
BU_ID MODEL VK_UNIT COUNTRY_ID STATE_ID LEAD_TIME FC 0 M123 210-39348 AB 0 20 A 0 M123 210-39348 AB 0 30 B 0 NULL 210-39348 AB 0 10 C 0 M123 405-12132 AB 0 10 A 0 NULL 340-30904 AB 0 30 C 0 M123 340-30904 AB 0 20 B 0 M123 340-30904 AB 0 10 A 0 NULL 403-10890 AB 0 10 B 0 M123 403-10890 AB 0 20 A 0 M123 709-10007 AB 0 10 B 0 NULL NULL AB 0 20 B 0 NULL NULL AB 1 30 A
Th final query has to return the following result to the OUT parameters p_Lead_Time p_FC of the procedure.
LEAD_TIME FC 30 B 10 A 20 B 20 A 10 B 30 A
How to implement my above requirement using BULK COLLECT.
I developed a form in forms9i, at the time of data entering form is not showing any ERROR messages or SAVE RECORD message and when I press exit button it is asking "DO YOU WANT TO SAVE THE CHANGES YOU HAVE MADE".
I have checked my PRIMARY KEY field and there is no mistake and value is populating at PRE INSERT.
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.
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.
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 a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST ID DATA 1 3/12/2007 2 0 3 3/8/2010 ABC 217 4 NONE 5 COLM XYZ 469 6/8/2011 6 LMN 209
I'm migrating some forms from 6i to 9i and one of them has to use the webutil library, but when I execute the form in the IE, a message appear and I don't know from where because I haven't made it. The message say: "Confirme el mensaje" This is a print of the message,
I have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>> declare v_sal1 number(7,2) := 60000; v_comm number(7,2) : v_sal1 * 0.20; v_message varchar2(2000) := 'eligible for commission'; begin [code]........
I am getting "sqlnet message from client" in top 5 wait events and sql*net trace .We are getting waiting for SQL*Net message from client for a huge number of sessions with huge total waits. From session level sql tracing
we figured that the whole query took 7 to 8 Hours to complete. But in trace we see query executed within 10 to 12 minutes and after that it was showing waiting for SQL*Net message from client till next 7 hours.
We have a queue in which the message is coming from external system. The payload of the queue table is a PL/SQL record type. Once we get the message in the queue, we de-queue the message and read through the PL/SQL type collection and process the message.
From the below query, we are able to convert the PL/SQL collection message to XML message and see the data.
SELECT dbms_xmlgen.getxml ('SELECT USER_DATA FROM <Queue_table> X WHERE X.USER_DATA.SALE_ORDER.P_HEADER_REC.ORIG_SYS_DOCUMENT_REF=800501298') FROM dual;
The new requirement is the message would come in a XML message in the queue. So my question is, is there any way through which the XML message can be converted to the PL/SQL record structure directly (it would be the opposite operation of the above query).
