Hi, I found out that Oracle Database 10g and 11g treat the following PL/SQL block differently (I am using scott schema for convenience): DECLARE v_ename bonus.ename%TYPE; BEGIN SELECT b.ename INTO v_ename FROM bonus b JOIN emp e ON b.ename = e.ename JOIN dept d ON d.deptno = e.deptno WHERE b.ename = 'Scott' ...
Hi, i have select like: select substr(account,1,4), currency, amount, module,count(*) quan, wm_concat(id) ids from all_transactions group by substr(account,1,4), currency, amount, module But sometimes COUNT(*) is more then 600. In that case i get: 'ORA-06502: PL/SQL: : character string buffer too small' Is there any way out to keep w...
Hi, have select like select trn_dt,collect(ac_no) from transactions where rownum < 1000 group by trn_dt i'm doing it in SQL Navigator. But i can't export results! Table collect(ac_no) is not exported. Is there any way out? ...
When I send e-mails using the package UTL_SMTP I am getting this error when executing the comand UTL_SMTP.MAIL: 501 5.1.7 Bad sender address syntax I am passing olny the e-mail as second parameter. This occurs only with certain smtp servers. The code is this: sFrom := '[email protected]'; Utl_Smtp.Mail(Connection, sFrom); Does a...
Is there a way in JDBC to find out the cursor opened or not in JAVA. We are getting an exception when tried to access an un opened cursor. java.sql.SQLException: Cursor is closed. Here is what is happening. The Stored proc is designed this way. the proc returns the cursor and a couple of other columns. For some conditions, the DB guy i...
I define a list a string which contains different country codes ( for example , USA ,CHINA ,HK ,JPN) How can I check that if a input variable equal to one of the country of the country list in pl/sql . I use the following code to test it but fail, how can I revise it? declare country_list CONSTANT VARCHAR2(200) := USA,CHINA,HK,JPN; ...
I have de-normalized table, something like CODES ID | VALUE 10 | A,B,C 11 | A,B 12 | A,B,C,D,E,F 13 | R,T,D,W,W,W,W,W,S,S The job is to convert is where each token from VALUE will generate new row. Example: CODES_TRANS ID | VALUE_TRANS 10 | A 10 | B 10 | C 11 | A 11 | B What is the best way to do it in PL/SQL without u...
I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest! > ERROR at line 1: ORA-29280: invalid > directory path ORA-06512: at > "SYS.UTL_FILE", line 18 ORA-06512: at > "SYS.UTL_FILE", line 424 ORA-06512: at > "SCOTT.SAL_STATUS", line 12 ORA-06512: > at line 1 HERE is the code create ...
I have made this job,that should be executed in an interval of 1 minute,but it's not working. When I use execute dbms_job.run(2); it gets executed. printe is a procedure Please suggest! BEGIN DBMS_JOB.SUBMIT ( job =>:job_no, WHAT=>'printe;',--Procedure next_date=>sysdate+1/24*60, ...
Hi, Im trying to use SET SCAN ON after as follows.. SET SCAN OFF; DECLARE -- declared a variable BEGIN --update statement END; SET SCAN ON; The use of SET SCAN ON; is causing the error when i try to run the script. The error captured ORA-06550: line 16, column 1: PLS-00103: Encountered the symbol "SET" 06550. 00000 - "line ...
i have two selects a & b and i join them like: select * from ( select n.id_b || ',' || s.id_b ids, n.name, s.surname from names n, surnames s where n.id_a = s.id_a ) a left join ( select sn.id, sn.second_name ) b on b.id in (a.ids) in this case join doesn't work :( The problem is in b.id in (a.ids). But why if it...
Let's say I have an ORACLE schema with contains a package. That package defines types, functions, procedures, etc: CREATE PACKAGE... DECLARE FUNCTION ... PROCEDURE ... END; Is there a query I can execute to get the definitions of those individual objects, without the wrapping package? ...
Hello: I'd like to know what should be the SQL statement (for ORACLE DBMS) that would get back unique (by CUSTOMER_ID, VEHICLE_ID, DEALER_ID and EVENT_TYPE_ID) rows BUT it will return the latest date (EVENT_INITIATED_DATE) for that row too. I've tried DISTINCT and GROUP BY, but wasn't able to figure out how to handle EVENT_INITIATED_DA...
Hi, I need to figure how much time passed between 2 times. For example: (14:00:00 - 13:15:00) * 24 = .75 I need this to later on convert KW to KWh, but that's not the point. I can not find out how to do this in PL/SQL. My date/time fields look like this in the DB: 1/23/2010 21:00:00 I would appreciate any suggestions. Steve ...
Here's a package with two pipelined functions: create or replace type tq84_line as table of varchar2(25); / create or replace package tq84_pipelined as function more_rows return tq84_line pipelined; function go return tq84_line pipelined; end tq84_pipelined; / Ant the corresponding package body: create or replace pa...
I have a select: select substr(acc,1,4) ,currency , amount , module , count(*) , wm_concat(trn_ref_no) trn from all_entries where date = to_date ('01012010','DDMMYYYY') group by substr(acc,1,4),currency, amount, module In this case I get an error: ORA-06502: PL/SQL: : character string buffer too ...
I didn't find much data about the internals of PL/Scope. I'd like to use it to analyze identifiers in PL/SQL scripts. Does it work only on Oracle 11g instances? Can I reference its dlls to use it on a machine with only ORACLE 9/10 installed? In a related manner, do I have to execute the script in order for its identifiers to be analyze...
Hi , I am trying to make a run time table named dynamic and inserting data into it from index by table using bulk update,but when i am trying to execute it this error is coming: ERROR at line 1: ORA-06550: line 0, column 0: PLS-00801: internal error [74301 ] declare type index_tbl_type IS table of numb...
Hi, I have some data with start and stop date that I need to sum. I am not sure how to code for it. Here are is the data I have to use: STARTTIME, STOPTIME, EVENTCAPACITY 8/12/2009 1:15:00 PM, 8/12/2009 1:59:59 PM, 100 8/12/2009 2:00:00 PM, 8/12/2009 2:29:59 PM, 100 8/12/2009 2:30:00 PM, 8/12/2009 2:59:...
Hi ALL, I am trying to use a strong ref cur with dynamic sql statment but it is giving out an error,but when i use weak cursor it works,Please explain what is the reason and please forward me any link of oracle server architect containing matter about how compilation and parsing is done in Oracle server. THIS is the error along with co...