SQL & PL/SQL :: Sequence Creation For Results (Values)

Jan 25, 2013

How to create the sequence which gives following values

1
0
1
0
1
0

when we excite the following query , it has to give the above results

select seq.nextval from dual

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation

Feb 22, 2010

I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :

CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER

I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......

I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........

The code is given below:

CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Creation Of Sequence To Varchar

Dec 7, 2011

how to create sequence to varchar and how see that sequence

View 2 Replies View Related

SQL & PL/SQL :: Table Creation With First Column To Be Sequence

May 6, 2010

I want to create a table, whose first column , patient_id, should be a sequence.

View 9 Replies View Related

SQL & PL/SQL :: Assign Sequence Name During Table Creation?

Aug 22, 2011

is it Possible to Assign the Sequence Name During the Table Creation.

View 1 Replies View Related

SQL & PL/SQL :: Partition Creation Based On Other Table Values

Feb 12, 2013

i am trying to create Procedure which will create the partitions based on the other table date values one per each day.

CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);

[Code]..

It is not creating the partitions and even not giving any errors.

View 4 Replies View Related

SQL & PL/SQL :: Query To Store Results - Update Values In Another Table?

Feb 22, 2010

I have a query like this -

SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;

When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.

Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.

View 6 Replies View Related

Inserting Values From A Sequence?

Jun 10, 2011

I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'

Created a sequence starting with 1...

and inserted single row, and multiple rows using loop also; by using below code -

insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;

but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'

View 1 Replies View Related

SQL & PL/SQL :: Sequence Generating Random Values After Increment By 1

Jan 28, 2011

DECLARE
P_LAST_UPDATE_DATE KPC_MMS_STD_CHKPOINTS.LAST_UPDATE_DATE%TYPE;
BEGIN
INSERT INTO KPC_MMS_STD_CHKPOINTS (CHK_POINT_CODE,CHK_POINT_DESC,CHK_POINT_FREQUENCY,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
VALUES (:P13_CHK_POINT_CODE,:P13_CHK_POINT_DESC,:P13_CHK_POINT_FREQUENCY,:P13_CREATED_BY,:P13_CREATION_DATE,
:P13_LAST_UPDATED_BY,:P13_LAST_U PDATE_DATE);
COMMIT;
END;

View 1 Replies View Related

ASM Disk Groups Creation After ASM Instance Creation

Jul 9, 2012

During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again

The Grid infrastructure was deinstall one time and still the same issue.

View 4 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

SQL & PL/SQL :: Getting Duplicate Results

Feb 7, 2012

I am getting duplicate results..I have been at it for hours now.This is what I have:

DECLARE
v_course_id classes.course_id%TYPE := :course_id;
v_instr_id classes.instr_id%TYPE := :instructor_id;
[code]...

The output I get is this (There is only 1 of each in the database)

Class ID: 1 Status: Enrolled Name: John O'Reilly
Class ID: 1 Status: Enrolled Name: Natacha Hansen
Class ID: 1 Status: Enrolled Name: Reed Jetto
Class ID: 1 Status: Enrolled Name: Janis Greenberg
Class ID: 1 Status: Enrolled Name: Vishal Singh
Class ID: 1 Status: Enrolled Name: Francis Hamilton
[code] ...

View 2 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

Calling SP Results In ORA-00911

Apr 20, 2007

I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.

call get_all_customer_orgs;

The get_all_customer_orgs procedure is defined as.

create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor)
is
begin
open p_recordset for
select * from customerorgs;
end get_all_customer_orgs;

I don't understand what the invalid character could be.

View 5 Replies View Related

Any Way To Pivot Results Of A Query?

Apr 23, 2007

Is there anyway to pivot the results of a query?

so if i have:

SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP

And it give the following output:

A 10
B 50
c 24

Is there anyway to put into this format?

A B C
10 50 24

I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!

View 1 Replies View Related

Select Field Where Results Are The Same

Feb 26, 2008

I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.

lets see .. so a count of records where widget = widget or something along those lines.

View 9 Replies View Related

Getting Total Results Count

Aug 27, 2010

I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".

Currently I am doing this by submitting a second query:

select count(*) from (<previous query criteria>)

Is there a better performing approach I could be using?

View 4 Replies View Related

SQL & PL/SQL :: Display Results Using CASE WHEN?

May 24, 2011

I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).

What do I need to do to display 'data' if 'data2' contains rows?

WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,

[Code]....

View 11 Replies View Related

SQL & PL/SQL :: Results Different After Using Order By Clause

Mar 18, 2011

Result are different after adding the Order By clause in below query.

Query

select sum(wpsv1.primary_qty) detail_qty,
wpsv1.from_subinventory subinventory,
wdd.revision revision,

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Grouping Splits Results?

Jun 6, 2013

SELECT
pas_code,
pas_profile,
count(sutp_id),
sum(sutp_price),

