SQL & PL/SQL :: Procedure That Will Format SQL
Feb 9, 2011
plsql package with a procedure or function that can format sql code. I am looking for two things:
1) I want to pass a sqlid or (address,hash_value,child_number) to a package fuction/procedure and have it return the sql from v$sqltext in a formatted manner.
2) I want to pass a sql statement to a routine and get it back formatted.
I don't need lots of flexibility in how it is formatted, don't really care if commas are leading or trailing, don't care if indentation is 2,3,4 spaces. I just want something readable.
Am willing to take any set of datatypes for parameters. A refcursor going in or coming out, clob in or out, varchar2 in or out. Anything is better than what I got now cause right now I am limited to cut/paste into Toad or a web page. I am looking for a callable plsql routine. An oracle supplied package would be nice but they have never provide this to my knowledge.
View 6 Replies
ADVERTISEMENT
Jan 30, 2009
I am wanting to write a procedure which takes in a TIMESTAMP datatype as a parameter. I only really want the time, not the date part.I am struggling to understand however what format the inserted TIMESTAMP would take. E.g
function_name( 'timestamp')
--would this be;
function_name('12:00');
--or something along those lines?
View 1 Replies
View Related
Dec 12, 2012
How to create a procedure to read data from database and export it into .csv format without using utilities
View 15 Replies
View Related
Oct 11, 2010
How to send email in HTML format as an attachment/email body by executing oracle anonymous procedure
View 1 Replies
View Related
Sep 20, 2013
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
View 3 Replies
View Related
Mar 6, 2010
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
View 8 Replies
View Related
Sep 6, 2013
I have the following requirement.Say I have the following procedure created.create or replace procedure myproc (p1 varchar2, p2 varchar2) is begin
find_the_proc_name();
end;
In the above myproc, using find_the_proc_name() i should print the parent procedure name (or function) calling along with the parameters passed.
View 5 Replies
View Related
Jun 25, 2012
DB version:11g
can we create a procedure inside a procedure...if not is there any alternative??
in db2 it is allowed so do oracle support this????
View 5 Replies
View Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Jul 10, 2011
We have a production database that have : NLS_LANGUAGE=FRENCH_FRANCE.WE8ISO8859P1.
We use (INSERT, UPDATE) arabic and french languages, and it works properly.
When I issue SQL statments to retrieve arabic data (with SQL*PLUS), it works and it returns correct arabic format.
When I use PHP, with the same small query, the arabic format is not correct.
I've tried changing the encoding characters on my browsers (IE and FF) and it's still incorrect.
View 4 Replies
View Related
Jan 19, 2011
I received a POS csv file with XML format that contain a multiple customer column and information within a csv file. I need to extract store number and associate ID from a csv column. I had already loaded the csv file to a external table and I am stuck on how to retrieve the data to a stage table along with other csv column.
For example, I need to pull data from col1, col2, col3, and col4 which col4 contain data for store_numner and associate id from a external table to a staging table which my staging table have col1, col2, col3, store_nbr column, and associate id.
View 5 Replies
View Related
Apr 19, 2013
In one of the query used like below is it correct or any need to be correct for date format? currently it's returning two tyoe of sets 1.NUll 2.01-JAN-00
SELECT withdrawn_date
FROM
csd where NVL(csd.withdrawn_date,'01-JAN-1900') = '01-JAN-1900';
View 15 Replies
View Related
Feb 8, 2012
How do I format a date which is this format to 2/18/2012 to 18/2/2012 and still keep the field as Date.
View 3 Replies
View Related
Jan 11, 2013
I need to format a value using Regular Expression.
9911223344, 9911223344
9911223344
11223344Result
(99) 1122-3344, (99) 1122-3344
(99) 1122-3344
1122-3344
View 4 Replies
View Related
Mar 19, 2012
I wanted to Compile a Procedure within another procedure.
Step 1:
CREATE OR replace PROCEDURE Compile_test
IS
var1 NUMBER(20);
BEGIN
SELECT user_id
INTO var1
FROM dummy;
dbms_output.Put_line('the output is '
||var1);
END;
Step 2:
CREATE OR replace PROCEDURE Compile_test_in (proc_name VARCHAR2)
IS
var2 VARCHAR2(20);
BEGIN
var2 := 'proc_name';
EXECUTE IMMEDIATE ('alter procedure ||var2|| compile');
END;
Step 3:
exec compile_test_in ('compile_test')
When trying the step 3, i am getting the below error message.
Error at line 1
ORA-04050: invalid or missing procedure, function, or package name
ORA-06512: at "MUTHU.COMPILE_TEST_IN", line 6
ORA-06512: at line 1
View 5 Replies
View Related
Oct 19, 2010
can we call a procedure within a procedure in a package or does it have to be declared before it can be called in the body,where can i find an example
View 9 Replies
View Related
Dec 15, 2011
how can I call a procedure from another procedure? whats the syntax?
View 19 Replies
View Related
May 29, 2013
I need to create a procedure that drops another procedure as follows:
create or replace procedure drop_any_proc (proc_name varchar2) is
drop procedure proc_name;
end;
View 5 Replies
View Related
Mar 16, 2012
i need to clarify that, we can call a procedure inside a procedure, when i am using inside a package,whether i have specify the called procedure in the Package specification?
View 5 Replies
View Related
Jun 15, 2007
decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,
After executing this statement. I get negative values if cancel time is null
I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time
View 1 Replies
View Related
Nov 23, 2009
I 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 Related
Sep 6, 2012
i see my dates in Oracle 11g like this: 2012-December-30 (in all my tables)
how to configure Oracle that i can see it like this: 30/12/2012
View 1 Replies
View Related
Nov 22, 2012
I have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.
View 5 Replies
View Related
Feb 27, 2012
How can I check if date is in correct format ? I have In parameter for function. It is date. Example:
2011-11-01
How can I check this format ?
View 3 Replies
View Related
Mar 1, 2010
iam having a table A where column ABC is a varchar field with varchar2(50).
currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18
now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.
View 9 Replies
View Related
Jul 26, 2013
I have an amount field and I want the o/p in the format of $9,999,999.99
like for 1500 it should display $1,500.00
for 25000 it should display $25,000.00
for 25000000 it should display $25,000,000.00
Test case
add comma from last 3rd position ,and append a $ in the start with two decimal places
View 15 Replies
View Related
Nov 22, 2012
i need to extract data in excel file through sqlplus .
View 2 Replies
View Related
Nov 9, 2010
I want to change the date format at database level in 10g express edition.
When i try to run the command,
ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY',
it throws error like specified initialisation parametr is not modifiabale with this option.
what are the other ways to chnage the database date format to the desired one.?
View 17 Replies
View Related
May 6, 2010
follwing is my structure and data in table. COLA has datatype varchar2.data in table is date..... now i want to convert all the dates in 'yyyymmdd' format.
IS it possible?
SQL> select * from taba;
COLA
---------------
1944-02-10
1982-07-20
08/24/1974
03/14/1957
22-Nov-79
View 6 Replies
View Related
Jul 25, 2011
While am working with Decode just found something, why it is returning like this.
I Ran this query where i have used all the date format as DD/MM/YYYY
SELECT DECODE(TO_DATE('25/04/2001','DD/MM/YYYY') ,
TO_DATE('01/01/1900','DD/MM/YYYY'), NULL ,
TO_DATE('25/04/2001','DD/MM/YYYY')) TEST
FROM DUAL
Quote:
TEST
-------------
25-APR-01
But the resule i got in DD-MON-YY
i tried searching this forum with searching strings Decode date format.. or similer i found nothing.
View 4 Replies
View Related
