SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master
Mar 6, 2010
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
View 8 Replies
ADVERTISEMENT
Aug 20, 2013
i created one procedure with 4 in parameters and 1 out parameters(return the value) but whenever execute this procedure i got the error i.e pl/sql: Compilation unit analysis terminated.
PLS-00201:identifier T_USER_PLDATA' must be declared.
CREATE OR REPLACE PROCEDURE GET_USER_PLDATA1( V_PROD_LINE_CD IN VARCHAR2,
V_BUS_GROUP_CD IN VARCHAR2,
V_BUS_UNIT_CD IN VARCHAR2,
V_COUNTRY_CD IN VARCHAR2,
V_USER_PLDATA OUT T_USER_PLDATA)[c
[code]....
View 7 Replies
View Related
Sep 20, 2013
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
View 3 Replies
View Related
Sep 28, 2011
In my application, we are getting the following exception on calling the Java procedure.
java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-04030: out of process memory when trying to allocate 10804020 bytes (joxp heap,f:OldSpace)
[Code].....
View 2 Replies
View Related
May 2, 2009
I'm trying to figure out how to simply check and see if a given item is in a table, and if its not, then raise an exception.
for example i have a table employees and each employee has a primary key employID. I need to write a procedure where employID is given as a parameter and if the one given isnt in the table then raise an exception.
View 3 Replies
View Related
Oct 10, 2011
I created a block for EMP table on tabular style. if in first line empno entered is 1then i don't want to allow the end user to enter 1 in second line. but when-validate-item is not working because first_record and next_record is not allowed. I need to validate before saving the data into emp table and immediately after empno is entered into empno item.
View 16 Replies
View Related
Apr 1, 2011
I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.
I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.
View 1 Replies
View Related
May 24, 2011
I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.
I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.
the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
beginning of the
create or replace
PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2,
i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER,
i_postingReport IN NUMBER, TESTER IN VARCHAR2) is
--set serveroutput on size 1000000;
[code].....
View 8 Replies
View Related
Apr 8, 2004
I have two cursors like
cursor_A IS select * from table_a where condition_A;
cursor_B IS select * from table_a where condition_B;
record_A is a recorded of cursor_A%ROWTYPE
record_B is a recorded of cursor_B%ROWTYPE
I define a procedure like pro_A(record_in cursor_A%ROWTYPE) can I overload this procedure by defining pro_B(record_in cursor_B%ROWTYPE)?
If I can't, Can I call pro_A by passing record_B as the parameter to it?
View 3 Replies
View Related
Dec 12, 2012
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
View 7 Replies
View Related
Sep 14, 2011
In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.
table1
=====
license_nbr(pk) name address
=============== ===== =======
LicNo1 test testing
Table2
=====
ticket number(pk) amountto be paid balance_amount license_nbr(fk table2)
tk1 200 200 0 LicNo1
tk2 300 300 0 LicNo1
tk3 400 400 0 LicNo1
tk4 500 500 400 LicNo1
table3
=====
payment table
ticket_number(fk table2) amount paid payment status license_nbr(fk table1)
tk1 1000 excess paid. LicNo1
so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.
View 14 Replies
View Related
Sep 23, 2013
is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies
View Related
Mar 13, 2013
I am trying to create a datablock based on a procedure , but im getting errors in compilation:
Errors are :
1) identifier 'HSM_WSH_DEL_UTIL.DEL_TBL' must be declared
2) PL/SQL ERROR 320 at line 7, column 27 the declaration of the type of this expression is incomplete or malformed
Heres my pkg spec and body for the data block:
CREATE OR REPLACE PACKAGE hsm_wsh_del_util
IS
TYPE del_record IS RECORD (delivery_id NUMBER);
TYPE del_tbl IS TABLE OF del_record
INDEX BY BINARY_INTEGER;
PROCEDURE do_query (p_del IN OUT del_tbl);
END hsm_wsh_del_util;
[Code] .....
View 1 Replies
View Related
Dec 8, 2010
Basically I've created a function, when I run it there is a user input. Mine is a customer number between 1-10.
I was wondering is there a way to add in error check so if I typed in an invalid number it would give me a message saying "Wrong customer_number" or something along the lines of that?
I was told I wasn't able to use "DBMS_OUTPUT.PUT_LINE" in the function I need to tamper with the function header?
Here is my header -
CREATE OR REPLACE FUNCTION hours (custid customer.cust_id%TYPE)
RETURN NUMBER IS
multiply NUMBER;
returnanswer NUMBER;
View 2 Replies
View Related
May 22, 2010
I have 2 items in my form:
1) Text Item
2) Tlist
Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .
View 1 Replies
View Related
Sep 6, 2013
I have the following requirement.Say I have the following procedure created.create or replace procedure myproc (p1 varchar2, p2 varchar2) is begin
find_the_proc_name();
end;
In the above myproc, using find_the_proc_name() i should print the parent procedure name (or function) calling along with the parameters passed.
View 5 Replies
View Related
Jan 20, 2013
I have a master-detail form, before filling up all the item in master cursor should not go to details, if user clicks in detail he should get a message.
When I am pressing ENTER to navigate from one item to another is asking'do u want to save the changes'.how to control this message and when pressedenter cursor should go to next item.
View 15 Replies
View Related
Jun 25, 2012
DB version:11g
can we create a procedure inside a procedure...if not is there any alternative??
in db2 it is allowed so do oracle support this????
View 5 Replies
View Related
Jun 20, 2013
in data block... in empno text item.. I created trigger using When-Validate-Item in that pl/sql block I written this code
declare
BTN number;
begin
if length(:emp.EMPNO)<4 THEN
BTN :=SHOW_ALERT('ALERT2');
END IF;
END;
but it is not showing the alert message when enter the empno more than 4 letters.i used Pre-Text-Item.
View 2 Replies
View Related
Jul 30, 2013
The attachment contains the table info. The condition is that when there are childer for the master ,the master record shd be negated . Excuse me if this very easy. Example -DEL HAS children so the record with DEL and DEL SHD not be in the result query. Example -RAG HAS NO children. so the MASTER record should be taken.
Input
COL1COL2COL3COL4
MASTERDELDELP1
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1
My expected output
COL1COL2COL3COL4
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1
View 3 Replies
View Related
Jun 12, 2012
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
View 2 Replies
View Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Sep 15, 2011
I have a form (StaffDetail) with three different blcoks. One of them is an Application Assignment block which consists of nothing but Assignment Type, Staff (Name), Last Updated By and Last Updated Date.. The Assignment Type and Staff has LOVs assigned to them individually.. Assignment type lets me pick all different types except for Project Manager. As soon as I select this option, i get the following error :
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception...
This does not even let me select any other Assignment type, then i have to go close all sessions and come back into the same screen.
Also, this form was first created and tested on the development environment and later moved to QA environment. All selections work in the development environment.. I checked for the roles in QA for individual Users..
View 2 Replies
View Related
Oct 15, 2011
I have Written this code in WHEN-VALIDATE-ITEM-TRIGGER on PO_DATE field
if not( :PO_HEADER.PO_DATE IS NOT NULL ) then
:po_header.po_date := trunc(sysdate);
end if;
if trunc(:PO_HEADER.PO_DATE)>trunc(sysdate) then
displayerror.errmsg('PO date cannot be future date!');
[code]...
when i am navigating through po_DATE field in the form its showing the error that
WHEN-VALIDATE-ITEM-TRIGGER raised unhandled exception.
View 2 Replies
View Related
Sep 2, 2011
im updating my program and i got a error message
FRM 40735 key-next-item trigger raised unhandled exception ORA 01722
View 3 Replies
View Related
Aug 31, 2012
I have a requirement when i will pass the table name as a parameter in the procedure then the series of stametmemt is performed on the table whose name has been passed.
code look like below
PROCEDURE PROC_CREATE_PARTITION(TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);
V_TABLE_NAME VARCHAR2(30);
[code]....
I am getting a error PL/SQL: ORA-00933: SQL command not properly ended
this error is show in the first select statement line where i have used the variable in the from list of table.
View 5 Replies
View Related
Apr 2, 2012
I need to create a procedure which inserts values from a temporary table into the main table the columns names in temporary table are same for all temporary tables only the table name will be replaced the skeleton procedure code will look like this.
create or replace procedure load_data as
select c_tables is select table_name from user_tables
where table_name like 'TEST%';
V_tbl varchar2(30);
BEGIN
OPEN C_TABLES;
LOOP
FETCH C_TABLES INTO V_TBL;
[code].....
The logic here is to insert values from v_tbl variable table into sales_target table.
when I compile it doesn't like using V_TBL variable in the procedure?
View 5 Replies
View Related
Mar 6, 2011
frm-40375: when-validate-item trigger raised unhandled exception ORA-01403.
i put a new column which is the birth date and i made a new procedure with regards to the age level for the Plan. and when i run the program, theres an error..
View 14 Replies
View Related
Jul 15, 2013
I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........
View 1 Replies
View Related
Aug 17, 2010
I need to create a temp table within a stored procedure. I want to apply some logic on the table, and then delete it when it is completed in runtime -- all inside the stored procedure.
View 13 Replies
View Related
