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.
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.
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
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.
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;
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?
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.
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.
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.
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).
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.
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),
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?
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.
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] ........
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')
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]..........
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.
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]....
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;
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:
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.
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
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?
