SQL & PL/SQL :: How To Generate Seq Numbering

Jun 10, 2011

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?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Numbering On Break

Mar 24, 2010

I have a query with output as:

CUST PROD
Customer1Product1
Customer1Product2
Customer1Product3
Customer2Product1
Customer2Product2
Customer3Product1The output required is

Number CUST PROD
1 Customer1Product1
Customer1Product2
Customer1Product3
2 Customer4Product1
Customer4Product2
3 Customer5Product1

For every change in customer i need to put a new serial number.

View 2 Replies View Related

Reports & Discoverer :: Page Numbering

Nov 15, 2010

My table contains following result:

DeptId Empid
001 1
001 2
001 3
002 1
002 2

So I want to print page number like from first page 1/3 then 2nd page 2/3 , on 3rd page 3/3 Once grouping change for Dept 002 then page count should reset like 1/2 then 2/2

View 6 Replies View Related

Client Tools :: Line Numbering For SQL Developer

Aug 28, 2013

I have a question regarding SQL Developer. When opening a SQL Developer worksheet, there are two different panes or work areas; the upper pane contains the query code and the bottom pane contains the query results (output). Using the

Tools===Preferences tab, I was able to enable line numbering for the top pane (code) but I haven't figured out how to enable line numbering for the bottom pane (results area). how to enable line numbering for the bottom pane?

View 3 Replies View Related

SQL & PL/SQL :: Generate A CSV Report?

Feb 18, 2012

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

View 25 Replies View Related

SQL & PL/SQL :: Generate Different Combinations Of A Value

Sep 12, 2011

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

View 2 Replies View Related

SQL & PL/SQL :: Generate XML From Proc

Oct 16, 2013

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 Related

SQL & PL/SQL :: Unable To Generate A Fk

Mar 1, 2011

I 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]....

View 2 Replies View Related

Generate A Text File

Apr 28, 2008

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!!!

View 11 Replies View Related

Generate Report From OEM Grid

Feb 6, 2012

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 Related

SQL & PL/SQL :: To Generate Procedure Execution LOG

Sep 3, 2013

I 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 Related

SQL & PL/SQL :: How To Design Subprogram Which Can Generate Pk Value

Sep 15, 2011

i mean which can generate a primary key when the table name and column name are passed as parameters

View 7 Replies View Related

SQL & PL/SQL :: Random Number Generate

Jul 6, 2010

I 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.

View 27 Replies View Related

Forms :: Generate Bar Code In 6i?

Nov 2, 2011

i want to generate bar code in forms 6i but i don't know the code.

View 3 Replies View Related

SQL & PL/SQL :: Generate Unique Combination

Dec 17, 2011

I 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.

View 31 Replies View Related

SQL & PL/SQL :: Generate Dynamic Unpivot?

Oct 16, 2012

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.

View 9 Replies View Related

Reports & Discoverer :: Generate PDF 11g?

Mar 28, 2012

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.

View 3 Replies View Related

How To Generate Unique Identifier

Jun 21, 2012

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.

View 23 Replies View Related

PL/SQL :: Generate Alphabet Sequence Like A-Z Followed By AA-ZZ

Mar 8, 2013

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]......        

View 2 Replies View Related

PL/SQL :: Generate Hours Range

Oct 4, 2012

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).

View 5 Replies View Related

SQL & PL/SQL :: Generate A Report Much Like A Program?

Jun 14, 2012

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]...

View 9 Replies View Related

SQL & PL/SQL :: Generate Records For Range Of Each ID

Sep 13, 2013

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

View 4 Replies View Related

Generate Report From OEM Grid 11 Or RMAN

May 17, 2013

*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

View 4 Replies View Related

How To Generate Email Based Trigger

May 9, 2011

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.

View 1 Replies View Related

Can HINT Append Generate Error

Mar 1, 2007

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!!

View 8 Replies View Related

Generate Last Seven Days Batch Run Times

Jan 8, 2013

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]...

View 3 Replies View Related

PL/SQL - Generate Update DML With Special Character?

Dec 1, 2010

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>

View 1 Replies View Related

Generate AWR Report Between Two Timings (Not Snapshot ID)

Aug 12, 2013

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]....

View 4 Replies View Related

SQL & PL/SQL :: Script To Generate Dynamic Columns

Mar 6, 2011

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?

View 39 Replies View Related

SQL & PL/SQL :: How To Design Subprogram Which Can Generate Primary Key Value

Mar 22, 2012

how to design subprogram which can generate a primary key value whenever the table name and column name are passes as parameter.?

View 19 Replies View Related







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