SQL & PL/SQL :: Rpad Function That Works For Multiple Chars?

Mar 6, 2011

How can I do this ?

is there an rpad that works for multiple chars?

select RPAD('test',10, 'X') from dual;

I want to be able to do something like

select RPAD('test',6, '%20') from dual;

which should return test%20%20

View 7 Replies


ADVERTISEMENT

Get Left Chars Of A Field?

Oct 15, 2008

I have many different file names within my table and I want to remove the .TXT extension from each one. I want to try this SQL but being a newbie in Oracle, I don't know how to say "Left" characters. "Left" is an invalid identifier.

Update TableName
Set File_Name = Left(File_Name, Len(File_Name)-4)
Where File_Name LIKE '%.TXT'

View 2 Replies View Related

SQL & PL/SQL :: Utf-8 - Japanese Chars Are Coming?

Aug 25, 2013

We have to get some data from U98 (saixdbU98) in UTF-8 format in Excel sheet.We are having queries ready for this. These queries bring data which have Japanese characters.But when we run the Select queries then in the result Japanese chars are garbled. It comes in ????.

let us know what process we have to follow to get the result in UTF-8 format in Excel sheet.here is the code

set sqlblanklines on;
SET DEFINE OFF;
set linesize 1000
set long 1000000

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Encoding Emoji Chars

Jan 3, 2012

I'm sending Emoji's in by Email body, but its not being rendering properly.

Is there any way to encode Emoji chars like --> 😪

[URL]........

View 4 Replies View Related

SQL & PL/SQL :: Utl-encode.mimeheader - Encoding Only First 75 Chars?

Dec 5, 2011

I'm using utl_encode.mimeheader_encode to encode the subject line for non ascii chars, but its encoding only first 75 chars, is there any other way to encode subject.

View 3 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

Aug 28, 2010

I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.

SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /

Table created.

SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);

1 row created.

SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);

1 row created.

SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);

1 row created.

SQL> COMMIT;

Commit complete.
[code]...

Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.

View 6 Replies View Related

SQL & PL/SQL :: How To Avoid Multiple Function Calls

Jul 23, 2013

I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.

select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,

View 10 Replies View Related

SQL & PL/SQL :: Function Return Multiple Rows

Jun 23, 2006

can i have a pl/sql function that can return multiple rows

may be the syntax will be like

create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;

..code to return the result of above statement to calling program..

end;

and functions will be called as

select candidateid from .. where candidateid in( select multiple());

View 13 Replies View Related

PL/SQL :: Function That Return Multiple Rows

Jun 6, 2013

I need a function that should return output of this query

SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;

This above function return 30 rows and I am not giving any input

Function using cursor,pipeline

View 5 Replies View Related

SQL & PL/SQL :: Remove Special Chars Using Regular Expression

Apr 9, 2013

Can we use regular expression to eliminate all characters other than -

A to Z
0 to 9
Special characters like - &,%,~,@,#,$,^,*,_,+,=,,/,<,>

Example

String - TEST@#_~````}{!!!12311HELLO

Expected result - TEST@#_~12311HELLO

how we can use regular expression to achieve this result.

View 4 Replies View Related

UTF-8 - Junk Characters Are Coming For Japanese Chars?

Aug 25, 2013

We have to get some data from U98 (saixdbU98) in UTF-8 format in Excel sheet.We are having queries ready for this. These queries bring data which have Japanese characters.

But when we run the Select queries through pbrun (Power Broker) then in result Japanese chars are garbled. It comes in what process we have to follow to get the result in UTF-8 format in Excel sheet.

here is the code
--
set sqlblanklines on;
SET DEFINE OFF;
set linesize 1000
set long 1000000
set pages 50000

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Passing Multiple Values To IN Clause In Function?

Jun 22, 2010

I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :

where account_code = P_CODE.

Eg. The value of :P_CODE is 'CS'.

I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.

I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0

View 3 Replies View Related

SQL & PL/SQL :: PIPE Multiple Rows At Once In PIPELINED Function?

Sep 13, 2012

I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.

Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.

Below is the test case:

CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),

[code]....

View 13 Replies View Related

Server Utilities :: SQL Loader Discard Non-numeric Chars

Sep 30, 2011

Creating a table

CREATE TABLE NEW_DATA
(INK_DATE DATE,
INV_ID NUMBER,
CUST_ID NUMBER,
AMOUNT NUMBER)

