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
ADVERTISEMENT
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
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
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
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
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
Jul 17, 2013
i want to use more than four db links in my stored procedure to retrieve data from different databases. but it says ORA-02020: too many database links are opened. i also wrote execute immediate to close some links but its not working.
View 16 Replies
View Related
May 24, 2010
We have two databases A and B, both are 10g Enterprise edition and we have to replicate only one table from A to B. A is the source and B needs a read only copy. The source of A is modified by User through Oracle Forms.
A latency of 1day can be accepted though the lesser the better. The problem is that this company does not approve of DB links in any form or any tool which in the end has to create a DB link.
View 2 Replies
View Related
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
View Related
Sep 7, 2012
How can I determine what views have a dblink hard coded in them? I am talking about a large number of views so bringing up each view's DDL in a GUI data dictionary tool to look is not an option. I tried running the query below unfortunately "text" is a LONG and doesn't allow the use of the "like" statement.
select * from dba_views where text like '%@%' ;
View 4 Replies
View Related
Nov 29, 2010
the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:
--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));
[Code]...
--expected output
group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E
My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.
View 6 Replies
View Related
Mar 28, 2012
I've begun experiencing a very odd issue with one instance and all of their their db links. When I'm connected via telnet to the unix server, and I connect:
connect myuser/password via sql plus, I connect fine, and the link works.
but when I connect this way:
connect myuser/password@db.tnsname.entry.com using the TNS name specified, I again connect fine but the link calls fails with a TNS cannot resolve service name issue.
This is an existing instance with existing db links, but I repeat the issue on this machine alone with new links. the fully qualified name is the same name entry I'm using to create the link, both when it succeeds and when it fails. This is an 11g instance and the TNSnames file is in the ASM home.Also, i f I connect
connect sys as sysdba I get success as well. This behavior happens with both private and public database links. I'm not aware of any setting changes within this instance, and I've modified (after backing up) the TNSNames file with values from another that works perfectly well. Connecting via remote sqlplus or other JDBC connections gives the same error. Success connecting locally but failure connecting to DB Links when I'm remote. Connecting to the instance shows no issue.
Another instance on the network? An IP config issue in DNS? We have 100's of servers so it's needle in a haystack time. Can I run some sort of trace to see what it's calling to when it works and when it doesn't? (It selects another db's single table and I've confirmed the target is correct and the data is correct).
View 4 Replies
View Related
May 16, 2013
I have a view called V_MEDGEN_LISTRADIO but I can't run SELECT * FROM V_MEDGEN_LISTRADIO
ORA-02020: too many database links in useThe cause is that we call in the view 5 tables over dblink and the open_links parameter is set to 4.
What is strange is that when I run this select by adding a where clause then it works!
SELECT * FROM V_MEDGEN_LISTRADIO WHERE SPRR = 3645;
(use of index so all tables are not called ?)
View 4 Replies
View Related
Jan 25, 2012
I'm currently assessing the design/performance of a Distributed System in which hundreds of Field reps have local Oracle DBs (10.2.0.4) on laptops & have to update a remote database (11.2.0.1) via a PUBLIC database link. Field data (millions of records) collected daily is synched from the local to the remote DB & vise versa through this database link. I have 2 concerns here:
1. Is the database link the best option for such a configuration? (recently field reps have been complaining about the slowness in synchronizing data between local & remote DBs). If not, what other options are available for such processing?
2. I've read a lot about security concerns with using PUBLIC database links, but haven't seen any documents to proof they're a majority security issue. why PUBLIC database links are considered not to be very secure?
View 39 Replies
View Related
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Jul 21, 2011
how to find out what are all the objects referring to particular database links in database?
View 6 Replies
View Related
Jul 2, 2011
There is a user account called 'BALA' in my database.I want to find out the database links that uses this 'BALA' account.
View 2 Replies
View Related
Oct 1, 2012
Is it possible to use the Oracle HAIP feature to bond multiple network links between Active and Standby database ?
Does this require a seperate license ?
Basically, I was looking for a solution for bonding the muliple WAN links that we have between the active and Standby database.
View 0 Replies
View Related
Jul 23, 2013
is there any way to refer a table which is in different schema other than using DB links.
View 5 Replies
View Related
Oct 14, 2013
A website requires to display consolidated data from databases located in different geographical regions (India, London and New York). The application server for the website is hosted only in one location India. What are the techniques that can be used for faster retrieval of data from all 3 databases?
Note: There is no need of real time data retrieval from different regions; however the user should able to view the updated data at predefined intervals.
View 9 Replies
View Related
Jun 15, 2013
I know how to use database links in various forms, but I've been trying to think through how the authentication works for a connected user link in 11g. If I create the link like this,
create public database link using 'orcl';
then any user can use the link, provided they have an identical username/password in the two databases. With pre-11g passwords, it was understandable: the password was salted with the username, so the hash of the password would be the same in both databases, and I assumed that the logon through the link used some sort of IDENTIFIED BY VALUES mechanism. But in 11g, the salt will different in the two databases. So the hash will be different. And of course Oracle never stores the actual password. So I don't see how the authentication works.
View 1 Replies
View Related
Dec 19, 2011
I would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies
View Related
