-----Original Message-----
From: Rajavardhan [mailto:rajavardhan.[Email address protected]
Sent: Monday, July 02, 2007 3:15 PM
To: 'Virendra Kumar Ratrey Noida '
Subject: RE: help in Dynamic SQL
-----Original Message-----
From: Virendra Kumar Ratrey Noida [mailto:virendra.[Email address protected]
Sent: Monday, July 02, 2007 3:01 PM
To: RAJAVARDHAN.[Email address protected]
Subject: RE: help in Dynamic SQL
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Hi
Virendra
Its works but you did small mistake
You have append semicolon in sql statement
When you write dynamic sql statement doesn't required semicolon
But for pl sql block is required
CREATE OR REPLACE PROCEDURE Proc(columnname VARCHAR)
AS
TYPE nameslist IS TABLE OF VARCHAR(20);
names nameslist;
sqlst VARCHAR(50):='';
BEGIN
sqlst:='select '||columnname||' from hr.departments';
EXECUTE IMMEDIATE sqlst bulk collect INTO names;
dbms_output.put_line('hi names'||names(1)||'hi '||names(2));
END;
/
if doesn't work please reply
though if it works reply me
waiting for your reply
Please if have any doubt send me
Cheeeeeeeeeeeeer's
Raj
Hi Rajavardhan
,
I tried like this , but it not working
CREATE OR REPLACE PROCEDURE Proc(columnname VARCHAR)
AS
TYPE nameslist IS TABLE OF VARCHAR(20);
names nameslist;
sqlst VARCHAR(50):='';
BEGIN
sqlst:='select '||columnname||' from hr.departments';
EXECUTE IMMEDIATE sqlst bulk collect INTO names;
dbms_output.put_line('hi names'||names(1)||'hi '||names(2));
END;
/
SQL> exec proc('department_name');
BEGIN proc('department_name'); END;
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "HR.PROC", line 8
ORA-06512: at line 1
Regards
Virendra R.
-----Original Message-----
From: Rajavardhan
[Email address protected]
Sent: Monday, July 02, 2007 9:36 AM
To: LazyDBA Discussion
Subject: RE: help in Dynamic SQL
Hi tarun
The following code works for
create or replace procedure proc(columnname varchar)
as
type nameslist is table of varchar(20);
names nameslist;
sqlst varchar(50):='';
begin
sqlst:='select '||columnname||' from test';
execute immediate sqlst bulk collect into names;
dbms_output.put_line('hi names'||names(1)||'hi '||names(2));
end;
if doesn't work please reply
though if it works reply me
cheeeeeeeeeeer's
Raj
-----Original Message-----
From: tarun-bansal
[mailto:oracledba-ezmlmshield-x30726400.[Email address protected]
Sent: Friday, June 29, 2007 6:21 PM
To: LazyDBA Discussion
Subject: help in Dynamic SQL
Hi
I want to get the values of one column from a table but dont know the
name
of
column at the time of creation of procedute where i want the output.
I have to use Dynamic SQL, but the problem is, the output of that query
is
more
than one records, so how can i get the required data by using dynamic
SQL.
Detail
Table is Month
There are 4 columns(A1,A2,A3,A4)
Query is << select p from month >>
and pass this p from the parameter of procedure.
So i want to get the output of that query....
Plz help me out..
Regards
Tarun Bansal
----------------------------------------------------------------------
This mail sent through Toaster-Horde (http://qmailtoaster.clikka.com/)
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DISCLAIMER:
----------------------------------------------------------------------------
-------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect
the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of
this message without the prior written consent of the author of this e-mail
is strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. Before opening any mail and
attachments please check them for viruses and defect.
----------------------------------------------------------------------------
-------------------------------------------
Oracle LazyDBA home page