create table test_g(x date);
insert into test_g values (to_date('01-NOV-2001','DD-MON-YYYY'));
insert into test_g values (to_date('02-NOV-2011','DD-MON-YYYY'));
insert into test_g values (to_date('03-DEC-2012','DD-MON-YYYY'));
insert into test_g values (to_date('12-DEC-2012','DD-MON-YYYY'));
insert into test_g values (to_date('31-DEC-2012','DD-MON-YYYY'));
[code].....
I wrote below procedure
create or replace procedure p_testq(p_in_date in date) is
v_comp date;
v_strg varchar2(200);
i number:=1;
type t_trc is ref cursor;
trc t_trc;
v_sql varchar2(2000);
-- record to which data goes into
type t_prec is record(x date);
prec t_prec;
-- plsql table to store data
type t_frec is table of t_prec index by binary_integer;
frec t_frec;
-- flow of data, is from v_sql --> plsql record --> plsql table
begin
dbms_output.put_line(' month of paramter '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
select min(x)
into v_comp
from test_g
where x <= P_IN_DATE
[code].....
how do I store v_strg values so that the dates are included in single quotes
begin
p_testq('12-DEC-2012');
end;
v_strg: 06-Nov-2012,09-Nov-2012
The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result from c_ship_hist where shipment_dtl_id = :SDID; exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).
The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR FROM messages WHERE msg_code = 10;
returns nothing.
Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR FROM messages WHERE msg_code = 20;
This package is generating excel file which contains cursor result.In excel data is populated like below.Column name is Zip_code .My concern is how to remove that single quote from excel file.
eg:
Zip_ '01234 '12567 '23432 '00234
create or replace PACKAGE BODY PKG_MONTH_END_AUTOMATION AS PROCEDURE PROC_ZIP_CODE_MONTHEND (directoryOrPath IN VARCHAR2 default 'LOC_PHASE1_WHOUSE_SALES_ADMIN')
[code]...
-- main body
BEGIN -- Generating Zip Files SELECT last_day(add_months(sysdate,-1)) INTO v_last_date
create or replace procedure ab(a in varchar2, b in varchar2) is test varcha2(8); begin if (a is not null) then for i in(select c from t where c between ||'''||a||'''|| and ||'''||b||'''||) loop test:=i.c end loop; end if; end;
I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.
The documentation for v$database says: QUOTE V$DATABASE displays information about the database from the control file. Is it safe to assume that there will only ever be one row queried from this view?
I have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are
sid varchar2(10), cob_dt varchar2(10), deal_id varchar2(10), new_val varchar2(10), old_val varchar2(30), amend_col v archar2(50), i have return a proc create or replace
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have this in one of the packages WCL_LIB: TYPE vc2_255_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
and i use it in one of the procedures as PROCEDURE "WCL_EVENTS" (p_event_id IN NUMBER, p_event_arr IN Wcl_Lib.vc2_255_arr, p_model IN VARCHAR2 DEFAULT NULL, p_model_code IN VARCHAR2 DEFAULT NULL
but the calling procedure doesnt have any array.... Can I declare something like in the procedure to be called p_event_arr IN Wcl_Lib.vc2_255_arr DEFAULT NULL,
I tried, but doesnt seem to work? so how to call the procedure, which has a array as mandatory, but calling one doesnt have any?
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>
i have a problem when i execute this query and it takes more than 32 minutes and after that connection get hanged in toad 9.2.7 and oracle version 11.2.1.0 with this error- index was outside the bounds of array.
SELECT t.ID AS "serial#", SUM (CASE WHEN (SELECT MAX (g.test_date) FROM label1 a LEFT JOIN label2 g ON g.ID = a.ID ) IS NOT NULL
[code]...
i want to know the cause of above error and what would be the solution.
I'm trying to query an array where we will have mathnames that have the follow format: variablemathname[00000]
have been digging around for a few days to try and simplify my existing query. As of right now i'm hacking it together to bring back the bracketed array value by using INSTR and SUBSTR. This works and gets me the correct results but I want to clean the code up by using regexp_SUBSTR.
In my reading up on regular expression I've tried to create my pattern by using [.] which I believe to be [any character]. I want it to start at the beginning of the string so I've used [^ and I only want the one occurrence so I've ended my expression with ]
I tried using the escape before my pattern as I know that [ is a metacharacter but I receive the same results.i'm trying to use to get the expression correct.
SELECT REGEXP_SUBSTR('variablemathname[00000]', '[.],[^,],') RESULT FROM DUAL;
My expectation is it will bring back the following [00000] but the way it is written now is bringing back nothing.
I want to use a array variable to store many string values i.e. values like ( "my name is hhhyy and i have a tack", "My data is valid" , "hhggg gaya hujjjs") currently all these values are in seperate variables , I want to consolidate them in one array and use it in further , after replacing some values from them.
I want to take data too. I am thinking of using "Varchar array" with values are in one cursor i want to take them in single array. varchar array is throwing some exception once it has taken all the value. I had extended it upto 100 values.
create or replace function testet ( value varchar2) return varchar2 as x_value Varchar2(2000); x_value2 varchar2_array := varchar2_array(); x_value3 number; [code].....
I would like to know how to create an associative array from an existing array plus some columns using other tables using a SQL query, bulk collect to a new array.
For example: 1. array 1 is created like select emp_no, emp_name, dept_no bulk collect into l_emp from emp; 2. array 2 needs to be created from array 1 along with the table department and the output should have all columns of array 1 plus dept_name, loc_no from department table. 3. I know this can be done using a simple join between 2 tables but for some reason I would like to create it as explained above. 4.i wanted them as associate arrays and not object collection.
I have requirement to create a procedure that accepts an Array as IN parameter , Query a table using this array and return the result as refcursor . I tried to get it as below but not working .
CREATE OR REPLACE TYPE ARR_ID AS TABLE OF VARCHAR2(20); / CREATE OR REPLACE PROCEDURE TEST_ARRAY (P_ARR_ID IN ARR_ID, P_CUR_OUT OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_CUR_OUT FOR SELECT * FROM EMPLOYEE WHERE EMP_ID IN (SELECT * FROM TABLE(P_ARR_ID )); END; /
create or replace function nothin return int as type arr_type is table of departments.department_id%type index by binary_integer; arr arr_type; begin select department_id into arr from departments; return 4; end;
It gives the error : Quote:expression 'ARR' in the INTO list is of wrong type I can't figure out why.
I need to count number of elements in the same catagory of an array.. For example, an array consists of {'a','b','c','c','a','d','c'} means, i need to display like a=2, b=2, c=3, d=1.
I have written the below code.
declare type array_val is varray(10) of varchar2(15000); counter number:=0; SMQ_NAME ARRAY_VAL:=ARRAY_VAL(); begin
[code]....
But its not showing exact output as my requirement..