Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
How i can get all possible combinations of a given value using SQL or PL/SQL
for example:
1234 - input value
4321
2314
2143
3412
and so on
Earlier, we had a discussion how to generate the permutations. generating the code for combinations
For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs
1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5
Total of 10 combinations
We are having this problem of entering duplicated inventory parts in the system which physically refers to the same part. Need to create a select query to search through the list of inventory parts to find out existing parts with similar names.
Requirement is to have a search on a specific character set and it should list even records with 50% of the characters they have entered. Characters can be anywhere in the part name with any case.
For example: input of 'abcd1234' should return the parts with the following name... A nut 234 R49SE B12 A22Hub 156 65 a2 But not... abc 89998AK 47Xbox 360Mauser K98b
I'd like to set up a new Oracle Materialzed View to automatically default to refresh FAST every hour. I have no trouble setting this up in the CREATE MATERIALIZED VIEW statement. However, we will occasionally be performing table maintenance on the table referenced by the MV. At that time I would like to use dbms_mview.REFRESH to execute a refresh ON COMMAND (either F or C depending on the operation weperformed on the table). I have read that this will override the automatic refresh every hour that was set up for the MV when it was created. However - wil the override be permanent? Or will the MV continue to automaticaly FAST refresh every hour after the ON COMMAND refresh is completed?
View 1 Replies View RelatedI'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.
select COUNTRY_CODE, STATE_CODE, count(*) from
(select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE
where DRIVING_LICENCE is not null
and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
group by COUNTRY_CODE, STATE_CODE
The output is okay...for example I get these results:
INTINT
NZSI
NZINT
AUSTINT
NZNSW
NZ <null>
However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?
I have a requirement to generate a report in csv file, attached scripts, sql query and actual output and expected output.
In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.
I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.
oracle version : 10.2.0.1.0
I want to generate the 3rd digit to be sequence base on two digit infront. for example:
current data grep direct from table
----------------------------------
data line_number
-----------------
data1 1.1
data2 1.1
data3 3.1
data4 4.1
data5 5.1
data6 5.1
data7 5.1
I want to generate is append in the 3rd digit as below:
data line_number
-----------------
data1 1.1.1
data2 1.1.2
data3 3.1.1
data4 4.1.1
data5 5.1.1
data6 5.1.2
data7 5.1.3
How I can get this?
I have a requirement to generate the XML file from a pl/sql procedure. The methods which I have searched are either generating the XML from a single query or they are explicitly adding the nodes and elements. I need the method where I can dynamically append child nodes to the file.
View 12 Replies View RelatedI am unable to generate a fk in the following script..
DROP TABLE PD1 ;
CREATE TABLE PD1 (APP_CODE NUMBER,APP_DTLS VARCHAR2(10),
CONSTRAINT APP_CODE_PK PRIMARY KEY (APP_CODE)
);
DROP TABLE PD2 ;
CREATE TABLE PD2 (LAND_CODE NUMBER,PVT_LAND VARCHAR2(10),
GOV_LAND VARCHAR2(10),OWND_LAND VARCHAR2(10),LEASED_LAND VARCHAR2(10),
CONSTRAINT LAND_CODE_PK PRIMARY KEY (LAND_CODE)--,
-- CONSTRAINT APP_CODE_FK FOREIGN KEY (APP_CODE) REFERENCES PD1(APP_CODE)
[code]....
i have be requested to create a .txt file. Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far,
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not sure how to do that and how to generate it in a txt file?! I have to produce it in a script and not by a Export/Import wizard in sql server mangement studio!!!
Can we collect information(generate report) about user sessions connected, database hit ratios, memory usage and other system metrics of all target databases from OEM grid control repository database. Which tables/views in repository DB will be holding these information.
View 1 Replies View RelatedI have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
View 21 Replies View Relatedi mean which can generate a primary key when the table name and column name are passed as parameters
View 7 Replies View RelatedI have 2 question....
1. i have a table and i want to pick data from one field randomly..
i write this query for this purpose...
SELECT SUBJECTIVE_QUES_PK FROM
(SELECT SUBJECTIVE_QUES_PK FROM PMS.SUBJECTIVE_QUES
ORDER BY SYS.DBMS_RANDOM.VALUE)
WHERE QUES_TYPE_FK=3
AND ROWNUM<=5;
2. can i write this query in forms 6i..
i know that this question not relevant but if i write it in forms section then i will be duplicate.
i want to generate bar code in forms 6i but i don't know the code.
View 3 Replies View RelatedI need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like
A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90
and I need to find among all letters I have which combination is equal or closest to my target.
A:10
B:20
C:20
D:20
E:30
F:40
G:250
IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:
A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F
To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.
I want to be able to generate unpivot for 1 row of data dynamically.what i am trying to do is based on this linkI just need to be able to pass the table name in format : SCHEMA_NAME.TABLE_NAME and the query should unpivot the data for 1 row. (Will always be 1 row, just need to transpose the column names and the values for 1 row of data)
I made the below query so far. But I am getting an error and can't figure out a way to fix it.pass any table name from your database in the define step
define TAB_NAME='SCOTT.EMPLOYEE'
WITH sel_col AS
(
SELECT DECODE (data_type,
[code]...
ERROR at line 33:
ORA-00904: : invalid identifier
Its the last line of the code. The same seems to work in the select part but not in the IN part.
P.S: I cannot do PL/SQL - as i simply dont have access.
during the migration to 11g we had a problem with pdf font.
In 10g oracle report the pdf are generated correctly while in 11g all the character are shrink in the pdf file.
I attached the same file generate with oracle reports 10g.
Is there a way to generate a unique identifier(length 8), which can contain numbers (0-9) and letters(a-z) in pl/sql or sql ?
Note :- in oracle 9i.
Oracle 11g r2 version:
I tried to generate alphabet sequence like A-Z followed by AA-ZZ followed by AAA-ZZZ etc... using this query.
WITH chrset AS
( SELECT CHR (ASCII ('A') + ROWNUM - 1) alph
FROM DUAL
CONNECT BY ROWNUM <= 26),
t AS
(SELECT alph FROM chrset
[code]......
I have a table with a date field. This field storage dates with hours like this:
01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...
I have to generate a report with the day and the frequency :
__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3
The block of the hour must be 1 hour (8-9,9-10 and so on)
how generate the hours range.
My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).
I need to generate a report much like a program guide from the script attached. My problem is that my script works, but it's wrong, all the information is wrong. Here's the two queries I came up with;
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
And
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
write the query for the following requirement.I need to generate the records for the range (Difference between From_val & to_val) ) for each ID
create table test_base(id varchar2(20) , from_val number , to_val number);
insert all
into test_base values ('A', 6,10)
into test_base values ('B', 22,30)
into test_base values ('C', 123,130)
into test_base values ('D', 852,860)
into test_base values ('E', 30 ,30)
select * from dual ;
[code]....
No need to generate any thing for E as the difference is equal to Zero
*How can we generate the report of backup status,tablesapce(usedf,free space) for all the databases from OEM or RMAN*
1.)we need generate the report of tablespace used,free, archive...
2.)we also need we generate the Backup status report also
I have a table in some db called retailer. I want to generate an email based trigger. What i want to achieve is as follows
I want to generate an email based trigger. What i want to do is that i should store the current retailer id which is until this point and then on each further insertions in retailer id i want that some trigger or script is running in background which is storing the retailer id. When the retailer id advances by 45 then a trigger should be generated saying that retailer id has advanced to 45 numbers from this date.
I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.
The macro operations made by the updater (through sql code) are:
--create a copy of actual table and rename with '_old' suffix
--create new table, with indexes and constraints
--insert data into new table from old tables
Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.
To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!
BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!
I would the query to do the following:
1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer
select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...
I have a pl/sql question about interpreting special characters in pl/sql. I am generating an update statement and get an invalid character when the customer name has an ampersand in it (&). I�m now generating the update statement to include the customer name cut up with concatenation characters surrounding the ampersand, for example �CSCP-C�||�&�||�E�. However, pl/sql is still not executing the update.
Here is the code.
SET PAGES 2000;
SET LINES 200;
SET SERVEROUTPUT ON;
SET VERIFY OFF;
spool update_table.log ;
[code]....
The RESULTS follow. Of course, I can copy this statement and execute in sqlplus without a problem.
---------------------------------------------
- UNKNOWN ERROR CONDITION
SQL = UPDATE mytable SET CUSTOMER_CORE = ('DSCP-C' || '&' || 'E') WHERE REC_ID = 170795889
;
---------------------------------------------
PL/SQL procedure successfully completed.
SQL>
I have a requirement to create a generic script to generate awr report on the basis of two timings . We are planning to do load test on multiple server. The user will key in the start time and the end time of the load test and the script should round off the user entered time to the nearest snapshot id and generate a awr report. I have tried using the dba_hist_snapshot table fields begin_interval_time and end_interval_time column. However it failed.
The servers are running on 11g as well as 10g and few are in RAC and few single instances.
/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIME
UNDEFINE TO_TIME
[Code]....
Is there any sql script to generate dynamic columns? pivot still needs the programmer to know the data to generate columns just like this.
select * from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
how about if i don't know the data in fk_department. is this possible in sql?