SQL & PL/SQL :: Parsing BLOB Contents

Mar 28, 2013

I have some BLOB contents in the format of...

DK99F17,AA,032820130840,Other
ABCD,AA,032820130840,OV
AAZ123,BC,032820130932,DWL
CBA12345,ZA,032820130939,Other
Each BLOB is associated to a file name in the format...
03282013100002_thisfile.txt

The blob for each file may be zero rows to n rows in size, but typically there are 2 to 5 rows (four rows were shown in the rows above).The following kind of gets me there, but not quite as it splits up the BLOB rows at the comma and not the line break (HEX=0D0A / CRLF).

with rec as
(select fs.file_name, utl_raw.cast_to_varchar2(fs.file_data) file_data
from tada.files_store fs
where fs.file_name like '%citations.txt'
and trunc(fs.date_created) = to_date('26-MAR-2013','DD-MON-YYYY'))
[code].....

View 4 Replies


ADVERTISEMENT

Deletion Of Contents Of Tablespace

Jun 15, 2012

How can we clean a tablespace(delete all the contents) without dropping the tablespace.

View 3 Replies View Related

SQL & PL/SQL :: Information On Package Contents

Oct 25, 2012

I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.

To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...

SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;

However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).

So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?

Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?

View 3 Replies View Related

Alter Table Column And Its Contents?

May 4, 2011

I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.

I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.

View 2 Replies View Related

SQL & PL/SQL :: How To See The Contents Of Wrapped Package Body

Mar 31, 2011

I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.

View 1 Replies View Related

SQL & PL/SQL :: Table Contents - Unavailable Date

Oct 17, 2012

i have table A i.e. contents

----------------------
1-jan-2012 x
2-jan-2012 y
4-jan-2012 x
7-jan-2012 x

I need to discplay below:

1-jan-2012 x
2-jan-2012 y
3-jan-2012 0
4-jan-2012 y
5-jan-2012 0
6-jan-2012 0
7-jan-2012 x

View 1 Replies View Related

Server Utilities :: How To Know Dmp File Contents

Sep 24, 2011

Is there a way to list contents of a dmp file(using expdp generate),which objects(tables/vies/procedures/packages) does it contain?

View 4 Replies View Related

PL/SQL :: Adding Columns To Count Contents Of Other?

Aug 31, 2013

how to incorporate counts into my current SQL code  that would count food items, and then car items for each person. Once I understand this concept I hope to apply it to my real database (in Oracle) where I am counting medications and problems for each patient.For example using my test data below, the my goal would be to have count columns show that Cathy had 3 food count and a 1 cars item (the count would need to ignore the null values in these  columns). I don't really care if the count value repeats in every row for that person.

 Select data1.Name, data1.Category,Data1.Results,Case When data1.Category IN ('food') then data1.results end FOOD,Case When data1.Category IN ('Cars')then data1.Results end CARSFROM(Select T1.name Name, null Category, null Results from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNION Select T1.name, 'food', T2.Food  from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNIONSelect T1.name, 'cars',  T3.Cars  from a_Main T1, a_Cars T3 Where T1.cpi_seq = T3.CPI_SEQ)data1where data1.Category is not nullorder by Name,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Table With Different Contents

Mar 31, 2010

I am having a table with contents like

item id, name
100,s_enter
100,p_enter
200,s_enter

i would like the output to be

Variable name cnt_s_enter=1 (this is for 200)
cnt_s_p_enter =1 (this is becoz 100 is both in s_enter and p_enter)

I am working with basic case but thats not working.

select
sum(case when name='s_enter' then 1 else 0 end),
sum(case when name in (s_enter,p_enter) then 1 else 0 end)
from table

View 3 Replies View Related

Server Administration :: How To Read Dump Contents

Jan 4, 2012

How to read the treedump contents of a index IDX_TB_TEST_N1?

SQL> select object_id,object_name from dba_objects where owner='HXL';

OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------
51786 IDX_TB_TEST_N1

alter session set events 'immediate trace name treedump level 51786'

