help in simple select statement (regarding chr(10) or line break usage) [message #20844] |
Tue, 25 June 2002 08:53 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
urgent.... how to give a line break in the sql statement
Hi,
I want to īgive a line break using the statement given below.
SELECT DECODE(LINE,1,'CREATE OR REPLACE ' || TYPE || ' ' || 'NAMED' || ' ' || OWNER || '.' || NAME || ' ' || 'AS ' || TEXT ,TEXT)
FROM SYS.DBA_SOURCE WHERE NAME='MyTest' AND OWNER='test' AND TYPE='JAVA SOURCE' AND TEXT IS NOT NULL ORDER BY LINE;
The output for this statement above is
----------------------------------------
CREATE OR REPLACE JAVA SOURCE NAMED WNS.MyTimestamp AS import java.lang.String; ---first 2 lines problem
import java.sql.Timestamp;
public class MyTimestamp
{
public static String getTimestamp()
{
return (new
Timestamp(System.currentTimeMillis())).toString();
}
};
The actual output i want is (see the first 3 lines,the first line shown in the previous output has to be split into 3 lines)
Actually, the code does not return the create or replace..i am appending to the code which starts at import...
-----------------------------------------------------------
CREATE OR REPLACE JAVA SOURCE NAMED WNS.MyTimestamp
AS
import java.lang.String;
import java.sql.Timestamp;
public class MyTimestamp
{
public static String getTimestamp()
{
return (new
Timestamp(System.currentTimeMillis())).toString();
}
};
How do i achive this, i tried chr(10), but it does not show any code at all..
|
|
|
Re: help in simple select statement (regarding chr(10) or line break usage) [message #20846 is a reply to message #20844] |
Tue, 25 June 2002 09:26 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
works fine for me!
SQL> get q1
1 SELECT DECODE
2 (LINE,1,'CREATE OR REPLACE ' || TYPE || ' ' || 'NAMED' || ' ' ||
3 OWNER || '.' || NAME || ' ' ||chr(10)|| 'AS ' || TEXT ,TEXT)
4 as SOURCE_CODE
5 FROM DBA_SOURCE WHERE NAME='WRITE_ERROR' AND OWNER='MAG'
6* AND TYPE='PROCEDURE' AND TEXT IS NOT NULL ORDER BY LINE
7 /
SOURCE_CODE
----------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE NAMED MAG.WRITE_ERROR
AS PROCEDURE write_error
(p_message in varchar2)
as
file_handle UTL_FILE.FILE_TYPE;
col1 NUMBER;
Retrieved_buffer VARCHAR2(100);
v_file varchar2(100):='ora_err'||to_char(sysdate,'mm-dd-yyyy-HH-mi-ss');
BEGIN
file_handle :=UTL_FILE.FOPEN('/u01/app/oracle/admin/high9i/utlreps',v_file,'W');
UTL_FILE.PUT_LINE(file_handle,p_message );
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other stuff');
UTL_FILE.FCLOSE(file_handle);
END;
27 rows selected.
|
|
|