SQL & PL/SQL :: Resetting Row Counter Based On Variable

Apr 17, 2002

I'm wondering how I can use a counter to number records in a table I'm inserting into. I need the counter to reset based on changing data in my table. For example, I have the following

seq_number name_type
1 Short Name
1 Short Name
1 Short Name
2 Short Name
2 Short Name

I'd like my results to be the following:
seq_number name_type
1 Short Name - 1
1 Short Name - 2
1 Short Name - 3
2 Short Name - 1
2 Short Name - 2

I'd like my counter to increment so that I can add a sequence number to the end of my name type, but when my seq_number field changes I'd like to reset and restart my counter.

View 3 Replies


ADVERTISEMENT

XML DB :: Increment Counter In Loop Based On Condition?

Jan 24, 2013

I want to increment a counter in a loop based on a condition.

Here is how my xml looks like

<result>
<resultset id=1>
<value>

[Code]....

I need to look at each and every resultset and check if the value is >400 and if it is then display some text. Something like this

<outputvalue>
Yes, there is a value greater than 400
</outputvalue>

In my XSL, I declared a variable called count with value 0. I created a for-loop which goes through these results. Then inside the loop there is a condition to cehck if the value is > 400. If the value is >400, then the counter is incremented.

````````````````````
<outputvalue>
<xsl:variable name="Count" select="0"></xsl:variable>
<xsl:for-each select="/results/resultset">

[Code]....

There is a problem with the above code is that result is like this

<outputvalue>
*1*
Yes there is a resultset with greater than 400
</outputvalue>

Is there a way I can just remove the character 1 from the output? Or is there a better way to increment?

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Variable Based On Another Field?

Apr 26, 2010

In the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.

In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.

For each record
if change_program =1 then v_tabl = 201
elseif change_program =2 then v_tabl = 202
elseif change_program =3 then v_tabl = 203
else v_tabl = 201

how to declare and use variables.

SELECT account,
change_program,
task_code_01,
task_code_02,
task1.longdesc,
task2.longdesc
FROM work

[code]....

View 4 Replies View Related

Forms :: Executing Procedure Based On Variable Name

Apr 1, 2011

I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.

I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.

View 1 Replies View Related

SQL & PL/SQL :: Setting Variable In Procedure Based On Parameter?

May 24, 2011

I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.

I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.

the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.

beginning of the

create or replace
PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2,
i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER,
i_postingReport IN NUMBER, TESTER IN VARCHAR2) is
--set serveroutput on size 1000000;

[code].....

View 8 Replies View Related

Count Field Not Resetting When Connecting Through VB

Jul 13, 2010

I am working with an asp.net/vb.net application whose users have oracle accounts to connect to the application.The database is set up to allow three failed login attempts. When connecting directly to the database, the sys.user$ lcount field increments on unsuccessful login attempts, and resets to 0 on successful login attepts. This is what I expect should happen.

However, when logging in through the application, using:

objConnection = New OracleConnection(m_strConnectionString)
objConnection.Open()

