Home » SQL & PL/SQL » SQL & PL/SQL » help in simple select statement (regarding chr(10) or line break usage)
help in simple select statement (regarding chr(10) or line break usage) [message #20844] Tue, 25 June 2002 08:53 Go to next message
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 Go to previous message
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.
Previous Topic: Session killing automatically
Next Topic: Subquery
Goto Forum:
  


Current Time: Fri Mar 29 07:53:16 CDT 2024