SQL & PL/SQL :: Regexp_SUBSTR On Array Returning No Value
Oct 1, 2013
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.
Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?
I am using the attached program but its giving me compiler error as follows..
Error at line 31, column 1 in file sample.pc proc_modify_tdefs (:in_tdefs,:out_tdefs); 1 PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS' Error at line 31, column 1 in file sample.pc proc_modify_tdefs (:in_tdefs,:out_tdefs);
My query returns the 3rd field from a CSV string. If the third field in the string is empty I want the select to return a null but it returns the 4th field :
SELECT REGEXP_SUBSTR( 'A,B,,D,E','[^,]+',1,3) from dual;
.. this returns 'D'.
Can we somehow make the REGEX_SUBSTR return a NULL for the third field ?
I'm trying to get this down from two regexp to a single one, but I'm not sure it can be done and if so, its beyond me to work out how atm.
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Solaris: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production [code]...
The expression itself contains all the rules I'm aware of (nothing to say they won't move the goalposts again mind!) of and the desired output is as shown above. I should add that the actual data is more complex (many tags per record) so it rules out a regexp_replace
I can separate numbers from string (info) and the first value of the string using REGEXP_SUBSTR (see below):
with dat as (select '35263304 Alcatel One Touch 806' info from dual) select info, REGEXP_SUBSTR ( info, '[[:digit:]]+',1 ) tac, REGEXP_SUBSTR ( info, '[[:alpha:]]+',1) brand from dat
But how can I get rest of the values from that string (red color) ?
I just would like to get separately like: 35263304 Alcatel One Touch 806
I am using embedded sql in a Fortran program on IBM AIX. Whenever I execute a SQL statement other than connect I get a coredump. So I wrote a small test program, but same thing happens. This is the test program -
PROGRAM TEST EXEC BEGIN DECLARE SECTION CHARACTER*20 UDPWD EXEC END DECLARE SECTION [code]....
And then when I run test, I see the 'Connected' print & then coredump. Same thing happens for EXEC SQL Prepare statement. Am I not linking some library ?
My Scenario is'....456re0,50kg400,500rfabs43qre30,25kg150,354rf658....'
there is possible,using regexp_substr or other way to get the values, 0,50 and 400,500 and 30,25 and 150,354? I'm using [^re]+[$kg] and the string comes, but only the first occurence..
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456' using the sql below to break the string into columns. select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
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.
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
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..
I am providing the complete code and my exact requirement.
CREATE OR REPLACE PACKAGE INTERNAL_SCORING_RAM IS PROCEDURE TrendScoring_ram(pBUID IN STAGING_ORDER_DATA.BUID%TYPE, OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE, ReturnValue OUT VARCHAR2);
[code]...
/In my code the procedure "trendscoring_ram" is calling "inserttrend_ram" procedure 70 times for different variable values. Instead of calling the "inserttrend_ram" procedure 70 times.
want to hold the values in a associative array , defining it in package and call that procedure only once.As below.
i have written one procedure which is giving an error PL/SQL:ORA-00902: invalid data type, how to get the distinct value.
there is one table xyz and this table having so many attributes and file_data one of the attribute which is having CLOB data type. I have to find out distinct value in each record of file_data.
suppose for example
TABLE XYZ
col1 col2 file_data A B <CLOB VALUE>------------------------ this CLOB VALUE containing duplicate record V X <CLOB VALUE>------------------------- this CLOB VALUE also containing duplicate record
create or replace procedure test_dealer_upload IS t_out_file UTL_FILE.file_type; t_buffer VARCHAR2(32767);
I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.