/u01/app/oracle/admin/oracl/udump/oracl_ora_2679.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

[code]...

View 6 Replies View Related

Server Administration :: Contents Of Redo Log Files

Feb 1, 2011

I have some doubts about redo log files,

1) Can we fetch 'select statements' from redo log files through the use of log miner utility or any other?
(I think redo log file contains only insert,update,delete and DDL/DCL commands only)

2) If "No" to the above answer then how can i fetch all select statements fired on the system for a day or particular time.
(setting of sql_trace may be the one of them, but can it be possible for system level)

View 4 Replies View Related

Client Tools :: SQL Developer Cuts CLOB Contents?

Aug 23, 2011

I'm having a problem while exporting an DB to file. I have a certain table with a CLOB column which values are cut when I use the Export option on SQL Developer.

View 1 Replies View Related

Backup & Recovery :: Restore Datafile With Its Contents Without Using RMAN

Oct 13, 2011

I have no backup taken in my oracle server.Today morning i created a data file and unknowingly deleted it. Now i need to restore the datafile with its contents without using RMAN.

View 14 Replies View Related

Application Express :: How To Insert Contents Of CSV File In Table

Sep 16, 2013

I'm starting with APEX, I would like to know how to insert the contents of a file. Csv in a table, how to map a file to a table, how to insert file contents. Xls in a table.

View 1 Replies View Related

Application Express :: Capture Contents Of Page - Copy To Clipboard

Jan 31, 2013

I just need to run javascript to capture the contents of the page( form) like labels and values in the fields using a COPY button to capture all the information available on the page or form to copy that to system clipboard so that information can be used into another system.

View 2 Replies View Related

Application Express :: Assign To Item List Of Value According To Contents Of Other Fields

Oct 18, 2013

Let me explain, I have two items in my page, the first is not required, if it is null the second field is a value list based on selection A (select from A. .. A. .. where . A..) but if the field is not null then the list of values ​​of the second field is based on the selection B (Select B from B where B. ..). 

I create a dynamic action on the event "change" of my first field,  on the "true action" I tried this: 

declare  vva_ternum Varchar2 (3): = null;Begin   APEX_ITEM.SELECT_LIST_FROM_LOV (2, : P26_TERNUM, 'LOV_RSPRODUIT_TERRITOIRE'); End;  

But it does not work!! 

View 5 Replies View Related

Backup & Recovery :: Dropping Tablespace Including Contents And Datafiles

Jun 17, 2013

1) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.

2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.

List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSTEM01.DBF
2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF
3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF
4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF
5 Full 997754 17-JUN-13 C:DATAFABCD01.DBF

3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"

4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.

SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
[code]....

how to recover those tablespace's datafile?What happened to earlier tablesapce backup?

View 9 Replies View Related

PL/SQL :: Use Of Views And Synonyms If Changes Made To Them Affects Original Table Contents

Mar 28, 2013

what is the the use of views and synonyms if changes made to them affects the original table contents?It is data insecurity know?

View 2 Replies View Related

Parsing Address And Name

Jan 25, 2012

I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender.

E.g:
Nguyen Van A
Nguyen Thi B

After parsing, the result will be shown as:

TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale

 Parsed.gif ( 4.94K )
Number of downloads: 2

I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".

2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]Address field is divided into Street, Group, Area, Ward, County fields
E.g.:No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

The result:

StreetGroupArea Ward County
No 6 Sum StreetGroup 8Area 2ABCD London

I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work.

View 1 Replies View Related

SQL & PL/SQL :: Parsing Name And Address?

Jan 25, 2012

I intend to write procedures/functions in PL/SQL to parse Name

field into various fields : Title, FirstName, MiddleName, LastName, Gender
E.g:
Nguyen Van A
Nguyen Thi B

After parsing, the result will be shown as:

TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale

I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender =

Female. Otherwise, Title = "Mr", and Gender = "Male".

2/ Another procedure/function is ParseAddress with the

requirement as:

Address field is divided into Street, Group, Area, Ward, County fields
E.g.:
No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

