SQL & PL/SQL :: Declaring Variables In Exceptions Block Globally

Aug 23, 2011

I have a PL SQL program block as shown below,

create or replace
procedure except_handle
is
v_errorcode VARCHAR2(10);
v_errormsg varchar2(200);
constr_violation Exception;
PRAGMA Exception_Init(constr_violation,-2292);
[code]..........

Here, I have assigned values to the variables 'v_errorcode' and 'v_errormsg' directly inside the exception block so that I can use them in both exception types 'constr_violation' and 'others'. I am getting an error message like,

Error(22,1): PLS-00103: Encountered the symbol "V_ERRORCODE" when expecting one of the following: pragma when

View 6 Replies


ADVERTISEMENT

PL/SQL :: Declaring Variable Twice In A Block Is Redundant Or It Gives Any Benefit

May 5, 2013

if table exists then, i whould like to delete it and re create it again.to do so i have two method pls tel me which one i should follow.

declaraion of variable v_strTN twoice in 1) query, does the twoice declaration gives any benefit or it is redundant?

1)
DECLARE
v_strTN VARCHAR2(30);
BEGIN
BEGIN
   SELECT TABLE_NAME INTO v_strTN FROM USER_TABLES
   WHERE UPPER(TABLE_NAME)='ABC';

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Comparing Variables In Trigger Block?

Jun 29, 2010

I am having some issues in creating the following trigger. It gets created but with compilation errors.

create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;

[code].....

when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.

View 12 Replies View Related

SQL & PL/SQL :: Set Datetime Globally

Jun 23, 2011

I am facing problem to set date & time globally in oracle. I set date-time many time but it is on session base.

View 28 Replies View Related

Declaring Cursors With A Variable

Jul 7, 2010

I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:

declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';

cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;

Any way to do this?

View 4 Replies View Related

SQL & PL/SQL :: Declaring Arrays Inside Stored Procedure?

Jun 17, 2010

how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.

procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)

[code]....

When I try to compile it, I get the error "component EXISTS must be declared".

View 14 Replies View Related

SQL & PL/SQL :: Declaring Record Type In Package Spec?

Mar 29, 2013

I need to declare a record type in a package spec and used that define record type as a parameter in a procedure.

A procedure will call this package passing a record type . e.g xxtest.tmpprc(employee_rec employee_type);

(TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...);
CREATE OR REPLACE package xxtest as
PROCEDURE tmpprc(trecordType IN VARCHAR2);
END;

View 16 Replies View Related

SQL & PL/SQL :: Declaring Global Variable Inside Package?

Sep 11, 2012

I want to declare global variable inside package. get the correct query. how to assign value for that variable.

SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /

Package created.

SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)

[Code] ..........

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY NEW_PACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar

View 10 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.

Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?

Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;

View 8 Replies View Related

PL/SQL :: Exceptions In WHILE Loop

Jul 20, 2012

I want to do something like this

-------------------------------------------------------------------------------------------------------
While Condition
LOOP
BEGIN
Insert into table1 values(......);

[Code]...

EXCEPTION
When OTHERS THEN <capture the error while inserting into an Error table>
END;
END LOOP;
-------------------------------------------------------------------------------------------------------

Now I want that If one of the insert statement within the loop fails (say table2) the exception should be captured and next insert statement (table3) should be executed.

How can I do this ? I guess I wont even need a loop

View 10 Replies View Related

SQL & PL/SQL :: How To Handle Multiple Exceptions

Apr 11, 2012

I am having a pl/sql code with two blocks namely one inner block and outer block

declare
v_v1 varchar2(10);
v_v2 varchar2(10);
v_v3 varchar2(10);

[Code].....

from the above code I want to execute both the inner block exception and outer block exception and is there any way to pl/sql engine that execute the outer exception first and inner next

View 7 Replies View Related

Intermittent Exceptions In The Logs?

Jul 14, 2010

