PL/SQL

PL/SQL - How to make a check whether the attribute is positive or zero

Hi Experts, How to make a check on attribute(NUMBER) is positive or zero?

PL/SQL error

Hi Experts, I am getting the follwoing error ORA-08102: index key not found, obj# 141051, file 28, block 15245 (2) I tried rebuild on that specific obj id still error persist wat are the possible causes and how to overcome please guide me

SQLERRM = ORA-06502: PL/SQL: numeric or value error: character string buffer too small

My procedure is shown below. When I execute it, it gives me the title error. Field3 is identified as a CLOB. Any / all help would be greatly appreciated. Running Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 CREATE OR REPLACE PROCEDURE xxx IS FileHandle ...

PL/SQL Insert

Hi Experts, I am trying to do 2 INSERT stms on 2 tables consecutively n same transaction and my first insert is successful while doing the insert on second table which is completely carry same set of attibutes and values of 1st table + 1 additional static value attribute,it gives ORA :01400...

INSTR ()

Hi Experts, My test case s working fine for the INSTR() but the same if i use in implemenation its not raising the error giving below the pseudo code IF INSTR (v_lm,pcd,1,1) = 1 OR INSTR(vlm,pc,1,1) = 1 OR INSTR(vlm,pcn,1,1) = 1 THEN raise_application_error(...

Oracle migration error - help me to solve ORA-06544

I migrating data from one database server connection to other server connection using database link from 10g to 11g. I am not able to understand the cause of this error and how to solve it... On compiling the code I am getting the error Error(1): ORA-04052: error occurred when looking up remote...

How to write a test case in PL/SQL

Hi Experts, How to write a test case in PL/SQL to check whether the parameter of a procedure (eg email iddress) takes only lower case ,i did this way in my procedure If(emailadd) <> lower(emailadd)then raise_app_error_ But this can be checked n test case please guide me with pseudo code ...

pl/sql procedure ,urgent!!!

How to write a procedure for displying the data in a TREE or (PARENT and CHILD ) relationship , for ex: A is the main project id, for this project B,C,D are sub tasks(sub project id's) for B the sub tasks are e,f,g and for c is h ,i ,j and for d is k,l,m now i need to display the o/p in a TREE...

$$PLSQL_UNIT

Dear All, I know that using the $$PLSQL_UNIT i can get the current procedure executing. but there is a problem when i use it to get the procedure name inside the package. it only returns the Package name not the procedure name. how can i get the PACKAGE.PROCEDUE_NAME please help .. Ahmad Saeed...

Use of multiple SET/WHERE clauses in the MERGE of 2 Oracle tables

Currently am able to update 1 Oracle table from another using MERGE along with WHEN MATCHED THEN UPDATE logic. Under the UPDATE command I am using a SET/WHERE to update a specific column based on certain criteria. I want to add another SET/WHERE condition to this same update which will result in...

How to implement Sleep logic not using sleep() need customized

Hi Experts, I need to implement a sleep logic in a loop i need to check for SELECT/INSERT stm if it s locked by other user i will go to sleep mode then back to earlier savepoint position eg savepoint x; INSERT/SELECT i need to check for 10 times each time sleep is for 1 sec i applied...

PL/SQL: How to handle OUT Parameter during INSERT

HI Experts, I have a OUT parameter in my procedure(whihc performs INSERT data into a table),how to handle this OUT parameter (primary key of table) -- Will Insert statement inside the PL/SQL block should include this parameter or we should use SELECT stm after Insert to add this attribute ,i am...

PL/SQL: How to use EXCEPTION statement before INSERT?

Hi Experts how to use EXCEPTION statement before INSERT stm in PL/SQL Block Please help me with syntax or format is this option possilbe as well?

PL/SQL: How to check that parameter is case-sensitive?

Hi Experts how can i check the email id shud take only lower case in PL/SQL block i tried using vpos := REGEXP_LIKE(p_email_address,'[:lower:]','C'); as well vpos ;= REGEXP_LIKE(p_email_address,'[^[:lower:]]','C') But both says wrong type as error and p_email_address is a parameter of procedure...

How to perform Unique check in PL/SQL

Hi Experts, I need to perform a Unique check on existing record in table X for combination of field in my PL/SQL block before inserting new record into Table XX Table XX for combination of the following field list (X_ID, St1, St2, City, CP, PS, XNAME, C_ID) AND DISPLAY MODE = 0 How can i do this...

How to make or check the attribute (location) in PL/SQL block?

Hi Experts, How to make check the attribute (location) not taking the value same as city(attribute) or country(attribute) in Pl/SQL block Thanks for the help

How can I join 3 tables using JOIN keyword

Hi Experts, Pls hlp me with my query How can i join 3 tables using JOIN Keyword inside PL/SQL block in SELECT ... WHERE clause Query 3 Tables namely Reg,ROOTS,ME and the select statement takes both parameter variable and local variable Thanks!!!!!

Error(7,30): PLS-00103: Encountered the symbol "OUT" when expecting one of the following

I am getting this eror when i compile my procedure with OUT Parameter ,it has got several OUT param ,but only this particular line pops up err msg Error(7,30): PLS-00103: Encountered the symbol "OUT" when expecting one of the following: := . ) , @ % default character The symbol "," was...

Error(31,19): PLS-00103: Encountered the symbol "INTO" when expecting one of the following

I got this error while compiling a select stm and it s my pckg body however package parameter hav OUT as well IN .. Error(31,19): PLS-00103: Encountered the symbol "INTO" when expecting one of the following: . ( , % from The symbol "from was inserted before "INTO" to continue. Anbody able...

Number precision

What is the default precision of a variable declared as a datatype "NUMBER" in pl/sql