SQL & PL/SQL :: Package Returning Multiple Strings
Apr 5, 2011
I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.
I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?
My Package is the next one:
create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;
create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......
View 4 Replies
ADVERTISEMENT
Feb 18, 2013
I need to split the given string into muliple sub strings based on one special character
Ex : Speace is Special character
with data as (
select 'ab cd ef gh ' from dual )
select * from data
Required Output :
ab
cd
ef
gh
View 3 Replies
View Related
Aug 31, 2010
i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.
View 20 Replies
View Related
Mar 1, 2011
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;
View 3 Replies
View Related
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related
Mar 3, 2010
This is a surprisingly common one I've found on the web...even on devshed forum
I am updating one table from another (Updating Table A from Table B):
Table A
ID, Value
-- -----
1 A
1 A
2 B
Table B
ID, Value
-- -----
1 Animal
2 Box
Table A (modified)
ID, Value, Name
1 A Animal
1 A Animal
2 B Box
No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...
Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?
This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).
View 10 Replies
View Related
Oct 20, 2012
These are the tables I'm working with:
SQL> desc custinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID VARCHAR2(4)
CUSTNAME VARCHAR2(18)
CUSTADR VARCHAR2(12)
CUSTCITY VARCHAR2(10)
CUSTSTATE CHAR(2)
CUSTZIP VARCHAR2(5)
CAPACITY NUMBER(3)
HOUSECODE VARCHAR2(2)
[code]...
I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.
For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.
View 4 Replies
View Related
Apr 16, 2013
I'm trying to create multiple functions, add them then minus 1 in a package. I'm at my wits end as to what I'm not doing correctly as the package gets created fine but the package body is giving me compilation errors.
create or replace package addfunction as
function q1 (num1 IN NUMBER, num2 IN NUMBER) return number;
function q1 (num1 IN NUMBER, num2 IN NUMBER, num3 IN NUMBER) return number;
END;
/
[code].....
Warning: Package body created with compilation errors
View 4 Replies
View Related
Feb 17, 2004
how to send an email to multiple recipients. Is there a delimeter that i have to use to separate the email addresses, or do i have to do the utl_smtp.rcpt(c,email) mulitple number of times.
View 9 Replies
View Related
May 28, 2013
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Apr 8, 2008
I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison.
e.g. "oracle infotech" and "infotech oracle" are 100% match
Do we have any oracle built ins to compare ?
View 6 Replies
View Related
Apr 4, 2012
I have to fetch a string which is between to constant strings in a column.
Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.
In this i need to fetch only ID from each row.
create table testtable ( TestCol varchar2(4000));
INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');
View 17 Replies
View Related
Oct 18, 2013
PROCEDURE COLUMN_SPLIT (p_def IN VARCHAR2, p_sch OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2)
IS
BEGIN
NULL;
END;
END;
I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.
View 13 Replies
View Related
Jun 10, 2007
The db field is a string-type field that hold strings such as:
'1234'
'753'
'textstring'
'345'
Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'
My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'
Is there a quick and dirty SQL-only way to doing this in Oracle?
View 3 Replies
View Related
Jul 26, 2007
I have 2 strings that I want to compare for example
string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
View 2 Replies
View Related
Jan 25, 2012
Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .
In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.
View 3 Replies
View Related
Aug 16, 2013
In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it.
SQL> select 'This isn''t' from dual;
'THISISN''
----------
This isn't
SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual;
TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''')
---------------------------------------------------------------------
34456.789
SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual;
TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY')
------------------------------------------------------
Today's 16th August 2013
View 12 Replies
View Related
Jun 20, 2012
extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.
WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =
[code]...
View 5 Replies
View Related
May 27, 2011
I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )
But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.
So on comparing, it fails as the PL/SQL compares it with English Strings.
View 4 Replies
View Related
Mar 3, 2003
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
View 4 Replies
View Related
Sep 23, 2011
I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';
CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......
This is an except of what's in the external text file. The full text file has been attached.
000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------
Here is the error message I am receiving. I believe this is caused by the blank fields in the data.
INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?
View 12 Replies
View Related
Dec 13, 2012
tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
getting missing comma with the above query.when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"
how to avoid this and get the select query to store as
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij
View 5 Replies
View Related
Jun 8, 2010
There are two tables:
create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
View 4 Replies
View Related
Aug 7, 2010
I have a string:
"This is just for testing 123.
This is just for testing 45654.
This is just for testing 5567876.
This is just for testing 53456547.
This is just for testing 123423.
This is just for testing 98090.
This is just for testing 099473.
This is just for testing.
This is just for testing.
This is just for testing 3.
This is just for testing 34983245983.
This is just for testing 6432."
I need to divide this sting after every 100 characters, as the length of column to insert is 100. And i do not want to modify the column as it has great impact. I need to divide the string, such that it should be less then 100 characters also the string is not cut in between.
like:
first string: "This is just for testing 123.
This is just for testing 45654.
This is just for testing 5567876."
then 2nd string: "This is just for testing 53456547.
This is just for testing 123423.
This is just for testing 98090."
then 3rd string: "This is just for testing 099473.
This is just for testing.
This is just for testing.
This is just for testing 3."
View 6 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
Feb 17, 2011
I would like to create a procedure in oracle to remove extra spaces coming as blank spaces(squared ones)in strings.
View 32 Replies
View Related
Dec 9, 2011
We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .
The procedure builds the string by inserting single quotes in the following manner.
P_BU_LST is the parameter which have comma separated values
'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value
i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';
This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS
ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;
INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577
here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.
But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.
View 8 Replies
View Related
Feb 16, 2011
I created a sample application in java which concatenates three strings and displays the result in a final string.
Check the -
public class conCat
{
public static void main (String[] args)
{
String Message1 = "This ";
String Message2 = "is a Sample ";
[code].......
This is a Sample Application
And the three strings are concatenated to the final string "Message" only. How to concatenate them vertically? The required output is :
This is a sample Application
View 1 Replies
View Related
Jan 24, 2011
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
View 1 Replies
View Related
Sep 23, 2013
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
View 8 Replies
View Related