Forms :: How To Pass Table Name By Reference To Function
Oct 1, 2011
I have to create a function which count the max id in the provided table.
For example: I have two table customer and book
and
create or replace function Row_Count(tab_nam) return varchar2 is
CONTR_NO varchar2;
begin
select NVL(MAX(t.contract_num), 0) + 1
INTO CONTR_NO
FROM tab_nam t;
return(CONTR_NO);
end Row_Count;
when I call
Function( customer) ;
or
Function( book) ;
It should tell the max number with addition of one.
View 4 Replies
ADVERTISEMENT
Nov 8, 2012
I have written as function :
create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;
[Code]....
Now i need to pass the values to the parameter from a table? How can i do that?
View 2 Replies
View Related
Jan 11, 2012
There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.
View 5 Replies
View Related
Jul 23, 2013
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf
[code]...
View 3 Replies
View Related
Oct 24, 2010
I wrote many functions before, but NEVER a function to pass through authentication...Here are some of the specs ...In this example, an institution will be accepting tuition and fee payments against bills that are stored in the our system. In addition to the minimum bill authentication parameters, the institution would like to pass us a full name to display when welcoming the user, control the users primary e-mail address, and restrict the payment method on a per user basis.
URL url This will be issued to you by your project manager. It should be of the form URL....
User Id user_id The unique identifier for the user within the institution. This is typically a student ID. (in our database)
Full Name full_name Users full name. Example "John Smith". (in our database) Email Address email Primary email address. in (our database) Payment Method paymentMethod Payment methods allowed for this user.
Allowed values:
"none" :no payments accepted
"ach" :ach is the only accepted
"cc" :credit cards only accepted
"ach_cc" :credit card and ach 16
Key key The shared key that is issued to you by your project manager.Time Zone tz The time zone you are located in. Valid values are:
"E" :Eastern time zone
"C" :Central time zone
"M" :Mountain time zone
"P" :Pacific time zone 1
[code]....
I know I need to have cursor to check on the user_id, full name, email (those values are store in our DATABASE. The URL and the key are giving to me. Do I have to hard code the url in the cursor and stored in a variable so I can check that the url coming in (url in varchar2) is equal to the url in my cursor? The same with the key?
View 2 Replies
View Related
Jul 13, 2011
there is a way when doing the Rank Function in PL/SQL to pass the field that will be ranked as an override.
SELECT rank(p_ColumnAmt) within group (order by p_ColumnNm desc) rank
INTO v_RnkNoAmt
FROM Table_name
WHERE ??????;
p_Column is the amount I am ranking
p_ColumnNm is the actual field name to Rank.
When I pass the field name with an override I do not get the correct rank back. If I run the Select with the actual field name curr_1_mth_amt, I get the correct rank.
I have about 70 different field to Rank and do not want to make a procedure for each field.
View 7 Replies
View Related
Dec 23, 2012
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
View 2 Replies
View Related
Dec 19, 2011
Two tables: article i mutations
Article:
artno descr qty sales
1 beer 1 5
2 coke 1 7
3 wine 1 4
4 beer ct 12 2
5 coke ct 6 3
6 wine ct 12 2
7 beer pl 336 1
8 coke pl 336 0
and mutations:
artno mutation
1 4
1 7
2 5
2 8
3 6
I want to get the result like
article sales
beer 365 '5+2*12+1*336
coke 25 '7+3*6
wine 28 ' 4+2*12
How to do a query.
View 39 Replies
View Related
Sep 8, 2013
update one table with reference to another.
Table1:event_channel
Table2:event_inst
I have to update event_channel one column with 4 record(channel_type_id,values 1,2,3,4) with respect to one record of event_inst table column(event_instance_id).
event_inst table column(event_instance_id) has respective 4 records in event_channel
View 5 Replies
View Related
Mar 18, 2013
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
SELECT
TableA.ID,
SUBSTR(TableA.Code,1,4) Primary_Code,
[Code].....
View 6 Replies
View Related
Sep 13, 2012
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
View 2 Replies
View Related
Jun 25, 2012
I have the following table:
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL);
INSERT ALL
INTO test_A VALUES (1, 'mem1', NULL)
INTO test_A VALUES (2, 'mem2', NULL)
INTO test_A VALUES (3, 'mem3', NULL)
INTO test_A VALUES (4, 'mem4', 1)
INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
1mem1(null)
4mem41
5mem51
6mem61
2mem2(null)
7mem72
8mem82
10mem102
3mem3(null)
9mem93
11mem113
View 3 Replies
View Related
Sep 24, 2013
I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and store in child table with child data. i am not able to find how to get parenet refernce id using control file. In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record and put into child table for c_name test1 and test2 records.
mydata.csv
---------------
P_Name C_Name
------ ------
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
1. table parent (P_ID, P_Name)
2. table child (C_ID, C_Name, P_ID)
i need data bello way.
Parent Table data:
P_ID P_Name
----- ------
1A
2B
3C
4D
Child Table data:
C_IDC_NameP_ID
--------------
1test11
2test21
3test32
4test42
5test53
6test53
7test64
8test64
My controll file
-------------------
LOAD DATA
INFILE 'mydata.data'
APPEND
INTO TABLE parent
when (1:1) = 'p'
fields terminated by ',' optionally enclosed by '"' trailing nullcols
[code].....
able to load parent data and generate P_ID, but i am not able to get P_ID for child records.
View 6 Replies
View Related
Jul 25, 2013
i am getting the error as : FRM-40105:UNABLE TO RESOLVE REFERENCE TO ITEM MTL_SYSTEMS_ITEMS.ORGANISATION_ID.
when clicking the button to enter to into other managed attachments .
View 1 Replies
View Related
Feb 9, 2010
When I make Resource for routing, the error display as below:
"FRM-40105: Unable to resolve reference to item B_OP_SEQS.OLD_START_EFFECTIVE_DATE".
But I cannot find item OLD_START_EFFECTIVE_DATE in Form.
View 4 Replies
View Related
Jun 13, 2013
FRM-30047: Cannot resolve item reference CALENDAR.CONVERTED_DATETIME.
LOV TIMEZONES_AND_DATETIME
View 1 Replies
View Related
Dec 16, 2010
I want pass the tablename to cursor. The tablename will be a dyanamic one, as we have the data in different sources tables[multiple systems - all systems will provide the data dump to oracle] with same set of columns.
I have inserted all the tablenames in a parameter table and based on conditions daat should be fethced from each system.
I need to pass the tablename to the cursor to fetech the coloumn list from the table(the table that is passed as parametr).
View 3 Replies
View Related
Jun 2, 2010
I am trying to pass a PL/SQL table as a parameter to a procedure and then using that table, update the records, but I am getting an error like:
ORA-06550: line 30, column 10:
PLS-00306: wrong number or types of arguments in call to 'UPDATE_STATUS'
Find the code below:
CREATE TABLE test_pl(empno VARCHAR2(20), empname VARCHAR2(40), empsts VARCHAR2(10));
INSERT INTO test_pl
VALUES ('0001', 'A', 'Y');
INSERT INTO test_pl
VALUES ('0002', 'B', 'N');
INSERT INTO test_pl
VALUES ('0003', 'C', 'Y');
INSERT INTO test_pl
VALUES ('0004', 'D', 'Y');
[code]....
View 4 Replies
View Related
Sep 1, 2010
I want to pass a table as a parameter to a procedure.
As an example:
TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
However, I want to give this parameter a default of null... is this possible?
procedure myproc(p_param1 in varchar2, p_tab in my_tab default null)
View 11 Replies
View Related
Nov 12, 2011
declare
cursor c is
select employee_id from employees;
type nst_type is table of employees.employee_id%type;
emp nst_type;
begin
open C;
loop
exit when C%notfound;
fetch c bulk collect into emp;
end loop;
close c;
end;
Above is the sample code, and now i want to pass 'emp' as an input parameter to the procedure.
How can I do that, as emp is of nst_type type and I do not know how my procedure will recognize the datatype...
View 4 Replies
View Related
May 10, 2013
I have a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.
CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10))
/
create type t_msg_details ia table of t_example
/CREATE TABLE table_nested_sample
(msg_codes NUMBER(10),
language_id NUMBER(10),
[Code]...
How to call this procedure I want to insert data like this
Msg_codes Language_id Language_name msg_text_details
101 1 ENG "msg_test" "Bundle_msg_text" 1
"msg_test_1" "Bundle_msg_text_2" 2
View 5 Replies
View Related
Aug 5, 2013
I'm migrating forms 6i to forms 10g. I have a mune which has parameters ,which are working in forms 6i. But when i try to comile in forms 10g its poping up error's.
View 3 Replies
View Related
Mar 5, 2010
i had a chart developed by graphics builder in which I am passing one parameter(ie year) and displaying the chart.Now i want to pass two parameter into chart.How to do this here i have attached my chart view and procedure which executes and display the chart from forms.
coding when button pressed
PROCEDURE CHART_DISPLAY IS
pl_id ParamList;
pl_name VARCHAR2(100) := 'tempdata';
[Code]....
In the above procedure i had one parameter directly am passing value of year '2009' now i want to pass two parameter like between 'year1' and 'year2' for getting chart between two years.
View 1 Replies
View Related
Dec 18, 2012
If I want to pass lots of informations (100 parameter) from standard HR assignment form to my custom form. I am using fnd_function.execute('called form') to launch new form.
I thinking of creating 100 parameter object but not sure it has limitation of number or not. Another ways I googled are shared PL/SQL library or parameter list which I still doubt.
View 3 Replies
View Related
May 31, 2012
I have a problem here.Normally, we use &p_where inside a sql script in condition sectione.g :
select name from member where name like 'a%' &p_where order by name;
may i use this kind of parameter in table section?e.g :
select name from &p_table where name like 'a%' and status = 'a' order by name;
the reason i need to do is there are 2 different server. but i need retrieve same info.server ABC have table A but don't have table B and server DEF have table B but don't have table A.
Is there any other method to solve this problem?
View 4 Replies
View Related
Jul 18, 2012
Does it possible to pass object (or) table as an argument to a remote procedure?
View 2 Replies
View Related
Feb 22, 2011
i need to pass table as input parameter in stored procedures. during the run time, i am getting error
CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/
View 10 Replies
View Related
Apr 20, 2013
Is there any way to pass table of record as parameter to the plsql server page? For instance, I can define a new type in package specification:
create or replace package tst_type
as
type tab_expr_sec is table of varchar2(50) index by pls_integer;
type rec_expr is record (
prim_expr varchar2(50),
expr tab_expr_sec
);
[code]....
View 5 Replies
View Related
Feb 23, 2011
I have created a simple form & report based on 2 tables Cust & cntry.I just want to display all customers of region selected in the criteria form. I am uploading the files.
Other than selection criteria report is fine.
View 4 Replies
View Related
Dec 6, 2011
how can i pass global variable from form to db trigger ?
i have this trigger:
Create Or Replace Drop Trigger Access_Group_Category_Priv_Trg
After Delete Or Insert Or Update
On Scott.Access_Group_Category_Priv
[Code].....
View 4 Replies
View Related