Home » SQL & PL/SQL » SQL & PL/SQL » want to print dynamic sql query (oracle 10g)
want to print dynamic sql query [message #295454] Tue, 22 January 2008 06:33 Go to next message
piscean_n
Messages: 36
Registered: December 2007
Member
BEGIN
  v_bill_cycle_end_date DATE := TO_DATE('10/12/2008','MM/DD/YYYY');
    v_query               VARCHAR2(500); 
  
   v_query := 'UPDATE REQUESTS_DUP SET status = ''PI'' WHERE NEXT_INVOICE_PRINT_DATE = :1';

   EXECUTE IMMEDIATE v_query using v_bill_cycle_end_date;
   commit;
   DBMS_OUTPUT.PUT_LINE(' '||v_query);


I want to print this sql query after executing along with the input date.like this

UPDATE REQUESTS_DUP SET status = 'PI' WHERE NEXT_INVOICE_PRINT_DATE = 10/12/2008
Re: want to print dynamic sql query [message #295456 is a reply to message #295454] Tue, 22 January 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Why execute immediate and not just the update statement?
2/ What is the problem with dbms_output?
(3/ Do not commit inside a procedure, the caller must do it)

Regards
Michel
Re: want to print dynamic sql query [message #295616 is a reply to message #295456] Tue, 22 January 2008 22:19 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
dbms_output.put_line has output

UPDATE REQUESTS_DUP SET status = 'PI' WHERE NEXT_INVOICE_PRINT_DATE = :1


I'am using execute immediate since i've to execute query
and to print this query also.
Re: want to print dynamic sql query [message #295629 is a reply to message #295454] Tue, 22 January 2008 22:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try REPLACE function .

Thumbs Up
Rajuvan
Re: want to print dynamic sql query [message #295637 is a reply to message #295629] Tue, 22 January 2008 23:28 Go to previous message
piscean_n
Messages: 36
Registered: December 2007
Member
thank you.it worked.
Previous Topic: Privileges for Materialized views
Next Topic: how to create a table within a procedure
Goto Forum:
  


Current Time: Fri Dec 09 17:35:17 CST 2016

Total time taken to generate the page: 0.19924 seconds