I'm trying to figure out how to process a string of values that is being passed from Cold Fusion.
procedure test (Names in varchar2) is The Names variable from the Cold Fusion page would have values like Joe1,Joe2,Joe3,Joe4.
I need to loop through the Names variable (Joe1 then Joe2 then Joe3 and so on) and insert each one into a table. how to do that within the procedure?
I have a requirement to extract data from an XML formatted string that is passed as an IN parameter to a database stored procedure. I do not have a table to store the XML, nor an XML schema, nor a namespace, just the XML string. While new to XML, I do understand the basics. Using XMLTable I am able to parse out the simplest of XML data. My problem is this; attempting to extract data from nested XML nodes results in the error
'ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.
Testing a solution example I found on this site by Barbara Boehmer, I still get the ORA-19279 error. I suspect it may be because I have version 10g while she has version 11g. Her solutions is from #msg_514154. Here is that code that gives me the error:
SCOTT@orcl_11gR2> SELECT x."deptno"
2 FROM (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
3 XMLTABLE
4 ('/departments/deptno'
5 PASSING XMLTYPE (xml_data.col)
6 COLUMNS
7 "deptno" number path '/deptno') x
8 /
deptno
----------
1
2
2 rows selected.
As I said, this example generates the ORA-19279 error. Need processing XML data from an IN parameter?
I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.
OPEN p_cur FOR v_query
USING p_ht_nm, p_ht_treat_source, var_cycle (1),
var_cycle (2),
var_cycle (3),
var_cycle (4),
var_cycle (5),
var_cycle (6),
var_cycle (7),
[code]...
We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.
QUERY:
/* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */
INSERT INTO z_ca_get_lot_id
SELECT DISTINCT attrbts.lot_id
FROM (SELECT DISTINCT lot_id
FROM z_alv_cert_lot_attrbts_syn
WHERE NAME = 'Heat'
AND UPPER (text_value) = :p_ht_nm) attrbts,
[code]...
I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]
View 11 Replies View RelatedDifference between cold backup and cold cloning?
View 2 Replies View RelatedLong running data extract executed from SQL SERVER into Oracle.Process runs long time then chokes. What breaks after the process is running ?
03-AUG-2010 05:00:10 * (CONNECT_DATA=(SERVICE_NAME=MyDB)(CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtshost.exe)(HOST=SQLSERVER)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.xxx)(PORT=4498)) * establish * MyDB * 0
03-AUG-2010 05:00:11 * (CONNECT_DATA=(SERVICE_NAME=MyDB)(CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtshost.exe)(HOST=SQLSERVER)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.xxx)(PORT=4500)) * establish * MyDB * 0
03-AUG-2010 05:00:12 * (CONNECT_DATA=(SERVICE_NAME=MyDB)(CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtshost.exe)(HOST=SQLSERVER)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.xxx)(PORT=4502)) * establish * MyDB * 0
03-AUG-2010 05:00:14 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)
(SERVICE=LISTENER)(VERSION=153092864)) * status * 0
[code]....
In Oracle Apex 4.1,The Leave_transaction Table has the following Fields,
1.Leave_id
2.Emp_name
3.From_date
4.To_date
5.Remaining_days
The Emp_Master Table has the following columns,
1.Emp_id
2.Emp_Name
3.Remaining_days
Holiday_master table has the list of holiday dates as "From_Date"
I have the form based on the Leave_Transaction Table, and I have created the Process, as "On-submit-after computations and validations" and posted the following PLSQL code,
declare
days number(3);
ex_days emp_master.remaining_days%type;
new_rem_days emp_master.remaining_days%type;
begin
[code]....
If the Dates is between from_date and To_date comes in Saturday and sunday and/or if any Date is exist in the Hpliday_master table it will exclude and return the count(*) remaining dates, For example,
If the From_date is 04-may-2012' and To_date is 08-may-2012,
Here the dates 5th may and 6th may are "saturday" and "sunday"
and if any date between From_date and To_date is exist in Holiday_Master Table i.e say here it is 07-may-2012, Then the remaining dates are(excluding sat,sunday and dates in holiday_table),
04-may-2012,
08-may-2012.
so the count(*) is 2.
I am using the above code but still it returning 5,I think this
...where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holiday_start from holiday_master))
code is not working.
I have a column in a table that contains a string seperated by .
e.g.
IT.HARDWARE
IT.APPS
IT.SOFTWARE
I would like to split the two values out on two columns e.g.
Column1 - IT
Column2 - Hardware
etc.
I have data something like this:
Sample DateWITH DATA AS ( SELECT 'AAAXXXX IO BLUEEXPRESS' LIST FROM dual UNION ALL SELECT 'BLUEEXPRESS AAAXXXX IO BLUEEXPRESS' FROM dual UNION ALL SELECT 'DDDDD BLUEEXPRESS AAAXXXX' FROM dual UNION ALL SELECT 'DDDDD DDDDD AAAXXXX' FROM dual UNION ALL SELECT 'DDDDD BLUEEXPRESS AAAXXXX NO CARBON' FROM dual UNION ALL SELECT 'NO CARBON
[code]....
The above result depends on the following rules:
- Replace BLUEEXPRESS into BEXPRESS
- Remove the term NO CARBON (See row no 6)
- Reduce all multiple space into single space (see last record).
So far I create separated queries for replacing BLUEEXPRESS into BEXPRESS and replace NOCARBON term but I don't know how to do it in a single shot as well as stuck on scenario to remove multiple spaces and put single space.
Objective: I need to compile a final string by concatinating the unique values from different strings.
Here is the script to create tables and data.
Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');
COMMIT;
select * from temp_acronyms;
ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.
my final string should have the values as below
ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY
I am trying to update the greatest value in a column from a string of other column.
Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only
so for that I had written query as follows:
update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))
The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.
CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;
[code].....
Query Output :
1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh
Expected Output :
1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh
Expectation here is duplicate values should not be repeated.
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a cursor, whose sql is returning seven rows with these values:
9
4
4
9
7
9
4
i open cursor and fetch these values into variable as shown
OPEN id_search FOR l_sql_stmt;
LOOP
FETCH id_search INTO l_eve_id;
if l_eve_id != l_eve_id_prev then
l_eve_id_str := l_eve_id_str || ' , ' ||l_eve_id ;
[code].......
but i want only the distinct values in l_eve_id_str , i.e.,
l_eve_id_str := 9,4,7
What this code is doing is creating a string with all the values
l_eve_id_str := 9,4,4,9,7,9,4
How to remove duplicates from this string?
I need to extract values from string to variables as below.
declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or varchar2(100);
l_s varchar2(100);
l_ot varchar2(100);
l_pre varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/
But here challenge is order of alignment change dynamically. ex as below.
str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';
So how to extract Acknowledgement to l_a,Order requester to l_or ...
My DB version is 10.1.0.5.0
I want extract the values from the string using below query but i am unable to bring the correct result.
WITH t AS ( select '123,1,3,22' col FROM DUAL
UNION ALL
SELECT '123,,2,1' FROM DUAL
UNION ALL
SELECT '5,1,2,,' FROM DUAL
)
[Code]...
My required result like below.
123 1 3 22
123 2 1
5 1 2
get required result using regular expressions.
I would like to extract only the values from the XML string. The xml string looks like
<addressId>1</addressId><addressSource xsi:nil="true"/><addressInfoName xsi:nil="true"/><addressLine1>245 Murray Ln SW Bldg 14</addressLine1><addressLine2 xsi:nil="true"/><addressLine3 xsi:nil="true"/><addressLine4 xsi:nil="true"/><phoneNumber xsi:nil="true"/><phoneNumberExt xsi:nil="true"/><city>Washington</city><county xsi:nil="true"/><stateOrProvince>DC</stateOrProvince><ZIPCode>20528-1002</ZIPCode><country>USA</country><congressionalDistrict xsi:nil="true"/></address>
And the expected output is
245 Murray Ln SW Bldg 14
Washington
DC
20528-1002
USA
I need to extract only the values from the XMl string using sql.
I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:
#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0
Here, I want to update the above field value to a value in the form:
#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0
This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).
I have a problem with Between clause used in where statement to compare two string variable.
Query is like this,
select item_code, item_deacrption
from itm_master, invoce_det
where im_code = item_code
AND invd_item_number BETWEEN (:startNum) AND (:endNum)
Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.
now invd_item_number has one value '001003002001'
if we give :startNum = '001003001002' and :endNum = '001003004006'
:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.
it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).
But how can i get around this as i don't need that value to be fetched.
I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin
[code]....
Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');
I want a code something of this sort:
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
We have generated a fancy custom interactive report, in which the columns of string type, but can hold string, numeric or date values. However the type of values are uniform for a particular column. The end users wants to use the numerical values for generating charts, but, being a string column (though the data is numeric), it throws an error and they could not generate the chart. Is there any way to make use of this string column type numerical data for generating chart?
The current version of apex is 4.1.
A computation after submit pl/sql function process to trim off the first part of the string (CQ..) within the list manager values. Support for example the list manager contains values such as
CQ..SAMPLE1..TEST1CQ..SAMPLE2..TEST2CQ..SAMPLE1..TEST2
The computation process should trim off the first part(CQ..) and should return the list manager value as SAMPLE1..TEST1SAMPLE2..TEST2SAMPLE1..TEST2 Oracle APEX 4.0.2 is the version and Oracle 10g r2 is the database.
Is it possible to pass a nested select statement as a parameter to a procedure? In other words, I am currently successfully doing this:
SELECT BASE_PAY INTO N FROM EMPLOYEES WHERE ID=p_ID;
SetPackageValueProc(PkgID,'YearlyBasePay',N);
And I'd like to do something along the lines of this:
SetPackageValueProc(PkgID,'YearlyBasePay',(SELECT BASE_PAY FROM EMPLOYEES WHERE ID=p_ID));
I keep on getting the Invalid cipher error whenever I do a call to DBMS_CRYPTO.Decrypt The flavor I am calling is the one taking the parameter list below:
(dst in out Blob,src in Blob,typ in pls_integer, key in raw,iv in raw)
So the issue happens on the third parameter: typ. It is a constant defined in the package representing the decryption algorithm I intend to use (AES256 in my case). So the call goes something like that in my pl/sql
dbms_crypto.Decrypt(vdecrypted, vcrypted,dbms_crypto.encrypt_aes256,vkey);
but it won't work. I also tried setting a variable like :
my_algo := dbms_crypto.encrypt_aes256;
and using it in the call but that gave the same result.
I have an oracle 8i recovery scenario. I have database cold backups of MONDAY and WEDNESDAY evenings. backup contains control files, data files, redo files
Database runs in Archive log mode and all archives of the month are available. Hard disk crashed on SATURDAY
all archive logs are still available at different location. Control files are not backed up to trace or file name or automatically means no backup control file (except ones in cold backups described above)
restoring all files from cold backup of either MONDAY or WEDNESDAY (I recovered to Wednesday) leaves nothing to recover. Querying V$RECOVER_FILE returns nothing. All log sequences higher to the number nnn displayed as result of ARCHIVE LOG LIST are present at correct location in correct format
My question is how do i ROLL Forward the Database from WEDNESDAY to SATURDAY in the above scenario.
I've got a database I need to restore from a cold backup, but I don't have a copy of the multiplexed members of the redo groups - each group has 2 members, and we've only got a backup of the first.
I know this won't stop the database from starting, and I can recreate them, but for ease can you copy and rename a redo log file from a multiplexed copy to correct location like you can with a control file?
There is schedule cold backup was not taken successfully due to some DBF files are skipped and it would not copied to destination location? destination location is same server itself not for another remote server.
Total - 35 DBF files,
skipped - 4 DBF files
what could be issues? what are the points should be troubleshooting?
oracle 10.2.1 under windows 7 , today my friend format the server , but he have backup cold backup actually he take backup for all c:\ drive and i found that there is a oradata dir in this backup , so now i install new operating system win 7 and then i will create a database with the same name and then should i do a NID and restore the backup (cold)
how to do that ? i mean if i have database 10g under windows and this o.s is formatted we have backup of all c:\ drive which contain all oradata dir ? how can i restore this database
the database size is 3GB and contain the same default tablespace system,sysaux,undo,temp,users?
I have developed a RDF report which when linked to my application called OTM, it returns data which are not expected.
My report has two lexical parameters which are of date variables.
When i run the report from my application it asks for the parameter valeus and when i specify it, the report is not returnign the data within the date range i specified. it returns me all the data.
