SQL & PL/SQL :: Array Value In Single Quote?

Jan 9, 2013

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 values to be '06-Nov-2012',''09-Nov-2012'

View 3 Replies


ADVERTISEMENT

PL/SQL :: How To Replace 2 Single Quotes To Single Quote

Nov 21, 2012

I have an script.sql that receives as a parameter an string.

example:

@C:/myscript.sql "o'connor"

user_account_value varchar2(120) := '&1';

EXECUTE IMMEDIATE "Select * from Table where column = :1 "  USING user_account_value I am not sure how to deal with string that contains single quotes.

If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.

so my question is what options do I have to deal with dynamic queries and single quotes.

I tried replacing replace(myParameter,'''',''''''); but not working well.

View 11 Replies View Related

SQL & PL/SQL :: How To Select Data When It Has Single Quote In Value

Sep 4, 2013

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?

View 8 Replies View Related

SQL & PL/SQL :: Escape Character For Single Quote?

May 6, 2011

I have a simple update statement. Sometimes the data in this statement has single quotes in it (like shown below).

Update table1 set account = 'CD'S NOT MINE' WHERE NUMBER = '0027201'

When I run this SQL, I get SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"

Is there an escape charecter that I can use?

View 9 Replies View Related

SQL & PL/SQL :: Single Quote(') In Query Not Working?

Jan 5, 2013

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;

returns character '?'.

View 4 Replies View Related

SQL & PL/SQL :: Removing Leading Single Quote In Excel File?

Feb 20, 2013

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

[code]...

View 6 Replies View Related

SQL & PL/SQL :: How To Insert Single Quote In Insert Statement

Feb 24, 2012

INSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')

View 2 Replies View Related

SQL & PL/SQL :: Oracle 10g Quote Operator

Sep 7, 2011

I have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".

select q'[Oracle's world ]'
from dual

But The following query works.

select q'[It's Oracle's world ]'
from dual

View 4 Replies View Related

SQL & PL/SQL :: Insert String Having Two Double Quote?

Nov 10, 2010

i have table contains a column of var char type i want to insert a value

'1 mmTHICK GI SHEET 4' X 8' X 1MM THICH' in to the coulmn but m getting error

I tried set scan off but its not worrking for the below query.

ERROR at line 1:
ORA-00923: FROM keyword not found where expected

my query is
Insert into Inventory select
'N1280000015',
'1 mmTHICK GI SHEET 4''' X 8''' X 1MM THICH',

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Better Option To Use When The Quote Needs To Be Concatenated To A Varchar2 Value

Apr 13, 2006

which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.

In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)

However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''

View 24 Replies View Related

PL/SQL :: How To Concatenate Quote To Procedure Parameter Input Value

Dec 6, 2012

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.

View 7 Replies View Related

Client Tools :: Escape / Quote Ampersand (&) Because No Variable Substitution

Apr 5, 2011

I wish to replace the string "Benutzername&Kontakt" with nothing.

SELECT REPLACE (role_owner, 'Benutzername&Kontakt', '')
FROM JC_NAME_62
WHERE jc_name LIKE 'SAP_R4_BIN5\_%' ESCAPE ''

But my Toad prompts me always to fill in the variable. I don't wish to do that, I wish to escape the &

View 5 Replies View Related

QUOTE V$DATABASE Displays Information About Database From Control File

Feb 8, 2011

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?

View 2 Replies View Related

String To Array

May 22, 2009

I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.

View 1 Replies View Related

SQL & PL/SQL :: How To Initialize Array

Mar 5, 2010

I have written a pl/sql function. But it is returing null value. Because I have initialize pl/sql table. how to initialize it?

--------------
return v_list
As
type value_list is table of varchar2(100);
v_list value_list:= value_list()

[code]...

View 2 Replies View Related

How To Use Column Names In Array

Dec 2, 2012

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

[Code]...

View 1 Replies View Related

How To Use Array Variable In (in Clause)

Jun 23, 2011

select * from tablename where column in (array_varaiable);

this is my question How can i use all the elements of array in the "in clause";

View 4 Replies View Related

SQL & PL/SQL :: ARRAY As Parameter In Procedure?

Nov 1, 2010

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?

View 5 Replies View Related

SQL & PL/SQL :: Initialize Variable With Whole Array

Dec 3, 2012

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).

I've created & populated table TEST_TAB:

SQL>
SQL> CREATE TABLE "TEST_TAB"
2 ( "KEY1" NUMBER(10));

Table created.

SQL>
SQL> insert into "TEST_TAB" values ('1');

1 row created.

SQL> insert into "TEST_TAB" values ('2');

1 row created.

SQL> insert into "TEST_TAB" values ('3');

1 row created.

SQL> insert into "TEST_TAB" values ('4');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>

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>

View 2 Replies View Related

SQL & PL/SQL :: Index Outside Bounds Of Array?

Aug 27, 2012

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.

View 7 Replies View Related

SQL & PL/SQL :: Removing Array Index

Sep 10, 2010

TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );

TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy

So if I did something like this;

l_cash_records CashRecordTable;

-- say for example that l_cash_rec/l_cash_rec2 has been defined..

l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2

How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?

View 3 Replies View Related

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.

View 10 Replies View Related

PL/SQL :: How To Use Array Variable In Oracle

Jun 15, 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].....

View 9 Replies View Related

PL/SQL :: How To Create New Array Using Existing One

Apr 9, 2013

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.

View 2 Replies View Related

PL/SQL :: Procedure With Array IN Parameter

Jul 4, 2012

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;
/

View 3 Replies View Related

SQL & PL/SQL :: Select Column Values Into Array

Sep 10, 2013

Is there any way in PL/SQL to select the values from all columns of a table record into an array?

For example:

C1|C2|C3
0 |1 |2

v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2

or

v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2

But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;

View 10 Replies View Related

SQL & PL/SQL :: Populating Associative Array With A Column?

Jun 27, 2011

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.

View 6 Replies View Related

SQL & PL/SQL :: Count Number Of Elements In Array?

Mar 13, 2013

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..

View 6 Replies View Related

SQL & PL/SQL :: Associative Array - No Data Found

Dec 28, 2012

find the sample of code which is giving me the same error. whts wrong with below piece of code.

Programe
declare
cursor c1 is select * from x1;
l_x1 type1;

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Accessing A Table Contains Array In Remote DB?

Jan 30, 2009

How to access (create Synonym and Materialized View) a Table contains an Array of type object which is on Remote Database connecting through DBLink.

View 27 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved