Multidimensional Arrays In PL / SQL

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


ADVERTISEMENT

PL/SQL :: Multidimensional Data Models

Aug 7, 2012

Here is a question from a past paper i took for an exam i am baffled with:

"Give a critical analysis of the need for multidimensional data modelling in contrast to relational data modelling"

I can't seem to find any arguments to discuss this point

View 6 Replies View Related

PL/SQL :: Multidimensional Nested Table

Sep 10, 2013

I am studing Multidimensional Nested table and have the below code:

 DECLARE  TYPE table_type1 IS TABLE OF INTEGER;  TYPE table_type2 IS TABLE OF TABLE_TYPE1; 
table_tab1 table_type1 := table_type1(); 
table_tab2 table_type2 := table_type2(table_tab1);
BEGIN  FOR i IN 1 .. 2  LOOP    table_tab2.extend;   
table_tab2(i) := table_type1();   

[Code]...

exception  when others then    dbms_output.put_line(sqlerrm);END; This code is working fine as of now.But,If i comment below code(table_tab2 is also extended latter):

table_tab2.extend;     
table_tab2(i) := table_type1();

then it gives me error 'Subscription Beyond count'. I would like to know why i need to extend table_tab2 twice?!

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

Passing Associative Arrays Across DB Links?

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

SQL & PL/SQL :: Declaring Arrays Inside Stored Procedure?

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

Precompilers, OCI & OCCI :: Handling Runtime Errors For Host Arrays?

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

Application Express :: Get Checked Options Values Via Application Global Arrays?

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







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