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
ADVERTISEMENT
Apr 1, 2013
Basically, we have 2 procedures in 2 different databases. One of them makes a call to the other over a database link passing an associative array as a parameters.
This has woked fine for many years 9.2.0.8.0 but we are in the process of migrating to 11.2.0.2.0 and have started getting compilation errors.
1 > Create TYPE in Database_1
=====================
CREATE OR REPLACE PACKAGE DB1_Pkg
IS
type x_tab is table of number index by binary_integer;
v_tab x_tab;
END DB1_Pkg;
[code]....
View 3 Replies
View Related
Apr 29, 2009
I have to create a function which takes in 3 parameters as varchar2(SQL,From,Replace)
1) SQL is the actual SQL
2) Find is the table name to find
3) Replace is the table name to replace the found table.
Moreover there are 19 sets of Find,Replace and the above function loop through all the 19 sets to replace all the tablename in FInd with Replace. I could write the function but the problem is how do i define these sets of table names.
I tried define the tablenames as
Scrpt(1,1) = "PS_CGF_TBL"
Script(1,2)= "PS_CGF_C_TBL"
...
...
Script(19,1) = "PS_CGF_AC_TBL"
Script(19,2) = "PS_CGF_XX_TBL"
and then tried to loop it by declaring an integer as index as
For Index = 1 To 19
string = ReplaceAll(SQL, Script(Index, 1), Script(Index, 2))
Next.
I am getting error in declaring the table names.
View 1 Replies
View Related
Jun 17, 2010
how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.
procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)
[code]....
When I try to compile it, I get the error "component EXISTS must be declared".
View 14 Replies
View Related
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
Aug 4, 2008
I am trying to update a table with a MERGE command using a Host Array and I am getting a unique constraint violation. According to the Oracle documentation "sqlerrd[2]holds the number of rows processed by the most recently executed SQL statement. However, if the SQL statement failed, the value of sqlca.sqlerrd[2] is undefined, with one exception. If the error occurred during an array operation, processing stops at the row that caused the error, so sqlca.sqlerrd[2] gives the number of rows processed successfully."
So when I catch the error and try to print out the offending row in the host array, it points to the incorrect row. If I change the MERGE command to just an INSERT, sqlerrd[2] now points correctly to the row in error. Is there an issue with MERGE in this respect? How can I get the correct row with MERGE?
View 2 Replies
View Related
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
View Related
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
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
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
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
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
Jun 27, 2011
create or replace function nothin
return int
as
type arr_type is table of departments.department_id%type index by binary_integer;
arr arr_type;
begin
select department_id into arr from departments;
return 4;
end;
It gives the error : Quote:expression 'ARR' in the INTO list is of wrong type I can't figure out why.
View 6 Replies
View Related
Dec 28, 2012
find the sample of code which is giving me the same error. whts wrong with below piece of code.
Programe
declare
cursor c1 is select * from x1;
l_x1 type1;
[Code]....
View 3 Replies
View Related
May 6, 2013
I am providing the complete code and my exact requirement.
CREATE OR REPLACE PACKAGE INTERNAL_SCORING_RAM
IS
PROCEDURE TrendScoring_ram(pBUID IN STAGING_ORDER_DATA.BUID%TYPE,
OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
ReturnValue OUT VARCHAR2);
[code]...
/In my code the procedure "trendscoring_ram" is calling "inserttrend_ram" procedure 70 times for different variable values. Instead of calling the "inserttrend_ram" procedure 70 times.
want to hold the values in a associative array , defining it in package and call that procedure only once.As below.
Inserttrend_ram(pBUID, OrderNum, Associativearray, Associativearray, Associativearray,Associativearray);
For that I have tried the following but it's not working.IN the package I have declared the associative array like this.
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
I have modified the inserttrend_ram as below.
PROCEDURE InsertTrend_ram(
P_TYPE_OTS_REC IN type_ots
)
IS
BEGIN
[code]...
View 1 Replies
View Related
Jan 10, 2011
We can save Associative array in data base, if not why?.
View 3 Replies
View Related
Aug 11, 2011
For Nested table i have done it in follwing way? This is pl/sql stored procedure.
CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,
[Code]....
My JavaCode is
--First getConnection
--prepare sql string : sql = "{ Call Test(?,?,?,?,?) } ";
--Use prepareCall function on Connection object and passed this sql string and retrived CallableStatement class object.
[Code].....
Then what if i want to select more than one column from EMPLOYEE and pass to my javaCode......How my Pl/Sql will look like?........ I know how to retrieve all the coloumns of resultset in java.
I want to ask how pl/sql code will look like, i know the retrieveal process in java.
List<List> result = new ArrayList()<List>;
List col = new ArrayList();
ResultSet rs = (ResultSet)stmt.getObject(5);
ResultSetMetaData rsMetaData = rs.getMetaData();
[Code].....
View 1 Replies
View Related
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
Apr 25, 2013
I've one package in which one record is created. associative array is craeted on that record.create procedure on associative array.using forms 6i i want to call this procedue.(package_name.procedure_name(paramerters)). but my problem is what paramerter shuld i provide to excute the procedure?like PK_EXCEL_TO_DB.PR_DO_INSERT(LIST_ROUTE); but i am getting error while doing this.
CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS
TYPE ROUTE IS RECORD (COL_ROUTE VARCHAR2(255), VAL_ROUTE VARCHAR2(4000));
TYPE LIST_ROUTE IS TABLE OF ROUTE;
PROCEDURE PR_DO_INSERT(i_lData IN LIST_ROUTE);
[code]...
View 14 Replies
View Related
Jun 4, 2013
What is the real time uses for Associative array, nested table and varray ?
View 2 Replies
View Related
Sep 24, 2013
APEX_ITEM functions do not seem to be working correctly. When I create a region with this query:
select APEX_ITEM.SELECT_LIST_FROM_QUERY(1, 1, 'select ''Jan'' d, 1 r from dual union select ''Feb'' d, 2 r from dual') month_id
from dual I get the error:report error:ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parametersWhen I run that query in SQL Developer I get this:
<select name="f01" ><option value="%null%">%</option><option value="1" selected="selected">1</option></select>It
works but returns an incorrect result, there is no list.When I run that query in SQL Workshop on apex.oracle.com I get this:
<select name="f01" ><option value="%null%">%</option><option value="2" >Feb</option><option value="1" selected="selected">Jan</option></select>
which is correct.I tried changing NLS_COMP parameter from Linguistic to Binary and changing NLS_SORT to BINARY_CI but that did not make a difference. This seems like a rare problem and generally related to installation but it has me stopped dead in my tracks.
View 2 Replies
View Related
Apr 26, 2013
how to use checkbox item, and trying to get checked options values via application global arrays. So, this may be quite simple question, but I'm completely stuck here...
When I was looking through various threads and guides, I've encountered checkbox corresponding array names like "g_f01" - "g_f50". And so far i saw that these names are derived from item name in generated HTML code, for example:
<input type="checkbox" name="*f10*" value="3" />
And this one stands for array name "g_*f10*".However, when I tried to do the same thing - i receive item name which looks like "*p_v04*", and therefore, I can't figure out, which array name should I choose to adress it properly.
My generated HTML snippet:
<input type="checkbox" id="P6_ANSWER_0" name="*p_v04*" value="3"/>
View 2 Replies
View Related