the lcount field sporatically increments on unsuccessful login attempts (sometimes it does, sometimes it doesn't), and never resets to 0 on successful login attempts. I would have expected it to behave as it does when connecting directly to the database.

Why does the lcount field not reset on a successful login through the application? How can I make it increment on each unsuccessful login attempt and reset on each successful login attempt?

View 1 Replies View Related

SQL & PL/SQL :: Resetting Character Set At Runtime Of Procedure?

Oct 18, 2013

We have procedure that writes text to an Excel file.

The 10g database is setup for US7ASCII character set. This causes the characters on the Excel spreadsheet to appear strangely. As an example an 'a' would appear with a character similar to '~' on top of the 'a'.

If there anyway of resetting the character set at runtime of the procedure.

View 4 Replies View Related

SQL & PL/SQL :: Add A New Counter Column

Jul 25, 2011

I need to enhance the design of one of the tables by adding a primary key to it. i need to know how to fill this new column with sequenced serial before I change it to Primary key.

CREATE TABLE AFESD.REQUEST_TO_FINANCE
(
REFERENCE_NUMBER CHAR(10 BYTE) NOT NULL,
ISSUE_DATE DATE NOT NULL,
RECEIPT_DATE DATE NOT NULL,
DESCRIPTION VARCHAR2(250 BYTE),
FK_COUNTRYCODE NUMBER(3) NOT NULL
);

ALTER TABLE N_REQUEST_TO_FINANCE ADD REQUEST_SERIAL NUMBER(6);

Now I need to add values to REQUEST_SERIAL but can figure how to do that in SQL. I even tried to use your previous tip and use row_number() but it can only return a full set of values not a single one.

View 6 Replies View Related

Resetting DB Backup Including Archive-logs?

May 10, 2011

My supervisor wants to remove all the archivelogs since it was just a test for 1 year the DB is not actually YET been used. Problem is they want it to be used as soon as possible w/o recreating again the database and just removing some data on tables and removed archive logs. how to safely removed the existing archived logs and create a full backup with archived fresh to sequence 1.

View 2 Replies View Related

Resetting Passwords Of SYSMAN And DBSNMP Will Impact To DB?

Aug 28, 2013

 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
PL/SQL Release 11.1.0.6.0 - CORE    11.1.0.6.0     
TNS for Linux: Version 11.1.0.6.0 - NLSRTL Version 11.1.0.6.0

When I checked the status of $emctl status dbconsole. I got this error OC4J Configuration issue.

I was told by the forums to drop and recreate the repos. But I noticed the moment you drop the repos you will loose the passwords of SYSMAN and DBSNMP.  emca -deconfig dbcontrol db -repos dropemca -config dbcontrol db -repos createI tried default passwords does not worked. it looks like the only option I have is to reset the passwords. but my main concerns is this impact the DB in generally or its common. or is there any other way we can get the OEM back(I already dropped the repos).

View 2 Replies View Related

SQL & PL/SQL :: Associative Arrays Counter

Mar 30, 2012

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"

declare
l_sql_stmt VARCHAR2(1000);
l_year NUMBER(4);
l_event_id NUMBER(2);

[Code]....

Sql gives result like

YEAR EVENT_ID
1995 111
1996 111
1997 111
1998 111
1996 111
1995 111
1995 111

SO I am trying to do like

l_my_arr(1995):= 1 ;
l_my_arr(1996):= 1 ;
l_my_arr(1997):= 1 ;
l_my_arr(1998):= 1 ;
l_my_arr(1996):= 2 ;
l_my_arr(1995):= 2 ;
l_my_arr(1995):= 3 ;

But if I put l_my_arr(l_year) := 0, immediately after FETCH, then the counter is always 1, and I cannot put it before because l_year has to be FETCHED.

View 6 Replies View Related

Networking And Gateways :: Resetting Password For Listener Inherit?

Dec 7, 2010

I just found out that the LISTENER I've creating with a password was with "-inherit" settings. Twice I succeeded in doing this, but it seems that it's not working when I tested it. Thus, I opted to get rid of it's listener.bak file and set the password again. Now I just can't stop it because of this error message: "TNS-01169: The listener has not recognized the password".

I've tried every passwords I made, and tried resetting it, but I'm not successful either, it's the same error message.My last option would be to restart the db Server (since the original listener.ora is already intact),

View 7 Replies View Related

Forms :: Resetting The Sequence And Dynamically Displayed Block

Nov 29, 2010

Is there any way could alter the sequence depending on specific condition to start with 1 in the form builder?

i wanna to reset the sequence to 1 whenever the month change.is there any way?

Could i make detail block invisible and whenever button pressed i will display the block? I want to put the where clause for that block in the button and if the button did not pressed i want to display nothing in the detail block?

View 1 Replies View Related

Forms :: Way To Get Default Record Counter

Jan 25, 2010

I been looking high and low for a way to get the default record counter, at the bottom of the window, to provide how many records have been returned by any query.You know the record 1/? or record 1/10 at the bottom.

I don't want to do an:
execute_query;
last_record;
first_record;

Nor do I want to set the "query All Records" property of the data block to "Yes". Both result in the form opening up way too slow. I've a table with a total of 29500+ rows.

View 28 Replies View Related

SQL & PL/SQL :: Create Counter For The Number Of Inserts Run In A Block

Feb 18, 2010

I have a plsql Proc, which accepts a few parameters and inevitably loops through a cursor and runs a bunch of insert statements. With quite a few IF conditions.

Each insert statement has a value which i want to increment by (+1) every time an insert statement is executed in the same loop.. This is for a student housing database and this is for their room preferences so 1 is the first, 2 is there second preference e.t.c.

Please take a look at the code below: in the Insert values() I have put a? Where I want the number to increment from.

There are a lot more inserts which I haven't put below. I hope I have made myself clear as this has been quite difficult to explain. So for example if the 2nd two inserts are run, then I was the first one to insert with a 1 and the second with a 2.

BEGIN
FOR rec IN c1
LOOP
IF c1%FOUND THEN
INSERT INTO table (PK_A, fk_rms_id, application_type, application_person_type) VALUES (NULL, rec.pk_rms_id, app_type, app_person_type) RETURNING PK_APPLICATION_NO INTO x;
[Code] ........

View 2 Replies View Related

SQL & PL/SQL :: Create A View To Show Counter For Occurrences?

Jul 24, 2011

I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.

In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.

CREATE TABLE XYZ
(
SERIAL NUMBER PRIMARY KEY,
S_NAME VARCHAR2(30)
);
INSERT ALL
INTO XYZ VALUES (1, 'ABC')
INTO XYZ VALUES (11, 'ABC')

[code].....

View 3 Replies View Related

SQL & PL/SQL :: Track Department Change - Update A Counter

Jun 22, 2011

I am trying to track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.

CREATE TABLE EMPL_DEPT
(EMPLID VARCHAR2(11) NOT NULL,
EFFDT DATE,
EFFSEQ SMALLINT NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
DEPT_ID VARCHAR2(12) NOT NULL,
[code]..........

View 5 Replies View Related

SQL & PL/SQL :: Using Sequence To Insert In Child Table Group By A Counter Column

Oct 1, 2011

I found nothing in SQL (all in PL/SQL).I have a table:

create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.

And I have a child table:

create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);

that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.

View 7 Replies View Related

SQL & PL/SQL :: How To Set Variable

Jun 12, 2012

How do I set variables at the top of my code? I want to set the date as the variable.

Select * from employees where employee_dt > '2011-01-01'

View 1 Replies View Related

Get Variable Declaration?

Apr 24, 2013

it is possible to undeclared a variable if so how dont worry am with you to solve any problem lets we can do it

View 1 Replies View Related

SQL & PL/SQL :: Variable Losing Its Value

Jul 21, 2010

I have written an explicit cursor (procedure given below) and the issue I have is, when the cursor runs the sql statement

(CURSOR csr_address is
SELECT rtrn_id,
entp_abn,prog_program_cd,
sched_nbr,schd_version_yr,
litm_line_item_nbr, revise_val_text
FROM RETURN_LINE_ITEMS
WHERE sched_nbr = '000'
AND prog_program_cd = '01' AND litm_line_item_nbr in ('016','023')
AND rtrn_id = v1_rtrn_id;)

against a particular return id, it fetches 2 rows; one for line item 016 and the other one for line item 023 where in the litm_line_item_nbr for 016 is 016 and for litm_line_item_nbr for 023 is 023. Once that's done, (I have used a For loop cursor), it loops through as follows:

FOR country_rec in csr_address LOOP
v_line_item_16 := country_rec.litm_line_item_nbr;
if v_line_item_16 = '016' then
v_line_item_16 :='016'
end if;
[code]....

View 8 Replies View Related

SQL & PL/SQL :: How To Use Bind Variable

Jan 12, 2012

I have the below cursor 1 which is working already.For my requirement i want to use bind variable like second cursor.But its telling Bind Variable "p_col_list" is NOT DECLARED.

How to use bind variable Here.

Cursor1:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql ;
END;

Cursor2:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(:p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql using p_col_list;
END;

View 2 Replies View Related

SQL & PL/SQL :: XML - Return New Row For Each Variable?

Oct 31, 2013

I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:

<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>

I would like to be able to return a new row for each variable, For example:

VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.

View 2 Replies View Related

SQL & PL/SQL :: Table As Out Variable?

Mar 2, 2012

I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l

Note: i can not able post all that functions here.

View 12 Replies View Related

SQL & PL/SQL :: Assigning Value To A Variable?

Jun 14, 2011

Can I assign the outcome of a select query to a defined variable

like

var=select emp id from emp where empname='ddf'

View 5 Replies View Related

SQL & PL/SQL :: Variable In View

May 12, 2012

create view sample as

var varchar(100);
var:= select * from employee;

execute immediate var;

can we create like this ... my requirement is like this...

View 6 Replies View Related

SQL & PL/SQL :: Variable In The From Clause

May 8, 2013

Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.

---the tables

create table org_a (emp_id number(5) not null, name varchar2(20));
create table org_b (emp_id number(5) not null, name varchar2(20));
create table org_c (emp_id number(5) not null, name varchar2(20));

---the records

INSERT ALL
INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE')
INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE')
INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK')
INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN')
INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE')
INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND')
SELECT * FROM dual;

---verify inserts

SELECT * FROM org_a
UNION ALL
SELECT * FROM org_b
UNION ALL
SELECT * FROM org_c;

---i want the table name to be dependent on a variable. eventually,
---i intend to link v_org to a form with radio buttons (values: 1, 2, 3)
---to keep this simple, i'll just assign 1 to v_org

DECLARE

v_org number(1) := 1;
v_table varchar2(5);

BEGIN
v_table :=
CASE v_org WHEN 1 THEN 'org_a'
WHEN 2 THEN 'org_b'
WHEN 3 THEN 'org_c'
END;

SELECT * FROM v_table;

END;

--this is what i receive

SQL> /
SELECT * FROM v_table;
*
ERROR at line 10:

ORA-06550: line 10, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored

View 22 Replies View Related

SQL & PL/SQL :: Value Too Large For Variable

Jan 21, 2011

I HAVE DECLARED A VARIABLE

VAR1 VARCHAR2(20000);

BUT STILL WHEN I ASSIGN SOME STRINGS TO THAT VARIABLE I GET "VALUE TOO LARGE" MESSAGE. WHAT SHOULD I DO?

View 2 Replies View Related

SQL & PL/SQL :: Variable Value Is Not Being Recognize

Mar 27, 2013

reading the value from csv file and store into a variable and then use that variable in select statement in

filter. I have tried the following ways but it is now working.

File content is

first, second, third, forth
Y, N, 001|002, abc
N, Y, 003|004|005|006, xyz
n, Y, 007|008|009, mno

[code].....

View 8 Replies View Related

SQL & PL/SQL :: Variable Use In IN Condition?

Dec 5, 2010

I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition

var1 := {'A','B','C'}

and use in my statement like :-

Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?

View 7 Replies View Related







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