java.sql.SQLException: Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_RMERR start() failed on resource 'weblogic.jdbc.jta.DataSource': XAER_RMERR : A resource manager error has occured in the transaction branch
javax.transaction.xa.XAException: Unexpected error during start for XAResource 'EOD': null
at weblogic.jdbc.wrapper.XA.createException(XA.java:103)
at weblogic.jdbc.jta.DataSource.start(DataSource.java:765)
at weblogic.transaction.internal.XAServerResourceInfo.start(XAServerResourceInfo.java:1182)
at weblogic.transaction.internal.XAServerResourceInfo.xaStart(XAServerResourceInfo.java:1115)

View 3 Replies View Related

SQL & PL/SQL :: Handling Exceptions Without Halting Execution

Sep 14, 2010

I have a set of 500 insert queries which i need to execute. I have included exception handler to catch any errors encountered. I don't want the execution to halt when an exception is encountered, i want the next insert statement to be executed

I am pasting the sample code below

DECLARE
error_code NUMBER := SQLCODE;
error_msg VARCHAR2 (300) := SQLERRM;
BEGIN
INSERT statement 1
INSERT statement 2.....
....................
INSERT statement 500.....

commit;
EXCEPTION WHEN OTHERS THEN
IF (SQLCODE != 0) THEN
DBMS_OUTPUT.put_line(SQLERRM);
END IF;
END;

For example if insert statement 100 fails the exception should be caught and execution should pass to 101 statement.

View 13 Replies View Related

SQL & PL/SQL :: Handling Exceptions In Bulk Collect

Aug 17, 2012

For the following procedure if I send the existed employee number of emp table as input. The procedure is executing successfully. But if I send the employee number as input which does not exist in the emp table . The execution block does not handling the exception.

I am getting the following error.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "RAKULA.SP_TEST_EXCEPTION_BULK", line 8
ORA-06512: at line 7

If I use WHEN OTHERS exception then I am able to handle that exception. Why it's happening like this.

CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception_bulk(i_empno NUMBER)
IS
t type_test1;
BEGIN
SELECT deptno BULK COLLECT INTO t
FROM emp
WHERE empno=i_empno;
[code].......

how to handle that exception.

If I create the procedure without using

BULK COLLECT then I am able to handle that exception using WHEN NO_DATA_FOUND

In the following procedure I am able to handle the exception.

CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception(i_empno NUMBER,v_dept_no OUT NUMBER)
IS
BEGIN
SELECT deptno INTO v_dept_no FROM emp
WHERE empno=i_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('employee number' ||i_empno|| 'does not exist');
END sp_test_exception;
/

View 2 Replies View Related

SQL & PL/SQL :: Can Insert A Table In Case Of Exceptions

Aug 14, 2009

Is it possible to insert a table in case of exceptions. For example, i have a function like this:

Create or replace Function test_function(p_Parameter_ID number) Return number is

l_Variable number;
Begin
Select output into l_Variable from test_table where id=p_Parameter_ID;
Exception when others then
Insert into exception_Table values('No Data Found', p_Parameter_ID);
return l_Variable;
End test_function;

In this function, if you notice, i wanted to populate an exception table for further analysis in case data is not found. The function gets compiled without any error in this case.However when i execute this and the Select query does not return any output for a particular parameter_ID, then the exception part fails. It gives me an error "cannot perform a DML operation inside a query".

Can't i write insert statements inside the exception section?

View 39 Replies View Related

SQL & PL/SQL :: Exceptions Does Not Raise In Cursors Declared By User?

Feb 17, 2012

Why exceptions does not raise in cursors declared by user like in the following program

create or replace function sal (p_id employees.department_id%Type) return number is
----sal1 employees.salary%type:=0;
cursor cx is
select * from employees where department_id=p_id;
begin
for i in cx

[code]...

View 13 Replies View Related

Forms :: How To Put Exceptions While Defining Summary Item

Sep 20, 2013

how to put exceptions while defining the summary item ? In one of my datablock there are certain lines whose "inactive_flag" gets a "Y" value on certain events. I want my summary item to exclude such amounts from summing up.

View 3 Replies View Related

SQL & PL/SQL :: User Defined Exceptions For Insertion Update Queries

Sep 7, 2012