Loading data into the table with

LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'input/NEW_DATA.dat' "str '
'"
BADFILE 'log/NEW_DATA.bad'
DISCARDFILE 'log/NEW_DATA.dsc'

TRUNCATE INTO TABLE NEW_DATA
FIELDS TERMINATED BY '|'
(INK_DATE date "YYYY-MM-DD",
INV_ID,
CUST_ID,
AMOUNT)

My problem is that there are some rows (about 1%) where the columns INV_ID, CUST_ID, AMOUNT are containing non numeric characters and they end up in the BAD-file as errors.

Is there a way to make them end up in the discard file instead so I don't end up with errors but discards?Or even better load then into another table looking like this

(INK_DATE DATE,
INV_ID varchar2,
CUST_ID varchar2,
AMOUNT varchar2)

Do I need to have a WHEN-clause?

View 8 Replies View Related

SQL & PL/SQL :: Replacing Multiple Calls To Function By Table Joins

Dec 15, 2010

The following query calls the function get_meter_desc 8 times.

SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...

How can I replace the function call by the join in the main query?

View 19 Replies View Related

Reports & Discoverer :: CASE Function With Multiple Criteria?

Jun 20, 2013

I am using: Desktop / Discoverer 4.1 / Windows XP.

I am attempting to add a new calculated column and have had some success with the CASE function but need to add additional criteria.

What I have that works is:

SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' THEN Total Spend Plus Commit ELSE 0 END)

What I need to add are a few additional criteria. I attempted and failed with a few variants of this:

SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND
Task Owing Company = '534' OR '915' THEN Total Spend Plus Commit ELSE 0 END)

The three criteria points that I am looking to includea are:

•Expenditure Type = 'Supplier Rebates'
•Capitalizable = 'Y'
•Task Owing Company = '534' OR '915'

View 8 Replies View Related

Server Utilities :: Token Longer Than Max Allowable Length Of 258 Chars

Jun 23, 2011

i tried to apply a sql case statement in sql loader control file in " " the load succeed with 258 chars case or decode statement but when i add more cases it return sql loader 350 token longer than max

LOAD DATA
INFILE 'F:Vouvou20110613_102_951454.unl'
BADFILE 'F:Vouvou.pad'
DISCARDFILE 'F:Vouvou.dic'
replace
INTO TABLE vou_test_2
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
[code].......

the above one succeed when i edit the case statement as follow

ACCOUNT_2001 "CASE WHEN:ACCOUNTTYPE1='2001'THEN :REWARDAMOUNT1 WHEN :ACCOUNTTYPE2='2001' THEN :REWARDAMOUNT2
WHEN :ACCOUNTTYPE3='2001' THEN :REWARDAMOUNT3
WHEN :ACCOUNTTYPE4='2001' THEN :REWARDAMOUNT4
WHEN :ACCOUNTTYPE5='2001' THEN :REWARDAMOUNT5
WHEN :ACCOUNTTYPE6='2001' THEN :REWARDAMOUNT6
WHEN :ACCOUNTTYPE7='2001' THEN :REWARDAMOUNT7
WHEN :ACCOUNTTYPE8='2001' THEN :REWARDAMOUNT8
WHEN :ACCOUNTTYPE9='2001' THEN :REWARDAMOUNT9
WHEN :ACCOUNTTYPE10='2001' THEN :REWARDAMOUNT10 END"

i got the Error sql loader 350 token longer than max allowable length of 258 chars .

note : i cant modify the table structure to shorten the column names .

View 2 Replies View Related

SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

Dec 23, 2012

My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":

with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)

View 2 Replies View Related

Application Express :: Removing Text Messages Using Wwv_flow_api Or Multiple Delete Function?

Oct 8, 2012

I am currently using APEX 4.1 on oracle 11g XE.

By now I only know one way to delete text messages (globalization) and that is click them one-by-one and hitting the delete button.

To delete 100 messages this is really a pain, so I thought, there must be a way to multi-delete them, or just flush them out!

I searched all over the internet but I can't seem to find a way.

Is there a way to delete all text messages? For example by using wwv_flow_api or any other way?

View 0 Replies View Related

SQL & PL/SQL :: How Does Loop Works

Jun 27, 2011

