we have created following anonymous block........... DECLARE sql_str long(32000); where_str long(32000); counter NUMBER(3):=0; BEGIN sql_str:='SELECT '||' A.bio_id ,'; where_str:=' where '||'A.bio_id=B.bio_id AND'||' A.bio_id<>0 and rownum<25 AND (' ; LOOP counter:=counter+1; sql_str:=sql_str||'decode(A.wk_unit...
Can anybody let us know an easier way to get the result as shown by following query? We need to find a way that can show us the same result as the following query: SELECT DISTINCT A.bio_id , DECODE(A.wk_units2 - B.wk_units1,0,NULL,A.wk_units2) prev, DECODE(A.wk_units2 - B.wk_units1,0,NULL,B.wk_units1) curr, DECODE(A.wk_uni...
I wrote a stored-procedure in Oracle and now, I want to launch it in Java code. I will describe a problem. I have a object type: TYPE PERSON_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20)); And table type: TYPE PERSON_TYPE_TABLE AS TABLE OF PERSON_TYPE; My procedure looks like this: PROCEDURE EVALUATE_PERSON_PROC(P_PERSON_ID IN ...
Hello, I have a piece of my query in Oracle that generates discrete percentile: ... PERCENTILE_DISC(0.9999) WITHIN GROUP(ORDER BY DURATION_COUNT) as PERCENTILE_9999_QTY, ... The data type of PERCENTILE_9999_QTY is Number(8) it works fine except in some cases I get: ORA-01438: value larger than specified precision allowed for th...
hi we are trying to execute following script we are getting errors as ERROR at line 1: ORA-20000: Unknown Exception Raised: -933 ORA-00933: SQL command not properly ended ORA-06512: at line 23 DECLARE l_cursor INTEGER; l_output VARCHAR2(20); l_rows INTEGER; l_sql VARCHAR2(1000); BEGIN ...
we need to implement the following query in such a way that we should not have to write such a big query instead we should implement it through procedure,function or anonymous block (what ever way we can) wk_units1-105 are column names cnt_sls_dm.fct_sales_summary table name. we are comparing the data in the same table but week 2 of a....
Given the following tables, how might I build a SQL query that includes a list of all the items from the "items" table, and a column for each color from the "colors" table that, for each item listed, indicates what colors the item has a relationship with. If that is unclear at all, please let me know what additional information will hel...
How to get the dynamic select results of EXECUTE within pl/sql from Oracle sqlplus? I'm writing a simple sqlplus script to collect the sum of all NUMBER columns of a given table -- SET SERVEROUTPUT ON DECLARE CURSOR column_cur IS SELECT column_name FROM ALL_TAB_COLS WHERE owner = '&scheme_name' ...
Can anybody please tell me what is the use of %NOTFOUND operator? We can use "NOT %FOUND" in cursor also. It will perform same functionality. ...
hi, we have one table as following Table name: metadata_table strucutre is as follows S.No Tablename Column 1 Fct_sales_summary wk_units 2 Fct_exp_summary mn_units wk_units as in the table Fct_sales_summary are columns and are in range wk_units1 to wk_units105 and similarly in Fct_exp_summary ...
I have package with around 30 proc/functions. This gets invoked from servel places. At one place it's using only 2 functions out of 30. When packages gets invoked it will load all proc/functions in the memory. However, in this case it's a overhead. Is there any way to avoid this? Note :- I can not create these two proc/functions again....
please let me know the issue with following script (sql,oracle 10g) 1 DECLARE @colname AS NVARCHAR(50) 2 DECLARE @tablename AS NVARCHAR(500) 3 DEClARE @query AS NVARCHAR(500) 4 SET @colname = 'select wk_units1 from cnt_sls_dm.fct_sales_summary' 5 SET @tablename = 'SELECT tablename from dmi_user.fct_sales_meta' 6 set @q...
I have this procedure i my package: PROCEDURE pr_export_blob( p_name IN VARCHAR2, p_blob IN BLOB, p_part_size IN NUMBER); I would like for parameter p_blob to be either BLOB or CLOB. When I call this procedure with BLOB parameter, everything is fine. When I call it with CL...
I need to call some PL/SQL procedures from my Java application. I can do it with JDBC. But the problem is that procedures are using the "PL/SQL Web Toolkit" and its packages (htp, owa _ util, owa _ cookie, ...). When I call them I get some exceptions as this: Exception in thread "main" java.sql.SQLException: ORA-06502: PL/SQL: numeric o...
Does anyone have a simple example that explains why one would want to use an Oracle instead of trigger ? ...
I wrote a PL/SQL script to set a sequence's value to the maximum value of a table's primary key: DECLARE max_idn NUMERIC(18, 0); seq_nextval NUMERIC(18, 0); increment_amount NUMERIC(18, 0); BEGIN SELECT MAX(mbr_idn) INTO max_idn FROM mbr; SELECT mbr_seq.nextval INTO seq_nextval FROM DUAL; increment_amount := max_id...
I am trying to create a unique constraint on two fields in a table. However, there is a high likelihood that one will be null. I only require that they be unique if both are not null (name will never be null). create unique index "name_and_email" on user(name, email); Ignore the semantics of the table and field names and whether tha...
I'm using Oracle SQL Developer to query an Oracle DB (not sure which version it is) and I'm going to use the SQL I make for a Crystal report. Many of the reports the previous developers have written don't use JOIN keywords to make the joins (and I'm not too familiar with JOIN keywords as a result). Many of the joins they make are mad...
I have problem with Oracle 9.2 and JMS. I created PL/SQL routine to send XML text (from file or CLOB) to queue, but this routine do not compile. My code looks like (filling message omitted): create or replace procedure jms_test(msg varchar2) is id pls_integer; message sys.aq$_jms_stream_message; ...
I have a table which has several one to many relationships with other tables. Let's say the main table is a person, and the other tables represent pets, cars and children. I would like a query that returns details of the person,the number of pets, cars and children they have e.g. Person.Name Count(cars) Count(children) Count(pets) J...