Home » SQL & PL/SQL » SQL & PL/SQL » Adding a new line between selected lines in sql query..
Adding a new line between selected lines in sql query.. [message #255559] Wed, 01 August 2007 03:52 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Need some assistance for my problem

I am trying to select the source from dba_source table for package and package body and spool it into a sql file which I can run on any database for creating similar packages. I am not using export/import due to limited access. The problem is I want a forward slash ‘/’ to be added after every END of the package/package body. Is there a way of doing it in SQL query or should I go for Pl/sql?

select decode(line,1,'CREATE OR REPLACE ' || text, text)  
from dba_source
where 
order by name, type, line

[Updated on: Wed, 01 August 2007 03:52]

Report message to a moderator

Re: Adding a new line between selected lines in sql query.. [message #255576 is a reply to message #255559] Wed, 01 August 2007 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select name, type, max(line) last_line
from dba_source
group by name, type
/

This gives you the last line for each object.
Just add '/' when line matches with last_line.

Regards
Michel
Re: Adding a new line between selected lines in sql query.. [message #255585 is a reply to message #255559] Wed, 01 August 2007 04:27 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Didn't got your point? Maybe I didn't made myself clear Sad

I want like this.. A slash added after every package definition ends while selecting from DBA_SOURCE table...

  CREATE OR REPLACE PACKAGE P1 As
  --
  --
  END P1;
  /
  
  CREATE OR REPLACE PACKAGE BODY P1 As
  --
  --
  END P1;
  /
  
  CREATE OR REPLACE PACKAGE P2 As
  --
  --
  END P1;
  /
  
  CREATE OR REPLACE PACKAGE BODY P2 As
  --
  --
  END P1;
  /


Re: Adding a new line between selected lines in sql query.. [message #255589 is a reply to message #255585] Wed, 01 August 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand your question.
To add after the last line, you have to know which one is the last line, I gave you the query that gives you the number of the last line.
Just join the 2 queries.

Regards
Michel
Re: Adding a new line between selected lines in sql query.. [message #255594 is a reply to message #255559] Wed, 01 August 2007 04:37 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Can you give me an example? I don't want it on the same line, where it ends... but on the next line.. may be I am getting confuse here Embarassed

Regards
Himanshu
Re: Adding a new line between selected lines in sql query.. [message #255597 is a reply to message #255585] Wed, 01 August 2007 04:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
LEAD can help out:

Warning: untested code!

set feedb off
set head off
col thetext format a4000
set lines 200
spool the_ddl.lst
select decode(line,1,'CREATE OR REPLACE ' || text, text)  
    || decode(name, lead(name) over (order by name, type, line), NULL, chr(10)||'/') thetext
from dba_source
where owner = 'HR'
order by name, type, line
/
spool off
set head on
set feedb 6
set lines 80


MHE
Re: Adding a new line between selected lines in sql query.. [message #255600 is a reply to message #255559] Wed, 01 August 2007 04:45 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Sorry, it doesn't work... I want a new line with "/" to be added whenever the type or name changes... this is adding like

CREATE OR REPLACE PACKAGE P1 AS
--
--
END P1;/


whereas I want

CREATE OR REPLACE PACKAGE P1 AS
--
--
END P1;
/


Re: Adding a new line between selected lines in sql query.. [message #255604 is a reply to message #255600] Wed, 01 August 2007 04:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you tried it? The CHR(10) takes care of the newline, right?

MHE
Re: Adding a new line between selected lines in sql query.. [message #255605 is a reply to message #255559] Wed, 01 August 2007 04:49 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Yeah it should, but it would fail for last record I suppose.. but I got an idea...let me try first from my side and if any problem shall get back..

thanks for you time...

Regards
Himanshu
Re: Adding a new line between selected lines in sql query.. [message #255606 is a reply to message #255605] Wed, 01 August 2007 04:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
himang wrote on Wed, 01 August 2007 11:49
but I got an idea...let me try first from my side and if any problem shall get back..
Here's the result when I run it as a script:

the_ddl.lst:
CREATE OR REPLACE PROCEDURE add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/

CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
	RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END secure_dml;
/

CREATE OR REPLACE TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/

CREATE OR REPLACE TRIGGER update_job_history
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
/

It seems fine by me.

Edit: Trimmed trailing spaces in the the_ddl.lst content.
MHE

[Updated on: Wed, 01 August 2007 04:58]

Report message to a moderator

Re: Adding a new line between selected lines in sql query.. [message #255607 is a reply to message #255559] Wed, 01 August 2007 04:59 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks Maaher, it worked.. I was unable to see as I was trying it in TOAD Embarassed

Just modified a bit based on my requirement

set feedb off
set head off
col thetext format a4000
set lines 200
spool c:\the_ddl.sql

select decode(line,1,'CREATE OR REPLACE ' || text, text)  || 
decode(name || type, lead(name || type) over (order by name, type, line), NULL, CHR(10) || '/') thetext
from dba_source
where owner='ORDER_SCHEMA'
and type in ('PACKAGE','PACKAGE BODY')
order by name, type, line
/

spool off
set head on
set feedb 6
set lines 80

[Updated on: Wed, 01 August 2007 05:14] by Moderator

Report message to a moderator

Re: Adding a new line between selected lines in sql query.. [message #255613 is a reply to message #255559] Wed, 01 August 2007 05:19 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Ok, now I have another problem.. in the package body, I see some of the lines have splitted in 2, whereas they are part of same line...

something like -

1	v_typ_rule_condition(v_typ_rule_condition.LAST) := CONDITION_TYPE(rec_rule_condition.rule_id,rec_rule_condition.prerequisite_rule_id,rec_rule_condition.rule_name,rec_rule_condition.rule_threshol         
2 d,                                                              
3 rec_rule_condition.rule_start_date,rec_rule_condition.rule_end_date,rec_rule_condition.condition_id,rec_rule_condition.compariso
4 n_operator,  


Is there another way of getting this output in single line?

Regards
Himanshu

[Updated on: Wed, 01 August 2007 05:19]

Report message to a moderator

Re: Adding a new line between selected lines in sql query.. [message #255614 is a reply to message #255613] Wed, 01 August 2007 05:20 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Increase the linesize.

MHE
Previous Topic: Please explain UPDATE query (merged by LF)
Next Topic: EMP Table
Goto Forum:
  


Current Time: Sun Dec 04 15:05:57 CST 2016

Total time taken to generate the page: 0.20877 seconds