[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;

Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.

View 9 Replies View Related

SQL & PL/SQL :: Query Works In 10g But Not In 11g?

Apr 29, 2012

I have the query below that works absolutely fine in Oracle 10g but is not running in Oracle 11g. Couple of things I notices about this query.

1. If I remove the alias 'FACILITY_W_CODE' in the ORDER BY clause, query works fine in 11g. If I replace 'FACILITY_W_CODE' with the actual fields - 'F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC' even then it works fine.

2. If I remove the GROUP BY clause in the inside query but keep the alias 'FACILITY_W_CODE' in the outside ORDER BY clause, the query works fine.

I am not sure where the issue is, GROUP BY or the ALIAS. Also, the alias issue seems to be only where the alias is used for multiple fields.

SELECT F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC FACILITY_W_CODE, PF.FAC_TYPE FACILITY_TYPE, PF.FAC_IDENT LOCATION_IDENTIFIER, P.DISTRICT_CODE USER_W_CODE, P.EMAIL_ADDRESS, PF.CERTIFICATION_AUTHORITY, PF.CALLBACK_TECHNICIAN, PF.CALLBACK_ENVIRONMENTAL
FROM PEOPLE P
JOIN (
SELECT I_PF.ID, I_PF.FAC_TYPE, I_PF.FAC_IDENT,

[code]....

View 5 Replies View Related

PL/SQL :: How Where Condition Works

Jun 1, 2013

How oracle retrieves data from a table.like let say a table product as

create table product(id number,name varchar2(10))

table product dont have any index on it

now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product

does both query take same time or first query will execute in less time since it has where condition

View 2 Replies View Related

Code Works In Firefox But Not IE7?

Jan 29, 2008

I am fighting with some code to work in IE7. It is currently working in Firefox.

A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.

[Code]
<html>
<head>
<style>
</style>
</head>
<body lang=EN-US style='tab-interval:.5in'>

[code].....

View 3 Replies View Related

SQL & PL/SQL :: ORA-00979 In 11G But Works Fine In 10G?

Aug 1, 2011

I tested the SQL in 11G and get the ORA-00979 (not group by error) , the same SQL is working in 10G.

553322.1 - ORA-00979 IN 11.1.0.6 BUT NOT IN 9I OR 10G
814423.1 - ORA-00979 AFTER UPGRADE TO 11G

Do we have simple soluation for it if I don't have Oracle support account?

View 1 Replies View Related

SQL & PL/SQL :: DML Statements - How Does Merge Works

Jul 23, 2013

My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.

View 3 Replies View Related

How Remap_datafile Parameter In IMPDP Works In 10g

Oct 15, 2010

I want to know how Remap_datafile paramater in IMPDP works in Oracle 10g. give me the whole scenario for two databases. One in Linux Platform and other in Windows platform. I want to import the tablesapce data from Linux to Windows Server database.

View 5 Replies View Related

SQL & PL/SQL :: How Oracle Background Process Works

Jul 12, 2011

how oracle background process works and in what sequence when we file an insert or update command?

View 4 Replies View Related

Forms :: Save Button Works Only Once?

Jun 26, 2012

declare
blk_name varchar2(60);
form_name varchar2(60);

[Code].....

this is my coding on save button. this is working fine but only once. when i enter the data second time and try to save, it didn't do anything. what should i do?

View 5 Replies View Related

SQL & PL/SQL :: SELECT Works But View Doesn't

Jun 11, 2012

I'm trying to create a view that includes both custom and delivered tables. The SQL code produces the desired results in SQL Developer. However, when I create and test the view using that code, I get very different and incorrect results. The SQL part of the view is below:

SELECT j.emplid, pt.deptid, pt.y_policy_id, pt.effdt, pt.title, pt.comments, pt.y_policy_covg, pt.doc_url
FROM psoprdefn a
, ps_job j
, ps_y_policy_tbl pt
WHERE substr(a.oprid,1,1) = '0'
AND a.emplid = j.emplid
AND j.job_indicator = 'P'
AND j.effdt = (SELECT MAX(effdt) FROM ps_job
WHERE emplid = j.emplid AND empl_rcd = j.empl_rcd AND effdt <= sysdate)
[code]....

View 8 Replies View Related

PL/SQL :: SQL Insert Works Delete Doesn't?

Jun 22, 2012

I have a website I am working on a database for, and to insert new data into the table is not a problem, even to output the table isn't an issue.

But a delete query won't work and it doesn't return any errors or echo commands that I put in the steps of the logic.

View 11 Replies View Related







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