SQL & PL/SQL :: XML Parsing Into Base Table
May 20, 2010I 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
ADVERTISEMENT
PL/SQL :: Join Between Table With User Based Content And Base Table
Jul 30, 2012I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
SQL & PL/SQL :: Find Out Base Table Of Synonym?
Jun 10, 2011How to find out the base table of a synonym?
View 3 Replies View RelatedSQL & PL/SQL :: Create View Without Base Table?
Jun 25, 2012How to create a view without base table . some example?
View 8 Replies View RelatedForms :: Changing A Block Not Base Table
Oct 15, 2012How do I change the status of a block to change?
View 7 Replies View RelatedServer Administration :: How To Find The Base Table Of A View
Aug 15, 2011How to find the base table of a view,such as a view:
create or replace view vw_test as select * from tb_test;
Is there dic view to get the base table of view?
PL/SQL :: Update A Base Table Using Updatable Materialized View?
Sep 12, 2013I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies View RelatedSQL & PL/SQL :: Trigger Fired - Final Update To Base Table Not Happening
Jul 1, 2011In my DB,We have a table rqst_list
Create table rqst_list(id Number, --(PK)
pkg_name varchar2(100),
status varchar2(100))
When a record is inserted into this table(rqst_list), a trigger is fired which calls the package which is there in pkg_name.The function does some function.If there are no exceptions while executing the package, then SUCCESS needs to be updated in the table for the id for which the trigger got fired.I have used AFTER INSERT ROW level trigger.
But,the final UPDATE (UPDATE the rqst_list table to SUCCES/FAILURE)to the base table inside the package is not happening.
Security :: Unable To See Data In Base Table Where Can Be Viewed In Synonym?
Sep 9, 2011I have one schema argus_app where there is a table cfg_enterprise. There is a view v$cfg_enterprisewhich selects data from cfg_enterprise and there is apublic synonym cfg_enterprise for v$cfg_enterprise.
When I login to argus_app and select from cfg_enterprise there is no data where as i log in to sys and select I can see data.
SQL> conn /as sysdba
Connected.
SQL> select enterprise_name from cfg_enterprise;
ENTERPRISE_NAME
[code]...
what would be creating this problem.
Application Express :: Select Some Data From Table Base On SYSDATE?
Sep 6, 2012I'm trying to select some data from table base on SYSDATE. The below query does not return any data.
My query is:
select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate
why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?
SQL & PL/SQL :: FIFO Base Query
Sep 30, 2010select code,inv_date,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_amount,inv_nofu
from retreport order by inv_co_code, inv_fnd_Code,inv_date
STAT INV_DATE CODE NV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU
----- ------ -------- ----------- ------------ -------------- ---------------------- ----------------------
I 28/06/2010 117 13 CAD 1 MCB 15104708.75 148375.7308
I 01/07/2010 13 CAD 1 MCB 2804.7464
I 30/08/2010 117 13 CAD 1 MCB 700000.00 6882.2380
I 30/08/2010 117 13 CAD 1 MCB 9500000.00 93401.8018
R 31/08/2010 117 13 CAD 1 MCB 39315.8646
R 08/09/2010 117 13 CAD 1 MCB 24515.9089
6 rows selected
STAT I = IN STOCK
STAT R = OUT STOCK
I need FIFO Query First in Fist out (INV_NOFU)
STAT CODE INV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU BALACNE
----- ------ ----------- ----------- ------------ -------------- ---------------------- ---------------------- ------------
I 117 13 CAD 1 MCB 15104708.75 148375.7308 148375.7308
R 117 13 CAD 1 MCB 39315.8646 109059.8662
R 117 13 CAD 1 MCB 24515.9089 84543.8773
I 13 CAD 1 MCB 2804.7464 87348.6237
I 117 13 CAD 1 MCB 700000.00 6882.2380 94230.8617
I 117 13 CAD 1 MCB 9500000.00 93401.8018 187632.6635
Upgrade RAC From Base Version To Patchset 3
Oct 4, 2010I have one query regarding ORACLE 10g RAC.
I have installed 10gRAC [10.2.0.1] on VMware.
now I have requirement to upgarde it this existing RAC setup to 10.2.0.4.
options for me the steps or sequence I need to follow in upgrade path? like first needs to upgarde crs,then fro ASM and lastly for DB something like this.
SQL & PL/SQL :: Alphanumeric Sorting Column Base
Jun 18, 2010I am trying to sort columns base on cluster and alpha numeric field.
Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1
Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this
Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1
Forms :: Editing Base.htm File?
May 4, 2010can I add a session (php code) to base.htm file?
if yes, what i have to do?
Forms :: How To Base A Block On Left Join
Feb 5, 2012I am trying to develop an application of cars. The car have marques example :
TOYOTA,HUNDAI,CHEVROLET
each mark have families example: TOYOTA have Hilux, yaris corola, CHEVROLET have opra,...etc. Each family have a lot of models example: hilux have h2kn-clim,.. etc. And finally there are some options witch are generally in all cars example Radio-k7,air-conditioner ... etc.
option 1..n-----------------1..n model the relation call(opt_mod)
i did develop the block of marques (master) and the block of families (detail) in a form 1. i did develop the bock of models(master) in form 2 and the is no problem. but i want to add to form 2 the block of (opt_mod) but the user did tell me that he want to to see all options with check boxes .
As a solution of this problem i want to build a block on LEFT JOIN between table :option and table :opt_mod
Parsing Address And Name
Jan 25, 2012I 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.
SQL & PL/SQL :: Parsing Name And Address?
Jan 25, 2012I 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.
SQL & PL/SQL :: String Parsing
Aug 28, 2012I 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 ):
Server Utilities :: Daily Base Backup File
Apr 11, 2012I made small Inventory software for Medical store. Now I want daily base data in DMP file. How to make current date in DMP file don't need all.
I mean I have 30 tables in oracle sql . They are daily update with new entry and some table has date column and some not. Actually I want to send daily Data via mail.
Forms :: How To Change Base HTML Page For Oracle
Feb 29, 2012how to change base html page for Oracle Forms. I use Weblogic Server. Base html page - I mean this common for all forms as a background html page with "Oracle Fusion Middleware" text. I was looking something about this and I found that it is theoretically possible, but all traces reached me to the <Oracle_Home_path>forms/java directory which have only jars, with .class files.
View 1 Replies View RelatedServer Administration :: Change Datatype To HEX Notation / Base 64
Jul 10, 2013I am currently in the process of migrating our database from US7ASCII to AL32UTF8 using DMU.
I am stuck at a point where I have encrypted data that, when the conversion happens, will be destroyed. Oracle suggests to move the data which is currently stored in VARCHAR2 datatype, to a "character set safe way" like hex notation or base64 before converting.
SQL & PL/SQL :: PCT Is Not Enabled While Creating Mview / Though Base Tables Are Partitioned
Apr 4, 2011I created a mv for one of the partitioned tables but on viewing the mv capabilities it still shows PCT is set to 'N'.
create materialized view MV_summary_SEC
refresh fast
start with sysdate
nEXT SYSDATE + 1/24
enable query rewrite as
[code]....
If i remove the sub query and create the mview like this,then PCT is enabled.
create materialized view MV_summary_SEC
refresh fast
start with sysdate
nEXT SYSDATE + 1/24
enable query rewrite as
select PERIOD , SUM(SUM_WEB_HITS)
from summary ,date_table
where PERIOD >= DATE_TABLE.CUR_DATE
group by PERIOD
Is it simply because oracle doesn't support PCT if the definition contains subqueries ?
JDeveloper, Java & XML :: Parsing Through PL/SQL
Nov 21, 2011oracle 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]...
SQL & PL/SQL :: Parsing BLOB Contents
Mar 28, 2013I 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].....
Bind Variable In Parsing?
Aug 13, 2013Identical 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 RelatedPL/SQL :: String Parsing Using Oracle
Sep 17, 2012Oracle 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
PL/SQL :: Parsing Date With MM.YYYY And Between
Apr 17, 2013I 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)
Performance Tuning :: Creating Index On Base Tables Of A View?
Dec 9, 2010I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?
The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.
Parsing Comma-separated String?
Jan 22, 2009I 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.
Query Parsing And Buffer Cache?
Apr 18, 2012I want to know what exact process happens in oracle architecture when an update query is fired.
View 1 Replies View Related