The result:

StreetGroupAreaWardCounty No 6 Sum StreetGroup 8Area 2ABCDLondon

I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->

it doesn't work.

View 3 Replies View Related

SQL & PL/SQL :: String Parsing

Aug 28, 2012

I have the following table A that contain one column "MYREC"

MYREC
1253 69889897 89884 891254
568989 89897891 321 698751232
1239892 123358798 7899 58123457

I need to parse the variable column, the issue is that the number of spaces.

The string might start with 2 or more white spaces, that I can get rid of with the LTRIM function..I'm having difficulties with the rest ):

View 5 Replies View Related

JDeveloper, Java & XML :: Parsing Through PL/SQL

Nov 21, 2011

oracle PL/SQL. I have almost finished this xml parsing task but their is one problem. Actually in our table there are more than 70-80 columns & due to that only I don't want to put the hard coded column name in my procedure, because if I will do that, the unnecessary procedure size will be increase(means line of code).Here is our procedure

Create or replace procedure loadMyXML(dir_name IN varchar2, xmlfile IN varchar2) AS
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl1 dbms_xmldom.DOMNodeList;
l_nl2 dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
node1 dbms_xmldom.DOMNode;
l_colName VARCHAR2(100);
[code]...

View 3 Replies View Related

SQL & PL/SQL :: XML Parsing Into Base Table

May 20, 2010

I want to load XML into base table using PL/SQL procedure.For that I have wrote procedure but that does not work well .

View 4 Replies View Related

Bind Variable In Parsing?

Aug 13, 2013

Identical statements  from this link :  Parsing in Oracle — DatabaseJournal.com d. The bind variable types of the new statement should be of same type as the identified matching statement. i am getting confuse here .. when  parsing occurs some links saying about bind variable.but official document  never said about bind variables.

View 3 Replies View Related

PL/SQL :: String Parsing Using Oracle

Sep 17, 2012

Oracle SQL.I need parsing Strings.

Example:

ID Name Address num
1 Peter 123 park st,223 park st 123,223

Answer
ID Name Address num
1 Peter 123 park st 123
1 Peter 223 Park St 223

View 4 Replies View Related

PL/SQL :: Parsing Date With MM.YYYY And Between

Apr 17, 2013

I got a table with a Date in it. Lets called it Testdate and an item called start month and end month.

The Item (varchar2) looks like

MM.YYYY f.e. 03.2012 and is a

I want to see all data between start and endmonth.

First I tried to convert the date from my orignial table:

SELECT distinct TO_CHAR(TESTDATE, 'MM'||'.'||'YYYY')
FROM mv_auswertung;This works:

TO_CHAR(TESTDATE,'MM'||'.'||'YYYY')
02.2012
12.2012
01.2012

But now I am trying to get all data between start and enddate:

Select *
from mv_auswertung
where
TO_CHAR(TESTDATE, 'MM'||'.'||'YYYY') between
to_CHAR(:P7_startmonth) and to_char(:P7_endmonth)

View 5 Replies View Related

Parsing Comma-separated String?

Jan 22, 2009

I have a string value like -- a,,b,c,d,e,f

Using just sql, I want to put each value of the above string in a different row. So the output should be --

a
b
c
d
e
f

using procedures it would not be that great but I want to do it just using queries.

View 1 Replies View Related

Query Parsing And Buffer Cache?

Apr 18, 2012

I want to know what exact process happens in oracle architecture when an update query is fired.

View 1 Replies View Related

JDeveloper, Java & XML :: Parsing In PL/SQL Without Any DBMS_XML Packages?

Jun 15, 2012

i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"

[code]....

View 29 Replies View Related

JDeveloper, Java & XML :: Parsing Containing Array Of Data?

Jan 12, 2011

I have to parse an XML and store the data in a table.

My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>

[Code].....

The output is :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

There will be cases when I will get multiple values for the columns conversationid, command, value.

<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>

[Code].....

In that case the output should be as :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2

Not sure how to go about XML parsing when I have multiple values.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved