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

PL/SQL :: How To Pass Value From A Table Into Parameter Of A Function

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

Forms :: Table B Has Foreign Key Reference To Table A

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

PL/SQL :: Send Regexp Back Reference To A Function?

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

SQL & PL/SQL :: Function To Pass Through Authentication

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

SQL & PL/SQL :: Way When Doing Rank Function In PL/SQL To Pass Field

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

SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

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

SQL & PL/SQL :: Sum When Reference In Another Table?

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

SQL & PL/SQL :: Update One Table With Reference To Another

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

SQL & PL/SQL :: Substring And Reference Table?

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

PL/SQL :: Query To Get Reference Of Table?

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

SQL & PL/SQL :: Select With Order In Self-reference Table?

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

Server Utilities :: How To Get Parent Table Column Reference

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

Forms :: FRM-40105 - Unable To Resolve Reference To Item?

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

Forms :: FRM-40105 / Unable To Resolve Reference To Item

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

Forms :: FRM-30047 / Cannot Resolve Item Reference CALENDAR.CONVERTED_DATETIME

Jun 13, 2013

FRM-30047: Cannot resolve item reference CALENDAR.CONVERTED_DATETIME.

LOV TIMEZONES_AND_DATETIME

View 1 Replies View Related

SQL & PL/SQL :: Pass Table Name To Cursor?

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

SQL & PL/SQL :: How To Pass Table As Parameter To Procedure

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

SQL & PL/SQL :: Pass Table As Parameter To Procedure?

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

SQL & PL/SQL :: Can Pass Nested Table To Procedure

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

PL/SQL :: How To Pass Input Parameter For Nested Table

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

Forms :: How To Pass Menu Parameters In 10g

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

Forms :: How To Pass Two Parameter For Graphs

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

Forms :: Best Way To Pass 100+ Parameter In Form A To B?

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

Reports & Discoverer :: Pass Parameters In Table Section?

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

SQL & PL/SQL :: Pass Object (or) Table As Argument To Remote Procedure?

Jul 18, 2012

Does it possible to pass object (or) table as an argument to a remote procedure?

View 2 Replies View Related

SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?

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

PL/SQL :: How To Pass Table Of Record As Parameter To Server Page

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

Forms :: How To Pass Parameter From Form To Report

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

Forms :: Pass Global Variable To DB Trigger?

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







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