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]....
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]....
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>.
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)
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.
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.
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.
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;
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?
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.
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
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.
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?
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] .....
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)
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 .
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.
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.
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
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.
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 :
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..
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
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.
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?
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..
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]........
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.
