PL/SQL :: Not Getting Error Code And Message Back In Calling Proc?
Sep 19, 2012
create or replace package body test_exp_pkg as
procedure l_test_exp (errcode out Varchar2, errm out varchar2)
as
l_rec zt574%rowtype;
begin
[code]]....
output:
---------
anonymous block completed
in package init
caught in l_test_exp-1422ORA-01422: exact fetch returns more than requested number of rows
caught in l_test_exp errcode and errm set to -1422sqcORA-01422: exact fetch returns more than requested number of rows
caught in test_exp1User-Defined Exception
caught in others errcode and errm
View 8 Replies
ADVERTISEMENT
Jun 1, 2011
we have an application currntly running on an HP UX system that uses Oracle 9i database. the pro*C codes work fine with Oracle 9i, on the older system. however now we are migrating it to LINUX system (ORACLE 10g). in the new system we are facing issues with fetch statement
here is how we have the
the cursor statement:
EXEC SQL DECLARE diff_cns_list CURSOR FOR
select PREV.CNS_CODE,
PREV.CNS_DESCRIPTION,
[code]....
the code runs fine in the old operating system (HP UX) where oracle 9i was used. but fails in Oracle 10g(OS-LINUX).
the probable reason that we found out is that: the fetch statement returns zero rows, that is the reason why this error is being displayed. but Oracle 9i seemed to work out well with this.
we tried reducing the cursor conditions, where it fetches 3 rows. this is when is the fetch statement works fine.
the functionality of the code is that it should work fine even with no rows selected. Is there a way we can modfy the code to work with zero rows as well.
the error we are getting is : ORA-01002: fetch out of sequence
==========================
as per what is given in Oracle sites:
1) Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 3) Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.
========================
we have checked:
1>this is not the case and ORA-1403 not returned
2>No update statements involved , there is a insert statement which inserts data in this table
3>i am not clear with this one.
View 1 Replies
View Related
Aug 26, 2013
I keep getting PLS-00306 error message.
I am also getting an error that the statement was ignored.
View 5 Replies
View Related
Apr 24, 2013
I am trying to create my own error message but when ever i used sp_addmessage it gives error sp2-0734 unknown command biggning... what to do?
View 12 Replies
View Related
May 30, 2012
I have created a procedure, which should be executed on the below condition with EXECUTE IMMEDIATE COMMAND. But i am getting error.
The error shows the procedure/function name is not existing. But it is exist.
SQL>
1 Declare
2 a varchar2(20);
3 b varchar2(20);
4 c varchar2(1000);
5 begin
6 select to_char(sysdate,'day') into a
7 from dual;
8 select to_char(sysdate,'HH24') into b
9 from dual;
10 if
11 (a='friday' and b>=22)
12 or
13 (a='saturday' and b<=6)
14 or
15 (a='wednesday' and b>=9)
16 then
17 begin
18 EXECUTE IMMEDIATE ('begin'||BACKUP_AUTO_execute_bat_file||'end;');
19 end;
20 else
21 null;
22 end if;
23* end;
SQL> /
EXECUTE IMMEDIATE ('begin'||BACKUP_AUTO_execute_bat_file||'end;');
*
ERROR at line 18:
ORA-06550: line 18, column 32:
PLS-00222: no function with name 'BACKUP_AUTO_EXECUTE_BAT_FILE' exists in this
scope
ORA-06550: line 18, column 4:
PL/SQL: Statement ignored
View 6 Replies
View Related
Oct 31, 2013
i have a xml sheet which contain 30 attributes,and only because of one attribute i got this error when i preview my xls sheet and the rest 29 attribute is working correctly.
Start Excel Previewonly open: falsemTemplate: C:Documents and Settingsesys36Local SettingsApplication DataOracleBIPublisherTemplateBuilderforExcel mp mp mp.xlsmTmpTemplate: C:Documents and Settingsesys36Local SettingsApplication DataOracleBIPublisherTemplateBuilderforExcel mp/tmp.xlsjava.lang.NumberFormatException: multiple points at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source) at java.lang.Double.valueOf(Unknown Source) at oracle.xdo.template.excel.render.BookDataWriter.copyCell(BookDataWriter.java:766) at
[code]....
View 1 Replies
View Related
Jul 26, 2010
I've set up a query that creates 2 columns 'UVLCredit' and 'UVLDebit' and what I'm trying to do is subtract the 'UVLDebit' column from the 'UVLCredit' column and have the resulting value show up in a column called 'UVLTotal.
I'm multiplying 1 column times another to create a new column called UVLCredit, and then doing it again (with a different column) to create a 2nd new column called UVLDebit...the last thing I'm trying to do is to take the results of the 1st new column and subtract the results of the 2nd newly created column to create a 3rd new column called UVLTotal. The error states that the 'UVLDebit' column is an invalid identifier.
See code below....
CODESELECT
T.PO_RELEASE_NBR, T.PO_LINE_STATUS, T.FACILITY,
TI.STATUS_DATE, TI.QTY_ORDERED_UP,
TI.PO_UNIT_PRICE, TI.QTY_REC_TOTAL_UP, TI.QTY_INVOICED_UP,
[b]sum(TI.PO_UNIT_PRICE) * (TI.QTY_REC_TOTAL_UP) as "UVLCredit",
sum(TI.PO_UNIT_PRICE) * (TI.QTY_INVOICED_UP) as "UVLDebit",
[code]........
View 3 Replies
View Related
Oct 6, 2000
When I try to load a .TXT file into an Oracle table, the following message is given at the command prompt;
SQL*Loader-524: partial record found at end of datafile
and the load is not successful. The control file is as follows;
Load Data
INFILE 'c:spledlsubj.txt'
APPEND
INTO TABLE tblSubjectiveCode
[code]...
Could the .TXT file causing any problems ?
View 7 Replies
View Related
Apr 21, 2011
when i running my form i m receiving this error message Error Message is "Could not reserve record (2 tries) keep trying ? " This is due to multi user Login, How can i avoid from this error message.
View 26 Replies
View Related
Nov 13, 2007
I get this error message when debugging this dynamic sql procedure:
ORA-00922: missing or invalid option
ORA-06512: at "PLANNING_PWR.RUNNING_TIMES_EXP", line 66
ORA-06512: at line 12
I cannot find where the error is.
CREATE OR REPLACE
procedure running_times_exp(p_table_name IN VARCHAR2,
p_pattern in varchar2,
p_f_date IN DATE,
[code]...
View 6 Replies
View Related
May 20, 2013
A page has a display-only text item that is populated when a rare exceptional condition arises. I'd like for the text to be colored red. While editing the item, in the Element region,
I populated the item "HTML Form Element Attributes" by selecting 'style="font-color:red"' from the drop-down list. When the text displays, however, the color is still the default black. What do I need to do to turn the text red?
View 2 Replies
View Related
Apr 10, 2012
I want to create a schedule as below. But it shows error message which i cant understand.
begin
DBMS_SCHEDULER.CREATE_jOB(
job_name => 'Alert_monitor',
[Code]....
Here is the error message.
Error at line 1
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
View 3 Replies
View Related
May 30, 2012
We have been getting the below error message, while backing up the DB(Full DB backup) via netbackup. The DB version is 10.2.0.3 (64bit). The archive backup goes fine. the problem is with the full backup.
input datafile fno=00241 name=/u106/oradata/iwhdbqa/iwh_mvlog_01x.dbf
channel CH02: starting piece 1 at 29-MAY-12
RMAN-03009: failure of backup command on CH01 channel at 05/29/2012 21:41:28
ORA-19506: failed to create sequential file, name="iwhdbqa_20120529210533_db_ipnc7n6l_1_1", parms=""
[code]...
I came to know that this is a netbackup configuration/client bug. Can we do something from the oracle side.
The RMAN COMMAND STRINGS we use is as follows:
SET SNAPSHOT CONTROLFILE NAME TO '/u101/app/oracle/product/10.2.0.5/db_1/dbs/iwhdbqa_snapshot_db.snap';
RUN {
ALLOCATE CHANNEL CH01 TYPE 'SBT_TAPE' parms='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1' ;
SEND 'NB_ORA_SERV=atlbackupmaster, NB_ORA_POLICY=iwh-dbqa_oracle, NB_ORA_CLIENT=iwh-dbqa-bu';
ALLOCATE CHANNEL CH02 TYPE 'SBT_TAPE' parms='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1' ;
[code]...
View 3 Replies
View Related
Nov 1, 2012
I am learning oracle DBA training course. I tried to find disc space and mail alert .I got permission denied error message.I was running this script in my own pc.
CODE #!/bin/sh
# Shell script to monitor or watch the disk space
# It will send an email to $ADMIN, if the (free avilable) percentage
# of space is >= 90%
[code]...
View 14 Replies
View Related
Mar 4, 2010
create table test123 as (unit varchar2(5),qty varchar2(25));
insert into test123('ABC','10,40,50');
insert into test123('PQR','20,30,40,10');
insert into test123('XYZ','20,10,70');
I have a table called test123 which qty field. if the sum of qty is entered more than 100 or less than 100, it should throw error.
I wrote this trigger..but it is not working.
create or replace restrict_sum
after insert or update of qty on test123
for each row
declare
v_sum number;
[code].........
View 5 Replies
View Related
Jun 4, 2013
In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are
"UpdateRelatedOrders" and "SetGeoFraudScore"
with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures
"UpdateRelatedOrders" and "SetGeoFraudScore".
I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....
View 19 Replies
View Related
Feb 22, 2013
i have successfully installed Oracle 10g in Windows 7 ultimate but there is one problem after installation of Oracle 10g an Oracle Enterprise Manager 10g have opened immediately after installation.
when i have sign in with
username: sys
passwd: oracle
connect as: SYSDBA
iam getting this error message
"Error
java.lang.Exception: Exception in sending Request :: null"
& then when i click on "Administration" again the login page is opening.i have tried installing Oracle 10g 4 times this is 5th time .
View 3 Replies
View Related
Apr 7, 2011
I tried to use external C procedure from the database and i did all required steps as below.
1. create a C program
2. compile and link the C program
3. copy it to the Oracle_home/bin directory
4. configure listener.ora and tnsnames.ora files
listener.ora
=======
callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP) (HOST = ip_address)(PORT = 1521)
[code]...
View 4 Replies
View Related
Jun 27, 2013
I'm calling a web service with HTTPS protocol from a pl/sql program and i'm using the UTL_HTTP package for do this.
I have a Oracle DB installation 11g Enterprise Edition Release 11.2.0.1.0 on my personal computer with a Windows XP OS, and when i run the code it works fine and returns the result I expect. But, when i run the same code on the Oracle DB of my work (Oracle 10g Enterprise Edition Release 10.2.0.4.0 and OS HP-UX) it fails and give me an error, i think that the error has to do with the configuration of the wallet and the certificates in it, because i consume the WS from Os prompt using the command WGET and works fine and i test consuming a WS with HTTP no secure and works fine too.
This is the code and the result in the Oracle DB 11g on my computer:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as oe@oRCL
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
[Code]....
PL/SQL procedure successfully completed
SQL>
And this is the code(Is the same, the only difference is the path of the wallet) and the error resultant:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as gpacheco@acseld
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
[Code].....
View 4 Replies
View Related
Jun 5, 2012
getting error frm 21011 , ora -65020 , while calling forms from menu , the forms are also passing parameters between forms 1 and forms 2 .
The following code is in menu item .
declare
uname varchar2(30);
cname varchar2(40);
[code]....
View 1 Replies
View Related
Jan 31, 2012
I prepared a report in Oracle reports and trying to run the report through command line by creating a batch file. but i am getting the following error message when i run the batch file
REP-0069: Internal error
REP-57054: In-process job terminated:Executed successfully but there were some errors when distribute the output
REP-50159: Executed successfully but there were some errors when distribute the output
the batch file is as follows
D:DevSuiteHome_1BINRWRUN p:spannawazeitspanna_wa_zeit.Rep userid=alrayatec/alrayatecprod@alraya.world
DESFORMAT=HTMLCSS DESTYPE='mail' DESNAME='sarfraz_it@hnd.com'
View 1 Replies
View Related
Jan 15, 2013
I developed a form in forms9i, at the time of data entering form is not showing any ERROR messages or SAVE RECORD message and when I press exit button it is asking "DO YOU WANT TO SAVE THE CHANGES YOU HAVE MADE".
I have checked my PRIMARY KEY field and there is no mistake and value is populating at PRE INSERT.
View 2 Replies
View Related
Oct 8, 2013
we are getting "snapshot too old" error when we executing a procedure. The error details are as below:
"ORA-01555: snapshot too old: rollback segment number 208 with .."
The code framework is below. basically we are having 3 cursor to select data and the base tables seems to update frequently. hence we understood that this error might come. if any other better way to restructure the below code framework.
Create or replace procedure proc1 as
CURSOR C1 is
select a from tab1
CURSOR C2 (p1 VARCHAR2)
is
select * from tab2;
[code]...
Since DBA not willing to change UNDO_RETENTION and other UNDO* parameters, we need to work around with restructure the code.
View 2 Replies
View Related
Jun 3, 2011
We need to connect to an Oracle database from an ASP web page and I downloaded and installed ODAC112021.zip to a Window 2003 server; can connect OK I sent the zip to co-worker in another state and he installed it on slightly older build of 2003 server and we get the following error
"The setup routines for the Oracle in OraClient11g_home1 ODBC driver could not be loaded due to system error code 14001"
Only saw one post on OTN discussion and msot mention another error (998) [URL]
View 1 Replies
View Related
Aug 29, 2011
If i try to execute the below code i go the ora:00600 error. what is wrong in the query.
select FCT.* from (SELECT
NVL(DD.DATE_KEY,1855) AS DATE_KEY,
DP.PRODUCT_KEY AS PRODUCT_KEY,
DP.VENDOR_KEY AS VENDOR_KEY,
DP.VENDOR_CODE AS VENDOR_CODE,
COUNT(DISTINCT FPL.PO_NO) AS PO_ASSIGNED,
SUM(FPL.PO_TOT_QTY) AS UNIT_ASSIGNED,
COUNT(DISTINCT FVL.PO_NO) AS PO_RECEIVED,
SUM(VO_TOT_RECD_QTY) AS UNIT_SHIPPED,
[code]....
View 3 Replies
View Related
Nov 19, 2010
I'm trying to write my own application ( in Delphi) which should be work similar to the Oracle SQL Developer. I received the body of the selected stored procedure by select * from all_users where type='PROCEDURE' and NAME='name_of_the_selected_proc'
Next, I put the body of the procedure into richedit component, make necessary changes, then put the corrected body into the variable ( varchar2) which is the input parameter of my stored procedure PW_DO_IT
create or replace PROCEDURE PW_DO_IT(P_SQL in varchar2)
as
begin
EXECUTE IMMEDIATE P_SQL;
end;
Everything works fine , the only problem is that I don't know how to get the line in which simulated error occured and its details. The only thing I get is the ORA-24344: success with compilation error
View 4 Replies
View Related
Jan 21, 2013
Recently i have faced the error ora-600[kcbgtcr_12]. oracle suggested in the note (5523799.8) to flush the buffer cache by executing below command. My database version 9.2.0.8
1) to flush the buffer cache: alter session set events 'immediate trace name flush_cache level 1'
is this command for flushing the buffer cache ? if so what will be the impact if we flush the buffer cache ?
View 7 Replies
View Related
Jan 3, 2013
Iam facing another issue with Skillbuilder modal page(for those who have used this plugins). Ive the CREATE part working well the problem is with EDIT part to edit records via the link i applied all the steps and clicking the edit link pops up the page but the issue is for any clicked row the parameter is not being passed and seleceted record doesnt show on the pop up page where could the problem be? though if i set it back to normal link created by the wizard the edit link works well and shows that record on the editable page.
Apex 4.1.1/oracle 11gR2 ; issue happens in FF & IE
View 3 Replies
View Related
Feb 7, 2006
==> linux advance server 2
==> Oracle 9.2.0.1
I received this error in my alert logfile.
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
TRC file out:
====================================
Oracle program name: oracle@ABCxxx
*** 2006-02-07 09:08:01.340
ksedmp: internal or fatal error
[code]...
View 3 Replies
View Related
Sep 10, 2013
I have a .sql file that is used as a wrapper file that when executes within sqlplus (9.2.0.1.0), executes a bunch of .sql files within it. Example below:
WRAPPER.SQL
START D:ScriptsA.sql "'04-01-2012 00:00:00'"
START D:ScriptsB.sql "'04-01-2012 00:00:00'"
START D:ScriptsC.sql "'04-01-2012 00:00:00'"
START D:ScriptsD.sql "'04-01-2012 00:00:00'"
START D:ScriptsE.sql "'04-01-2012 00:00:00'"
EXIT;
Each of the .sql file (A,B,C,D,E) Spools individual output of sql statment within them. Each of the indv .sql file queries different tables with different filters(where) clause.
I would like to capture any error (OS,SQL,DB) into indv error file (A_ERROR.log). The reason being is because later in the process we need to validate if there were any errors before processing and loading the data into our SQL database
View 15 Replies
View Related
