I have written an explicit cursor (procedure given below) and the issue I have is, when the cursor runs the sql statement
(CURSOR csr_address is
SELECT rtrn_id,
entp_abn,prog_program_cd,
sched_nbr,schd_version_yr,
litm_line_item_nbr, revise_val_text
FROM RETURN_LINE_ITEMS
WHERE sched_nbr = '000'
AND prog_program_cd = '01' AND litm_line_item_nbr in ('016','023')
AND rtrn_id = v1_rtrn_id;)
against a particular return id, it fetches 2 rows; one for line item 016 and the other one for line item 023 where in the litm_line_item_nbr for 016 is 016 and for litm_line_item_nbr for 023 is 023. Once that's done, (I have used a For loop cursor), it loops through as follows:
FOR country_rec in csr_address LOOP
v_line_item_16 := country_rec.litm_line_item_nbr;
if v_line_item_16 = '016' then
v_line_item_16 :='016'
end if;
[code]....
This is in Oracle 10.2.0.4.0. I have some blob records (delimited text uploads) with some diacritic characters in them, and the diacritics are being lost when I run CONVERTTOCLOB.
database character set is AMERICAN_AMERICA.AL32UTF8
I have an application which contains a chart of type Dial.In order to set the major interval dynamically I have switched to using custom xml and replaced the #SCALE_DATA# with:
However, I would also like to label the value with a Units Label (e.g. '%'). I was previously doing this using the Value: Postfix setting under Axes Settings, but after switching to Custom Settings removes this box and I no longer get the units displayed.
Is there some xml I can add which will set and display the units.I am using Application Express 4.0.2.00.09
We have several prod instances and many applications on each of these... and interactive reports saved by many users.Each prod application have its own ID, distinct from the dev application ID.
Until today I used the apex_application_install inside the first wokspace only, and it works fine, but I need to update applications on every instances. It seems the only way to keep a saved report during an update is to use the same application Id (use the app n°200 to overwrite the n°200).So it means the only available method is : release the dev app on the dev workspace ; export 50 apps from it and import those 50 times, maybe more in the future.
My configuration :Oracle Standard or XEApex 4.1.1OHS
I have the below cursor 1 which is working already.For my requirement i want to use bind variable like second cursor.But its telling Bind Variable "p_col_list" is NOT DECLARED.
How to use bind variable Here.
Cursor1: DECLARE emp_cv sys_refcursor; iid NUMBER := 1; i_sql varchar2(100); p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd'; BEGIN i_sql := 'select '''||REPLACE(p_col_list, ',', ''',''')||''' from dual '||CHR(10) ; dbms_output.put_line(i_sql); OPEN emp_cv FOR i_sql ; END;
Cursor2: DECLARE emp_cv sys_refcursor; iid NUMBER := 1; i_sql varchar2(100); p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd'; BEGIN i_sql := 'select '''||REPLACE(:p_col_list, ',', ''',''')||''' from dual '||CHR(10) ; dbms_output.put_line(i_sql); OPEN emp_cv FOR i_sql using p_col_list; END;
I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:
I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l
Note: i can not able post all that functions here.
Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.
---the tables
create table org_a (emp_id number(5) not null, name varchar2(20)); create table org_b (emp_id number(5) not null, name varchar2(20)); create table org_c (emp_id number(5) not null, name varchar2(20));
---the records
INSERT ALL INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE') INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE') INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK') INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN') INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE') INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND') SELECT * FROM dual;
---verify inserts
SELECT * FROM org_a UNION ALL SELECT * FROM org_b UNION ALL SELECT * FROM org_c;
---i want the table name to be dependent on a variable. eventually, ---i intend to link v_org to a form with radio buttons (values: 1, 2, 3) ---to keep this simple, i'll just assign 1 to v_org
DECLARE
v_org number(1) := 1; v_table varchar2(5);
BEGIN v_table := CASE v_org WHEN 1 THEN 'org_a' WHEN 2 THEN 'org_b' WHEN 3 THEN 'org_c' END;
SELECT * FROM v_table;
END;
--this is what i receive
SQL> / SELECT * FROM v_table; * ERROR at line 10:
ORA-06550: line 10, column 17: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 10, column 3: PL/SQL: SQL Statement ignored
I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition
var1 := {'A','B','C'}
and use in my statement like :-
Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?
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;
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT Location, LEVEL,
FROM S_ORG_EXT
where Location = 'North America' and LEVEL ='Software' OR location = 'North America' and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried: SELECT Location, LEVEL, Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM S_ORG_EXT
where Location = 'North America' and LEVEL ='Software' OR location = 'North America' and Active = 'N'
The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost( o_ref_primary_dept OUT PRIMARY_DEPT) IS l_primary_dept LONG; [code].....
how to use collection variables as OUT parameters in procedure.
am new to oracle based coding and am having a heck of a time trying to figure this out. Basically i need to declare 2 date variables, Begin date and End Date.
I then need an if statement that says if begin date = 0 or is null then set it to the first day of the previous month. and if end date = 0 or is null then set it to the last day of the previous month.
I'm having some hard times with attempts to initialize a set of values, Of a specific column from a table into a variable containing it as an array(like a single column table).
Then I am trying to compile a function that gets a parameter - a number. The function should check whether this number exists in this list(return 1), or not(return 0).
I want to initialize key1 column of table TEST_TAB *into* v_my_array(this will be my list), just to see that I can have a set of values in this variable. Then I will want to see if the number is in that list, and return 1 or 0 accordingly.
I get the following error compiling, and can't seem to understand what's wrong:
SQL> create or replace function is_num_in_list (p_number number) 2 return number 3 is 4 TYPE varray_type IS TABLE OF NUMBER(10) INDEX BY PLS_INTEGER; 5 v_my_array varray_type; 6 begin 7 select key1 into v_my_array from TEST_TAB; 8 --if the number provided exists in the list, then 1(TRUE) 9 if p_number in v_my_array then 10 return(1); 11 else 12 --if the number provided *IS NOT* in the list, then 0(FALSE) 13 return(0); 14 end if; 15 end; 16 /
Warning: Function created with compilation errors.
SQL> show error Errors for FUNCTION IS_NUM_IN_LIST:
LINE/COL ERROR -------- ----------------------------------------------------------------- 9/16 PLS-00103: Encountered the symbol "V_MY_ARRAY" when expecting one of the following: ( SQL>