[code]...

And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.

pas_codepas_profilesutp_idsutp_pricex
2664good stuff310069< because pbk_price is like 67 from that period
2664good stuff310071< because pbk_price is like 50 from other period

how to get all results in a single line like:

pas_codepas_profilesutp_idsutp_pricex
2664good stuff6200140

View 3 Replies View Related

SQL & PL/SQL :: To Get The Results In The Form Of A Tree

Nov 25, 2012

My table structure is like this

CREATE TABLE Tab1(LVL1_NM VARCHAR2(20), LVL2_NM VARCHAR2(20), LVL3_NM VARCHAR2(20), LVL4_NM VARCHAR2(20))

and the values will be for one value of LVL1_NM there will be different LVL2_NM values like wise for each and every value of LVL2_NM column there will different values in LVL3_NM column like wise for LVL4_NM is there any way to get the results as per their relation ships.

View 7 Replies View Related

SQL & PL/SQL :: Grouping Results By Ranges?

Oct 21, 2010

I have this table,

Create table TBL_OK_HIST
(
DATE_KEY NUMBER,
A_N VARCHAR2(22 BYTE),
R_DUR VARCHAR2(8 BYTE),
CH_DUR VARCHAR2(8 BYTE),
REV VARCHAR2(20 BYTE)
)

insert into TBL_OK_HIST
values (20101010,123456768,5,20,2);
insert into TBL_OK_HIST
values (20101010,123496568,15,20,2);
insert into TBL_OK_HIST
values (20101012,122235768,25,25,3);
[Code] ......

Thus, applying the following would yeld:

Select * from TBL_OK_HIST

DATE_KEYA_N R_DUR CH_DUR REV
201010101234567685202
2010101012349656815202
2010101212223576825253
201010112345676819202
2010101234567681252527
2010101323456768136365
2010101056768123411202
2010101134681256717202
2010101068123456755559

generate the following results:

range_start_rdur range_end_rdur no_of_an sum_of_rdur sum_of_chdur sum_of_rev
1 5 1 5 20 2
6 10 1 9 20 2
11 15 2 26 40 4
16 20 1 17 20 2
21 25 1 25 25 3
26 30 0 0 0 0
31 35 0 0 0 0
36 40 1 36 36 5
41 45 0 0 0 0
46 50 0 0 0 0
51 55 2 107 107 16

I thought I would make use of the following query, but I am not getting the proper results when applying it to a real table with more than 20 mln records:

SELECT trunc(R_DUR/6)*5+1 as range_start_rdur,
trunc(R_DUR/6)*5+5 range_end_rdur,
sum(noofan) as no_of_an,
sum(sumofrdur) as sum_of_rdur,
sum(sumofchdur) as sum_of_chdur,
[Code] ...........

View 1 Replies View Related

SQL & PL/SQL :: Search B If Not In A - But Only Use Results From A If In Both Tables?

Nov 11, 2010

I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".

Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.

The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.

Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.

I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...

View 9 Replies View Related

PL/SQL :: Get Expected Results From Pivot?

Jan 31, 2013

with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
union all
select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
select param_name,param_type,param_value from t1;

desired output:

need output in a row in three different columns

param_value
01-01-1970    31-12-9999 30

I tried below query

SELECT *   
FROM   (
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual

[code]...

and am getting output as

param_type      eff_date_param_value   disc_date_param_value   initial_ignoring_param_value
mbn256_type    <null>                                 <null>                           <null>

View 2 Replies View Related

PL/SQL :: How To Get Added Hours From Results

Jun 14, 2012

I am not getting added hours from results.

SELECT audittimestamp + interval (SELECT      EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.

View 7 Replies View Related

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.

Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......

The result of this is:

Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868

As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.

I need the output to be

Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

DB Creation Manually?

Oct 19, 2012

Just started to learn oracle DBA. I am creating database manually.. I am facing following errors

I executed catproc.sql script it was running suddenlt oracle throwd following error.

CODEPL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package body created.

No errors.

Package body created.

BEGIN
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13517

[code]...

Script detail's

CODECREATE  DATABASE drprod
       LOGFILE GROUP 1 ('/u01/app/oracle/oradata/drprod/redo01.log') SIZE 100M ,               GROUP 2 ('/u01/app/oracle/oradata/drprod/redo02.log') SIZE 100M
  MAXLOGFILES 5
  MAXLOGMEMBERS 5

[code]...

View 14 Replies View Related

SQL & PL/SQL :: Creation Of Package

Jan 12, 2011

Why Package has to be created as two parts

1) Package spec
2) Package Body ?
Quote:PLS-00304: cannot compile body of '<PACKAGE_NAME>' without its specification

I learned it as a Thumb of rule! But IF there is any rule for it give that URL/link for that.

View 25 Replies View Related

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

I am searching the simplest way for ad hoc MINUS.I do:

SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....

and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?

View 6 Replies View Related







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