SQL & PL/SQL :: Send Again Function Of Equivalent Of DATEADD
Apr 19, 2011Is it possible to send again the function of equivalent of DATEADD in sql for plsql??
View 3 Replies
ADVERTISEMENT
SQL & PL/SQL :: Left Function Equivalent In Oracle 9i
Dec 22, 2006Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?
View 10 Replies View RelatedSQL & PL/SQL :: Set / Send / Input CLOB Value To Function?
Jan 19, 2012Trying to set/send/input CLOB value to function.
I tried:
Clob size is about 4-5KB of data.
DECLARE clobz CLOB:=EMPTY_CLOB();
BEGIN
SELECT '<claim>'+
'<lbo_id>34</lbo_id>'+
'<claim_type_id>11</claim_type_id>'+
'<claim_subtype_id>11</claim_subtype_id>'+
[code]...
Error:
Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
[code]...
SQL & PL/SQL :: ORA-00904 / DATEADD / Invalid Identifier
Oct 12, 2011i have this code.
select * from openquery(serverlinkeodwmain, 'SELECT COUNT(CNTR_N) cnt FROM OPS_DLY_AGING_DTL
WHERE BATCH_ID = DateADD(day , datediff(day, 0, getdate()), 0)')
the error
OLE DB provider "MSDAORA" for linked server "serverlinkeodwmain" returned message "ORA-00904: "DATEADD": invalid identifier".
is it because dateadd function is sql and i need to change it to oracle? if so how do i do that? i dont know much about oracle.
i need to get the current date without time. my records look like this
2011-08-16 00:00:00.000
PL/SQL :: Send Regexp Back Reference To A Function?
Jul 23, 2013Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf
[code]...
PL/SQL :: Best Way To Send A Procedure / Function Output To The Application
Sep 8, 2013What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.
View 49 Replies View RelatedSQL & PL/SQL :: Hexadecimal Equivalent
Jun 16, 2011I need to get the output as ú (Lowercase U-acute) using oracle sql.
SQL> select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('<x>')) from dual;
where <x> represents the hexadecimal equivalent for the character 'ú' ,
PL/SQL :: Top N Percent Equivalent
Jul 16, 2013what is the equivalent of Top n Percent in Oracle sql 11g. Here is my requirement:
I have to find stores contributing top 20% of sales: Store Sales PercetageABC200(200/380)*100=52%XYZ100(100/380)*100=26%PQR50(50/380)*100=13%dddd20(20/380)*100=5%rrrr10(10/380)*100=2%
In the above example I have to get only store ABC as this store alone is contributing more than Top 20%If I change the requirement to Top 70% I have to get store's ABC and XYZ.
Upgrade :: 10g Companion CD Equivalent In 11g?
Sep 25, 2013I am upgrading from 11.2.0.1 to 11.2.0.3 (64 bit) on Windows (64 bit) I planned to install Oracle 11.2.0.3 on new home and i am not sure wheteher i have to install: Oracle Gateway and Oracle Examples on the 11.2.0.3 new home. I am not using any non - oracle so i may not need Oracle Gateway. I am not sure about examples media . Does it have only examples to learn (or)will it have any optional components which we may require (like companion CD in 10g)? What about the ODBC and com components will it be installed with the database media itself? After DB upgradation is it possible to install oracle gateway/examples on the same home?
View 2 Replies View RelatedSQL & PL/SQL :: What Would Be The Sybase Str Equivalent In Oracle
Mar 4, 2010What would be sybase str(approx_numeric [, length [, decimal] ]) equivalent in oracle.
Sybase call :
select str(10000.5555, 6, 3) as Test.
Tset
-------
10001
SQL & PL/SQL :: Query Must Return All Equivalent Records
Nov 26, 2012I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
[code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
SQL & PL/SQL :: Getting Equivalent DECODE For CASE Statement?
Feb 21, 2013case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age
the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode
Query To Get IST equivalent Time
Mar 20, 2013My requirment is something like below.
My Oracle DB server time is in UTC. which lags by 9:30 mins to IST.how to get tHe IST time (Asia/Kolkotta) time for that UTC timezone? I can not hard code +9:30 to UTC as this difference varies as per Daylight savings every 6 months.
PL/SQL :: Find Equivalent Of Mysql_data_seek In Oracle
Oct 23, 2012"php 5.3.x + oracle 11g R2 XE 11.2.x.x"...I'm quite new to oracle (2-4 months) and php(2 weeks).I have one query with results that needs to be reused in several parts of my website. I can't seem to find the equivalent of the mysql_data_seek in Oracle. I wanted to reset the cursor/pointer of oci_ fetch ($result) so that I can scroll the result again.So far this are what I have come up:
A. On first fetch put the the results in a php array and call the array later on.
B. Do the query again.
C. Keep on looking for a mysql_data_seek equivalent and fail.
I'm leaning towards option 'A' but I just wanted to consult the experts.
Replication :: What Is Stream's Tag Equivalent In Golden Gate
Apr 13, 2012What is the stream's "tag" equivalent in Golden gate ?My tables are already in GG replication, but i want to do few insert in to source which i dont want to replicate to target.
View 1 Replies View RelatedSQL & PL/SQL :: Date Diff (Month Not Day) Equivalent In Oracle
Jun 16, 2010I see month aging buckets in the Oracle Application I am using (Keystone time and billing). I need to do a query in directly in Oracle (Toad front end to Keystone database) using month aging buckets . I use the following in Access that matches the results in Oracle, but I need to work directly in Toad because I want to avoid having to bring over all the dates when I want to summarize by buckets.:
2-4 Months: Sum(IIf(DateDiff("m",[invoice date],forms!Reporting_Standard!txtlastdayofmonth) In (2,3),[Outstanding Gross],0))
I know about getting the difference between two dates and that works fine for day aging buckets, but I need months, which can deal with months that are different sizes.
SQL & PL/SQL :: How To Get Java Nano-time Equivalent In Oracle
Dec 11, 2011how to get java nanotime equivalent in oracle
View 2 Replies View RelatedPL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement
Jan 29, 2013Is there a save exceptions clause or an equivalent for an Insert as select* statement ?
How do I trap the errors in the below statement -
INSERT INTO copy_emp
SELECT * FROM emp;
Is it an all or nothing scenario ?
PL/SQL :: To Create Function Based Index For Group Function Columns
Jun 15, 2012Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function
Feb 4, 2013I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?
Mar 11, 2010What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View RelatedSend Mail Using SQL
Feb 23, 2009I want to send mail using plsql.I know that this can be possible by UTL_SMTP package.So I went thro some doc's & I tried this code :
PROCEDURE send_test_message
IS
mailhost VARCHAR2(64) := 'mailhost.fictional-domain.com'; /*(my computer name)*/
sender VARCHAR2(64) := 'prasadsug@yahoo.com';
recipient VARCHAR2(64) := 'prasadsug@rediffmail.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
[code]....
I got 'ora-00900:INVALID SQL STATEMENT'. which line i've done the mistake .how to rectify that?.
Send SMS Through Oracle DB?
Jul 7, 2012We can send E-mail through Oracle DB, can we send SMS through Oracle DB to any Mobile No.
View 1 Replies View RelatedSQL & PL/SQL :: Send SMS Via Oracle 10g?
Mar 5, 2012i am sending { Daily Status } via E-Mail by Oracle 10g,,,,
now My client want to Receive By sms to his mobile ,,,
Is there any possibilities to sent sms by oracle 10g ,,if exists pls sent the Code,,,or any procedure to sent sms via Oracle 10g
PL/SQL :: Possible To Send Sms Through Database
Aug 13, 2013I would like to know about the implementation of sending an SMS through pl/Sql.
View 8 Replies View RelatedPL/SQL :: How To Send A Mail
Aug 6, 2013I have a requirement how to send a mail from pl/sql procedure and that mail content will be in HTML format and the data i have to fetch from one table
View 2 Replies View RelatedSQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function
Jun 10, 2010What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
SQL & PL/SQL :: Send Records From One Table To Another
Apr 21, 2011I have requirement like we need to send records from one table to another table. for example if i have 4 records in Table A , first i need to send only 2 records to Table B then again rest 2 records to the Table B.
View 10 Replies View RelatedForms :: Send SMS And Mail Through 6i?
Feb 17, 2012how can i send sms and mail through d2k 6i form?
View 14 Replies View RelatedSQL & PL/SQL :: Send Email In Oracle?
Apr 24, 2013what is the best way to send an email in oracle?
1.utl_smtp
2.utl_mail
3.utl_tcp