how to define user defined exceptions for cases like, ==> when anyone tries to insert string values without using single quotation marks " '...' "? ==> update the column which is not present in table.

how can I define user defined exceptions for such cases?

View 3 Replies View Related

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

Forms :: Multi Data Block And Checkbox Field Based On Control Block

Nov 19, 2010

i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field

e.g

item11 item21 item31 chkbox1
item12 item22 item32 chkbox2

Scenario like this :

My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field

when i checked chkbox1 , my cursor goes to item31...not item32

View 12 Replies View Related

Forms :: Distribute Master Block Total Amount In Detail Block Line Wise

May 5, 2012

i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example

Cheque amount in master block = 291

Invoice wise receiveable amount is as follows

Invoice No , receivable amount , Received amount

10, 196 , 0
20 , 95 , 0
30 , 54 , 0

Result should be as follows:

Invoice No , receivable amount , Received amount

10, 196, 196
20 , 95 , 95
30 , 54, 0

Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.

View 2 Replies View Related

Backup & Recovery :: Error / Corrupt Block Relative Dba / 0x01c12a58 (file 7 / Block 76376)

Jan 25, 2011

i have an error with block corruption

Error: Corrupt block relative dba: 0x01c12a58 (file 7, block 76376)

What all the ,methods I can go for if we are working on a production environment with out any down time.

I can go for dbms repair package and restore and recover

View 3 Replies View Related

Forms :: How To Read Values In A Multi Record Control Block Using Pre-insert Trigger (block Level)

Jul 24, 2010

I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.

I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".

View 3 Replies View Related

How To Use Variables Within Select

Apr 5, 2011

im as using oracle 8 with sqltools i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them.for example:

select SomeID from SomeTable;

i want SomeID to be put into a variable.but i still want to be able to get a normal select query at the end so that i can see the returned value:

i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;

but could not get it to work.

View 2 Replies View Related

Using Variables In A Trigger?

Oct 24, 2007

consider the trigger below,

CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
AFTER UPDATE
ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS

[code].....

The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?

View 2 Replies View Related

SQL & PL/SQL :: Using Table Name As Variables?

Aug 23, 2010

Below is the code I am facing problem using tablename as variable.

I have five tavble is scheme Emp1,Emp2..Emp5

CREATE OR REPLACE
procedure emp_up as
tablename1 varchar2(30) ;
Begin
For x in 1..5
LOOP
tablename1 := 'EMP' ||to_char(x);
EXECUTE IMMEDIATE 'update '||tablename1 || 'set ename = ''ZZZZZ'' where ename in (''MILLER'')';
END LOOP;
End;

Error : Identifier EMP must be declare

View 15 Replies View Related

SQL & PL/SQL :: Use Of Cursor Variables?

Mar 11, 2012

what is use of cursor variables?

View 1 Replies View Related

ODP.NET :: Binding SQL Variables

Nov 5, 2012

The following code is getting the 'Not all Variables bound' error. There are only two variables so I don't see the order being an issue. Assume the integers are assigned elsewhere.

DateTime beginDT = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt);
DateTime endDT = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);

SQL.Append(" WHERE DATE >= :beginDTParameter ");
SQL.Append("AND DATE < :endDTParameter");

OracleCommand cmd = connection.CreateCommand();
cmd.Parameters.Add(new OracleParameter("beginDTParameter", OracleType.DateTime)).Value = beginDT;
cmd.Parameters.Add(new OracleParameter("endDTParameter", OracleType.DateTime)).Value = endDT;

View 2 Replies View Related

SQL & PL/SQL :: Use Of Global Variables

Feb 24, 2012

Explain about Global variables in Plsql? What is use of these global variables and where do use these variables?

View 1 Replies View Related

PL/SQL :: Not All Variables Bound

Sep 4, 2012

I was new to Oracle. When i am Executing this Query, I was getting the error "ORA - 010008 - not all variables bound" .

VARIABLE sathya NUMBER
BEGIN
SELECT sal INTO :sathya FROM emp WHERE empno=7902;
END;

View 10 Replies View Related







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