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.
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]....
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]...
We can save Associative array in data base, if not why?.
View 3 Replies View RelatedFor 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].....
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]...
What is the real time uses for Associative array, nested table and varray ?
View 2 Replies View RelatedAPEX_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.
I have a field on a form which is not a database item. The value for this is got by concatenating 3 other fields which are database items but not visible on form. But when i am running the execute query to fetch results into these items I want to concatenate the value in those 3 DB fields and display it in the other non-db field at runtime.
View 2 Replies View RelatedI'm running a 10.2.0.4 database with auditing enabled:
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ -------- -------------
audit_trail string DB, EXTENDED
I have auditing enabled for create session:
SQL> select audit_option, success, failure from dba_stmt_audit_opts;
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
My problem is that when I run a report against DBA_AUDIT_SESSION, the CLIENT_ID column is never populated, it's always blank. I've tried running a trigger to populate the client_identifier variable:
create or replace procedure capture_module
as
begin
dbms_session.set_identifier(sys_context('userenv','module'));
end;
/
[code]....
And if I put an access trigger on a table and create an audit event, the CLIENT_ID column from DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL both show the updated value (module), but no matter what, I never get the CLIENT_ID column in DBA_AUDIT_SESSION to show anything - it's always null.
I want to run a report against DBA_AUDIT_SESSION that will tell me who logged in, when they logged in and out, where they logged in from and the one thing I can't get - what module they were running (SQLplus, Toad, etc). Why can't I get the CLIENT_ ID column to take the value I'm setting with the logon trigger?
I have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are
sid varchar2(10),
cob_dt varchar2(10),
deal_id varchar2(10),
new_val varchar2(10),
old_val varchar2(30),
amend_col v archar2(50),
i have return a proc
create or replace
[Code]...
Is there any way in PL/SQL to select the values from all columns of a table record into an array?
For example:
C1|C2|C3
0 |1 |2
v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2
or
v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2
But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;
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.
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]....
I have an existing Procedure that uses a ref cursor as a parameter. The procedure currently simply selects data into the ref cursor using an OPEN FOR and then exits.
I have a need to extend the processing of the procedure. This will mean retrieving extra columns in the select and also removing some of the retrieved records.
I have the constraint that the definition of the REF CURSOR (which is based on a record type as follows) cannot be modified.
TYPE t_charge
IS
RECORD (
id number,
date_from invoice_detail.from_date%TYPE,
date_to invoice_detail.to_date%TYPE,
description invoice_detail.desc_text%TYPE,
amount invoice_detail.amount%TYPE
);
TYPE t_charge_cursor IS REF CURSOR RETURN t_charge;
The procedure does this
PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
IS
OPEN bill_lines_list FOR
SELECT id, from_date, to_date, desc_text, amount
from invoice_table
where id = p_bill_id;
I am thinking instead of selecting directly into the bill_lines_list parameter I will use a local REF CURSOR to select into a COLLECTION. I can then do some work on the collection, similar to below:
PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
TYPE t_local_charge
IS
RECORD (
[code].....
This all works fine, but the question is, how do I then put the collection into the bill_lines_list REF CURSOR where the '*** HERE ***' comment is so I can send the existing REF CURSOR structure back? (Remember I cannot change any definition that is referenced externally)
I am using Forms 6i.I have a Radio Group, where in I have specified Intial value for it. Previously it was populating properly, But when I have added some code to fix a bug. The radio group starts behaving strange. It doesnt show defaulted value.The code what i put is not even related to the radio group.
View 1 Replies View Relatedthere are 3 radio buttons and 1 list item
i want different options to appear in the list item when choosing different radio buttons.
i've tried to compile the codes and there are no errors.
I dunno what went wrong.
B_DEV IS THE BLOCKNAME
LIST_ITEM IS THE NAME OF THE LIST ITEM
SEVEN_ELEVEN, MANNINGS, GNC ARE THE RADIO BUTTONS NAMES
FOR_BANNERS IS THE RADIO GROUP NAME
the following is my
DECLARE
[Code]....
I want to populate a combo box using values from a table, I know you can manually enter values in the combo box's property palette using the 'Elements in List' option, but this combo box will have changing values and I would prefer it to come from a table rather than manual entry so that when users add a new staff name to the table it will automatically be in the combo box.
I would need to use a trigger on this combo box, but which is the best to use and im guessing the SQL needed is more than just 'select * from staffname'?
I created one list item.which is populating values from list item query dynamically.If i select a particular value from list it will create new record.for the new record line i want to display list item as 'complete' (i.e not exists in the list).At this time i don't want the list also.How can i assign value to the list item in this scenario?
View 9 Replies View RelatedI have created a item, CO_NAME as List Item and list style is Poplist.
I have written following code in when-new-form-instance to populate the data from table to item.
DECLARE
rg_name VARCHAR2(40) := 'COMPANY';
rg_id RecordGroup;
[Code].....
But when I run the form, Oracle Error - FRM-41337:Cannot populate the list from record group.
I have 2 non data base blocks, these 2 blocks act like master details but there is no relation. In header block i have 2 fields called "Product name" and "Product Type"
in detail block having 2 tab pages one having multi record(20 item number and name displayed on it) another one having single record but it has around 20 image items displayed on it.
My requirement is once i enter the product name and then if i press either "key tab" or "Key Enter" or "Mouse Move" from product name field
into product type field then detail record should be populated automatically without using go_block and go_item.
I want to search values with F11.for example i will enter some value and i will press F11.it should be display that record. That block is populating values from code.
View 6 Replies View RelatedI have a form in which the fields are calculated fields. So i need to populate the form after calculating the values based on a database columns. For example if i have a table as below
COL A | Col B | Col C | Col D
2 3 4 5
3 4 5 6
I have 3 fields on my form which i need to populate such that field1 maps to col A. Field2 value is COL B * COL D for that value of COL A. Field 3 value is COLB * COL D for that value of col A.
I tried something like this
PROCEDURE NAME IS
cursor c1 is SELECT * FROM TABLEL;
begin
go_block('Block1');
clear_block(no_validate);
for i in c1 loop
:BLOCK.field1 := i.COL_A;
[code].......
this fetched all the records for field1 but for field2 and field3 it only calculates and populates the field row.
I have the scenario where I have a table(table 1) which stores the car code and the meaning of that code. I then have another table (table 2)which just stores the code. My datablock is based on the table which just contains the code(table 2). On my form if the user wishes to populate this field in the record I have a Lov which goes to the table 1 and retrieves the code and the meaning of it.
The code is then returned to the corresponding field in the datablock for table 2. this works fine in adding and editing. the user sees the meaning and whilst selecting from the lov populates the code field behind the scenes and everything works fine. However, my problem is when the user does a query only the code field is populated from table 2 (the code field is hidden to the user) therefore the meaning field is left blank as if there is no data there when in fact this is not the case.
In one of our cluster environment host 1 has 30+ targets. We are trying to add the targets in the grid. But while doing Agent Synchronization targets are not getting populated in the host..
Checked the log file of the same. Getting error as below..Not sure how to fix this.
2010-07-30 22:15:42,777 Thread-4158 ORAXML-229, File=file:/oracle/product/em/agent10g/magerdbspr08.corp.internal.com/sysman/emd/collection/rac_database_db303pta.xml, Line=0, Msg=LPX-00229: input source is empty (01006)
2010-07-30 22:15:46,534 Thread-4675 target {dbb01pta, rac_database} is broken: Computation of a critical dynamic property failed. Retries Completed (00156)
2010-07-30 22:15:46,580 Thread-4675 ORAXML-229, File=file:/oracle/product/em/agent10g/magerdbspr08.corp.internal.com/sysman/emd/collection/oracle_database_d01pt1_d01pt.xml, Line=0, Msg=LPX-00229: input source is empty (01006)
2010-07-30 22:15:46,581 Thread-4675 ORAXML-229, File=file:/oracle/product/em/agent10g/magerdbspr08.corp.internal.com/sysman/emd/collection/rac_database_dpta.xml, Line=0, Msg=LPX-00229: input source is empty (01006)
2010-07-30 22:15:47,603 Thread-4166 target {tta, rac_database} is broken: Computation of a critical dynamic property failed. Retries Completed (00156)
2010-07-30 22:15:47,648 Thread-4166 ORAXML-229, File=file:/oracle/product/em/agent10g/magerdbspr08.corp.internal.com/sysman/emd/collection/oracle_database_dta_d22.xml, Line=0, Msg=LPX-00229: input source is empty (01006)
I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
ora-00000 normal successful completion error while populating data from Master block to Detail block in Oracle Forms 11g.
View 6 Replies View RelatedMy procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.
View 1 Replies View RelatedI have written a pl/sql function. But it is returing null value. Because I have initialize pl/sql table. how to initialize it?
--------------
return v_list
As
type value_list is table of varchar2(100);
v_list value_list:= value_list()
[code]...