PL/SQL :: Oracle Function That Returns Current Fiscal Year In Format
Mar 14, 2013Is there an oracle function that returns the current fiscal year in format (2012/2013)?
View 9 Replies
ADVERTISEMENT
PL/SQL :: Oracle 11g Table Function Returns No Records On First Call?
Jul 13, 2012On a Oracle 11g R2 I've a table function ( PIPELINED ) returning rows selected from a table.The first time the function is selected, in a session ( I've tried to disconnect and log in again ), it returns no rows.I've tried to log the call using DBMS_OUTPUT and from what I see the select on the table function returns no rows and no output is printed. So I presume Oracle is not calling the function.
The same function on a similar environment ( same db versions, patches and database structure ) works fine. The second environment is a production environment so it has more memory and some other settings enabled.
SQL & PL/SQL :: Getting Query For Current Year Data Up To Sysdate?
Jun 19, 2012I have a table for exampl,
emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012
i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.
my output like this.....
emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)
SQL & PL/SQL :: Checking If Month And Year Is Greater Or Equal To Current One?
Mar 7, 2013This is the description for the procedure:
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace
PROCEDURE ADD_REWARD_sp(
p_proj_id IN NUMBER,
p_pledgeAmt IN NUMBER,
p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
PL/SQL :: Create Constraint That Checks To Ensure (Year) Entered Is Less Or Equal To Current?
Aug 6, 2013, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express.
CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY, Year NUMBER(4), CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL);
*ERROR at line 4:ORA-00907: missing right parenthesis
Changing Year Format
Nov 23, 2009I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
View 4 Replies View RelatedSQL & PL/SQL :: Convert Year / Quarter Number To Date Format
Oct 5, 2010I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies View RelatedSQL & PL/SQL :: Function Returns Searched String?
Nov 6, 2010I just think about write a function with gives me a searched string from table.
select col1, col2 from the_table
col1 col2
--------------------------
SMS yes
Melodies no
Java build
...
And I would like to make a function or something like that where input parameter is from col1 and result is col2. For instance:
select the_function('SMS') from dual;
Result: yes
how to write it?
SQL & PL/SQL :: Change Value That Function USER Returns
Nov 25, 2011I have an existing database with several hundred tables and triggers on the tables in the schema S1. Most of the triggers are for storing audit information USER and SYSDATE in the respective columns.
We have built procedures what will accept incoming XML parameters from a BPMS application on the intranet which will have information about which table to update, which columns to update, what values to update and what the O/S user id is. The O/S user ids are mapped to oracle user ids in a specific table. Therefore when we receive the request we can easily figure out the oracle user id. The application always connects to the database using a specified user id S2.
The trouble is, the existing tables have triggers which read :NEW.userid := USER;and whenever a DML is fired from the procedure, the function USER always returns S2, since that is the user id the BPMS application connects to and therefore the audit columns do not capture the correct information as required.
The impact of changing all the existing triggers/procedures will be a mammoth task including regression testing and will certainly not be approved by the project sponsor.
override the value that USER returns? Using session contexts, etc? If so, we can possibly set it at the point of connect and have to do nothing else.
SQL & PL/SQL :: Function That Returns Source Tables From A Query
Oct 16, 2013I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
View 14 Replies View RelatedSQL & PL/SQL :: Passing Hierarchy As Input To Function That Returns XML?
Aug 3, 2012I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.
AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3
[code]....
Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.
I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.
1. What is the best to way (design)to pass input parameter for the function.
2. How to generate hierarchy in xml using DBMS_XMLGEN
PL/SQL :: Sample Function Returns Many Times Of Expected Value
Nov 30, 2012The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.
For example, we have thee tables with the following results:
Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000)
Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000)
Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)
The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"
I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?
PL/SQL :: Two Consecutive Numbers Are Transposed Function Returns True
Jul 6, 2012oracle version 11gr2.In the below sample data column a,b if there is Two (2) consecutive numbers are transposed in column B from A it should return string 'true'.
with t as
(
select '123456789' a ,'123476581' b from dual
union all
select '123456789' ,'123465789' from dual
union all
select '332211' ,'332121' from dual
union all
[code]....
SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body
Jul 26, 2011CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
Client Tools :: Debug Function Which Returns Table Of Records?
Apr 24, 2012I want to debug a function which returns the table of records. When I try to add the parameters and run the debug it gives the error as
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
if there is any way to debug the function which returns table of records.
PL/SQL :: Function That Returns The Check Digit Of 9-digit Number
Jan 17, 2013I would like a pl/sql function that returns the check digit of a 9-digit number?
Look at
[UR]......
For example number 109409714 (9 digits) should do check digit. I want validation of check digit, & want to return the check digit.
Amount Value Format Without Using To_char Function
Mar 1, 2012I want to convert an amount value in 99,999,999,990.99 format without using TO_CHAR function. I was trying to use regex. Is there any other function through which I would be able to do this?
View 6 Replies View RelatedSQL & PL/SQL :: Function Returning CLOB - Invalid Format
Jan 22, 2011I have a problem when trying to create a PLSQL function based on an XML extraction query.
I have three dummy tables:
SQL> get create_address
1 create table ADDRESS
2 (
3 id NUMBER not null,
4 house_number NUMBER,
5 house_name VARCHAR2(20),
6 street_name VARCHAR2(30),
[code]....
And the following dummy data for these:
1 insert all
2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF')
4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH')
6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
[code]....
So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.
So I try, very simply, this:
SQL> get get_xml_data
1 create or replace function get_xml_data(p_id in number) return clob is
2 Result clob;
3 begin
4 select xmlroot(xmlelement("HomeData",
[code]....
And, alas, i'm greeted by this:
SQL> /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION GET_XML_DATA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5 PL/SQL: SQL Statement ignored
24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
SQL>
I've tried to redo the query in several different ways but so far nothing.
SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp
May 30, 2012What is the difference between the following . In my schema all are giving the same results with some different format
SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month
Sep 22, 2010I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
View 5 Replies View RelatedForms :: How To Focus On Current Record In Oracle
Jul 4, 2011How to focus on current record in oracle forms.
for ex:- I have 10 records in line level.i want to focus my cursor on particular like (5th record) how to possible in form.
Is there any built-in like current_record?
SQL & PL/SQL :: Display The Current Date With Post Fix Notation In Oracle?
Jul 2, 2013Is there any way to display the current date with post fix notation in oracle?
Ex : 2nd ,5th , 3rd , 1st , 8th ,
select to_char(sysdate,'DD') from dual;
Forms :: How To Get Current Cursor Position In Oracle 10g Form
Feb 25, 2010I want to get current mouse cursor position dynamically,what should i do.
View 3 Replies View RelatedServer Administration :: Change Current Redo Log Size In Oracle Mount Stage
Jun 8, 2012We have one primary oracle database 10.2 and standby by database with no data guard. Initially we have 2 redo log group in primary and standby database.
We have recently add 2 more redo log and increase the size of log member from 50m to 200m in primary database. We don't have any problem in primary database.but in standby database we face a problem because we cannot open it. It always in mount stage in which . How we change the size of current redo log because we can't run. Alter system switch logfile command in mount stage.
SQL & PL/SQL :: Why Greatest Returns Null As Greatest Value While Max Returns 1
Aug 4, 2010SQL> select greatest ( 1000,null) from dual;
GREATEST(1000,NULL)
-------------------
SQL> with t as
2 ( select 1 a from dual union all
3 select null from dual)
4 select max(a) from t;
MAX(A)
----------
1
Why greatest returns null ( as the greatest value among the list of values) as the greatest value while max returns 1 in the above case?
Make Oracle Tree In Specified Format?
Apr 6, 2013---------------------------script of table for tree--------------
create table eqpt_mast
(
EQPT_ID VARCHAR2(16),
EQPT_CODE VARCHAR2(9),
PARENT_CODE VARCHAR2(9),
[code]....
output of tree like:-
grandf(0)
|__
---father1(1)
[code]...
i want to make tree like the above format.In the above tree "(value)" shows the code_type i.e. level of the tree..
version----Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SQL & PL/SQL :: Convert A HTML To PDF Format Using Oracle 9i?
May 30, 2011convert a HTML to PDF format using plsql using oracle 9i.
View 9 Replies View RelatedForms :: How To Format Drive Using Oracle 10g
Aug 4, 2010how to format a drive using oracle forms 10g and shutting down the remote pc and a personal pc using oracle forms 10g. i am using windows XP2 as os.
View 2 Replies View RelatedSQL & PL/SQL :: Send Mail As HTML Format In Oracle?
Mar 23, 2012I want to send the mail as HTML format in oracle.
The email message column is in clob datatype .
if i click the column it shows the text output(various tags ,symbols)
if i click the HTML it shows the correct format .,
i m sending the mail using that message column .,the mail Received with the text format ,actually i need it in the html format., How to send HTML output?
Generate Oracle Report And To MS Word Format?
May 25, 2011I would like to generate oracle report and to MS word format? new xml format of MS office?
View 2 Replies View Related