easy table based update statement??

Hello,

I have 2 ways of updating data I'm using often

1) via a cursor on TABLE1 update fields in TABLE2
2) via an some of variables ...

SELECT @.var1=FLD1, @.var2=FLD2 FROM TABLE1 WHERE FLD-ID = @.inputVAR
UPDATE TABLE2
SET FLDx = @.var1, FLDy = @.var2
WHERE ...

Now I have a system with 2 databases and I need to update table DB2.TAB
based on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,
I thought it would be much easier to get the required data immediately from
DB1.TAB in the update-statement of DB2.TAB ... but the way to do that
confuses me. I've checked books online and a lot of newsgrouppostings
giving good information but still I keep getting errors like this ...

The column prefix 'x.ADS' does not match with a table name or alias name
used in the query.

while executing the following statement ...

UPDATE DB2.dbo.TAB
SET
FLD1 = x.FLD1,
FLD2 = x.FLD2,
...
FROM DB1.dbo.TAB x, DB2.dbo.ADS
WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
@.InputParameter

So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
table DB1.TAB
AND I only want to update the row in DB2.TAB with the unique keyfield
IDOFTAB2 equal to variable @.InputParameter

Do you see what I'm doing wrong?

--
Thank you,
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> The column prefix 'x.ADS' does not match with a table name or alias name
> used in the query.
> while executing the following statement ...
> UPDATE DB2.dbo.TAB
> SET
> FLD1 = x.FLD1,
> FLD2 = x.FLD2,
> ...
> FROM DB1.dbo.TAB x, DB2.dbo.ADS
> WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> @.InputParameter
> So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> table DB1.TAB
> AND I only want to update the row in DB2.TAB with the unique keyfield
> IDOFTAB2 equal to variable @.InputParameter

The string x.ADS is not in the part of the query you posted. Maybe you
should post the complete query?

But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
but not in the WHERE clause. That could cause some unexpectedly bad
performance, as you get a cartesian join.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply Mr Erland Sommarskog.

I changed the names of the fields and tables in order to eliminate dutch
databasenames and in order to avoid confusion about the tablename ADS which
appears in both databases, but which isn't an exact copy. DBIngeven is made
only to insert new rows. DBLezen is made only to read data earlier entered
in DBIngeven and other databases. So I'm sorry I made the statement unclear
by replacing tablename ADS by tablename TAB, forgetting to do it
everywhere.in the statement.

This is actually the statement I wrote to synchronize the data, the
statement causing the error "The column prefix 'x.ADS' does not match with a
table name or alias name used in the query.". Might the problem being
caused by the fact that both tables, being in different databases, have the
same name?

CREATE STORED PROCEDURE USP_SYNC
@.ADIDLezen int
AS
DECLARE @.ER int

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
@.ADIDLezen

SET @.ER = @.@.ERROR

Thanks,
Perre.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B3F1B592928Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > The column prefix 'x.ADS' does not match with a table name or alias name
> > used in the query.
> > while executing the following statement ...
> > UPDATE DB2.dbo.TAB
> > SET
> > FLD1 = x.FLD1,
> > FLD2 = x.FLD2,
> > ...
> > FROM DB1.dbo.TAB x, DB2.dbo.ADS
> > WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> > @.InputParameter
> > So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> > table DB1.TAB
> > AND I only want to update the row in DB2.TAB with the unique keyfield
> > IDOFTAB2 equal to variable @.InputParameter
> The string x.ADS is not in the part of the query you posted. Maybe you
> should post the complete query?
> But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
> but not in the WHERE clause. That could cause some unexpectedly bad
> performance, as you get a cartesian join.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 3 Dec 2004 14:30:43 +0100, Perre Van Wilrijk wrote:

> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
>@.ADIDLezen

Hi Perre,

Change x.ADS.ADID to x.ADID (or x.ADS_ADID - I can only guess here).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> I changed the names of the fields and tables in order to eliminate dutch
> databasenames and in order to avoid confusion about the tablename ADS
> which appears in both databases, but which isn't an exact copy.

Instead you caused confusion. (And Dutch is not a problem to understand,
at least as it's single. Double-dutch may be more difficult...)

> This is actually the statement I wrote to synchronize the data, the
> statement causing the error "The column prefix 'x.ADS' does not match
> with a table name or alias name used in the query.". Might the problem
> being caused by the fact that both tables, being in different databases,
> have the same name?

No, but because you the alias in the wrong place:

> FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
> @.ADIDLezen

x.ADS.ADID would refer to a table ADS owned by the user x.

A tip is to always use aliases. They usually make queries less verbose,
not the least when you use three-part names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Thanks Hugo,

Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
messages given back by SQL Server wasn't clear enough for me to locate the
synthax error I made. Great you saw it without knowing my table and field
names. Sorry to have bothered you with such a stupid mistake.

I also made an alias for DBLezen making the WHERE-clause better readable and
maintainable.
A little bit strange now I can't use the y alias in the SET clause (eg SET
y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
refers to the table name after the UPDATE-word, which doesn't seem to be
aliasable. So this on works fine ...

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B4AD8233CE9Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > I changed the names of the fields and tables in order to eliminate dutch
> > databasenames and in order to avoid confusion about the tablename ADS
> > which appears in both databases, but which isn't an exact copy.
> Instead you caused confusion. (And Dutch is not a problem to understand,
> at least as it's single. Double-dutch may be more difficult...)
> > This is actually the statement I wrote to synchronize the data, the
> > statement causing the error "The column prefix 'x.ADS' does not match
> > with a table name or alias name used in the query.". Might the problem
> > being caused by the fact that both tables, being in different databases,
> > have the same name?
> No, but because you the alias in the wrong place:
> > FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> > WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID
=
> > @.ADIDLezen
> x.ADS.ADID would refer to a table ADS owned by the user x.
> A tip is to always use aliases. They usually make queries less verbose,
> not the least when you use three-part names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 7 Dec 2004 13:55:55 +0100, Perre Van Wilrijk wrote:

(snip)
>I also made an alias for DBLezen making the WHERE-clause better readable and
>maintainable.
>A little bit strange now I can't use the y alias in the SET clause (eg SET
>y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
>refers to the table name after the UPDATE-word, which doesn't seem to be
>aliasable. So this on works fine ...
> UPDATE DBLezen.dbo.ADS
> SET
> ADS_HR = x.ADS_HR,
(snip)

This should work as well:

UPDATE y
SET
ADS_HR = x.ADS_HR,
(...)
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

Personally, I prefer to always use the UPDATE .. FROM syntax this way, so
I won't forget to use it if a self-join is included (than it becomes
mandatory to use the alias in the UPDATE clause).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
> messages given back by SQL Server wasn't clear enough for me to locate the
> synthax error I made.

It's kind of difficult for SQL Server to second-guess what you really meant.

But it is true that the error messages from SQL Server are not always
crystal clear. When it comes to true parsing errors, the obscureness of
the messages partly comes from the too rich syntax of T-SQL. A typo can
lead to some legal syntax SQL that you are not aware of, but then lead
a syntax error further ahead.

For the error message you got, I guess the main problem is that the
message points to the first line in the UPDATE statement, instead of
the line where the error is. That makes it more difficult to spot the
error.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> It's kind of difficult for SQL Server to second-guess what you really
meant.
> But it is true that the error messages from SQL Server are not always
> crystal clear. When it comes to true parsing errors, the obscureness
of
> the messages partly comes from the too rich syntax of T-SQL. A typo
can
> lead to some legal syntax SQL that you are not aware of, but then
lead
> a syntax error further ahead.

It might not be easy to parse especially my code :-)
I can imagine that.
I once believed I could give people using my programs always clear
messages, for each kind of problem. We all have to learn.

Dynamically substitute table name in Cursor Defn.

hi,
I am looking for a soln,where in I can substiute the name of the table
dynamically with a paramter passed through the procedure in the Cursor
Defn
as shown below.

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

CURSOR TCN_cur IS

SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM
p_table_name py;

here note the p_table_name used for cursor declaration(is passed as a
parameter to the proc)...
Any soln or any other way of implemention this is appreciated.

Rgds
VivianYou need to use dynamic SQL:

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

TYPE refcur IS REF CURSOR;
tcn_cur refcur;

BEGIN

OPEN tcn_cur FOR
'SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM '||p_table_name||' py';
...

By the way, I'm not sure this does what you intend:

error_msg VARCHAR2(300) := SQLERRM;

The value of SQLERRM will be assigned at the time the variable is declared - but there hasn't been any error yet, so the value will always be "ORA-0000: normal, successful completion". Or maybe that's the default value you want if no error occurs?|||Hi,
Thks for ur reply.

I want this cursor fo looping record by record.How do I do that. eg

CREATE OR REPLACE procedure test (tab_id in integer)

TYPE refcur IS REF CURSOR;
c_Data refcur;


begin

select table_name into stg_table from table_source where table_type='ST' and table_id=tab_id and src_id=2;

open c_Data for 'Select * from '||stg_table||'order by record_id;';
loop
fetch c__data into rec_id ;
EXIT WHEN c_gib_data%NOTFOUND;
----
---- /* Some actions
end Loop;

When I complie,I get a error at the cursor where the sql statment is created

BEGIN test_dym(1); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "USR.TEST", line 46
ORA-06512: at line 1|||1) You need a space before 'order'
2) You should not put a semi-colon in the SQL string

open c_Data for 'Select * from '||stg_table||' order by record_id';|||Thnks Andrew.I t worked.|||Hi,
Suppose if I have to do the follwoing,how do I implment using REF cursor.Actually I need to pass a IN paramter to the cursor. Can this be implemeted in REF cursor

CURSOR c_Data( P_Serial_Number IN VARCHAR2 ) IS
SELECT
record_id,record_history_status ,updation_ico_count
FROM
prod_data
WHERE
record_id = ( SELECT max(record_id)
FROM
prod_data
WHERE
Serial_number LIKE P_Serial_Number);|||You don't pass a parameter to the ref cursor, instead you use a BIND VARIABLE and the USING clause. Here is a simple example:

DECLARE
TYPE refcur IS REF CURSOR;
c refcur;
r dept%ROWTYPE;
BEGIN
OPEN c FOR 'SELECT * FROM dept WHERE deptno > :mindeptno' USING 10;
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r.deptno);
END LOOP;
CLOSE c;
END;
/

In this example, the value 10 is used for the bind variable :mindeptno in the query. In your example this would be used like this:

OPEN c_data FOR 'SELECT ... WHERE Serial_number LIKE :x' USING P_Serial_Number;|||Hi,
I have defined the cursor as ref cursor as follows

TYPE c_Data_refcur IS REF CURSOR; --Cursor with dynamic table defn
c_Data c_data_refcur;

TYPE c_View_Data_refcur IS REF CURSOR; --Cursor with Dynamic
table defn and IN Para
c_View_Data c_View_Data_refcur;

Begin

--The value for stg_table is there with me --
open c_Data for'Select * from '||stg_table||' order by record_id';
loop
fetch c_Data into rec_id ; (Q1 : rec id should be deifned as what %rowtype as the table name is generated dynamically)

EXIT WHEN c_data%NOTFOUND;

IF v_transformation_boolean = FALSE
THEN
SELECT Transfer_ID_Seq.NEXTVAL INTO v_TransferSeq FROM dual;
v_start_record_id := stg_table.record_id; (Q2: How do I specify the fetch cursor value here?)
v_transformation_boolean := TRUE;
END IF;

v_end_record_id := stg_table.record_id; (Q3 How do I specify the fetch cursor value here?)

Q4 : Here I have to inialize the field values as null for the second cursor which has a In paramter.Here again the table name is dynamically populated for the cursor defn.)
c_GibView_Data.updation_ico_count := NULL;
c_GibView_Data.record_history_status := NULL;
c_GibView_Data.ico_status:= NULL;

--2 cursor having IN Parameter--
OPEN c_View_Data for 'SELECT record_id, record_history_status ,updation_count, status FROM'||prd_table||'WHERE record_id = ( SELECT max(record_id) FROM '||prd_table||'WHERE Serial_number = :P_Serial_Number)' using c_Data.serial_number;(This defn is not allowed)
FETCH c_View_Data INTO {c_GibView_Data_Rec};(Q5: how do i defin this as %row type as the table is dynamically populated)
CLOSE c_View_Data;|||Q1) You cannot declare a variable dynamically using NDS, you would need to know the structure of the record in advance. You need to use DBMS_SQL to handle this situation.
Q2) If you had a record declared like v_rec mytable%ROWTYPE into which you had fetched a row, then you would say v_rec.record_id
Q3) Same as Q2
Q4) Don't understand the question.

Dynamic value on Tool tip

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Quote:

Originally Posted by sajithamol

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Check the following link

http://msdn2.microsoft.com/en-us/library/system.windows.forms.tooltip.settooltip.aspx

DYNAMIC USE

I would need go along the current sql server using a cursor (another
alternatives will be welcomed) and changing of database
Something like that:
DECLARE @.BD AS CHAR(20)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
USE @.BD
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
But it isn't working.
Regards,The GO command signals the end of a batch of T-SQL statements. You put yours
in the middle of what you wanted to be the batch, splitting it in two.
But that's not the real problem. The trouble starts with you considering
using a cursor. You don't need to.
I could very well guess what you're trying to do, but I'd instead prefer you
to tell us.
So, here it goes:
What are you trying to do? Display a list of table names for all your
databases?
ML
p.s. if you answer 'yes' to my last question, we're half way there. :)|||DECLARE @.BD varCHAR(100)
declare @.string nvarchar(100)
set @.bd=3D''
declare cursorbd cursor for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT
IN('MASTER','PUBS','MODEL','TE=ADMPDB','
MSDB','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status =3D 0
begin
set @.string =3D N' USE '+@.BD+''
--print @.string
exec sp_executesql @.string
SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END=20
CLOSE CURSORBD=20
DEALLOCATE CURSORBD|||Hi
You shoudl read http://www.sommarskog.se/dynamic_sql.html on issues
regarding dynamic SQL. For you query use three part naming instead of the US
E
statement
declare cursorbd cursor fast_forward for
SELECT 'SELECT TABLE_NAME FROM ' + QUOTENAME(NAME) +
'.INFORMATION_SCHEMA.TABLES' FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
declare @.sqlstmt nvarchar(4000)
open cursorbd
fetch next from cursorBD into @.sqlstmt
while @.@.fetch_status = 0
begin
exec (@.sqlstmt)
fetch next from cursorBD into @.sqlstmt
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
John
"Enric" wrote:

> I would need go along the current sql server using a cursor (another
> alternatives will be welcomed) and changing of database
> Something like that:
> DECLARE @.BD AS CHAR(20)
> declare cursorbd cursor fast_forward for
> SELECT NAME FROM MASTER.DBO.SYSDATABASES
> WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
> open cursorbd
> fetch next from cursorBD into @.BD
> while @.@.fetch_status = 0
> begin
> USE @.BD
> GO
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> fetch next from cursorBD into @.BD
> END
> CLOSE CURSORBD
> DEALLOCATE CURSORBD
> But it isn't working.
> Regards,|||Since this sounds like an administrative task, and not production code,
then you could use one of Microsoft's undocumented stored procedures to
help: sp_MSforeachdb
EXEC sp_MSforeachdb @.command1="SELECT TABLE_NAME as ? FROM
INFORMATION_SCHEMA.TABLES"
Of course, this will return all databases (including system and test),
and I would definitely NOT use this in production, as it may change in
future releases, yada, yada.
If you want to use a cursor, then you'll need to use dynamic SQL to
build your SQL statement:
DECLARE @.BD AS VARCHAR(20)
DECLARE @.SQL as varchar (200)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
SET @.SQL = 'USE ' + @.BD + '
SELECT TABLE_NAME AS ' + @.BD + '
FROM INFORMATION_SCHEMA.TABLES '
EXEC (@.SQL)
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
Finally, you could use SQL-DMO and a scripting language to do this
task; this option is the most powerful, because it allows you to treat
every SQL entity as an object, and expose their properties through an
evet-drive interface. I use this to script out my development
databases in order to check them into source control.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
VBScript (abbreviated)
SET SQLServer = CreateObject("SQLDMO.SqlServer")
SET Database = CreateObject("SQLDMO.Database")
SET Table = CreateObject("SQLDMO.Table")
SET View = CreateObject("SQLDMO.View")
SET Proc = CreateObject("SQLDMO.StoredProcedure")
SET Func = CreateObject("SQLDMO.UserDefinedFunction")
SET Index = CreateObject("SQLDMO.Index")
SQLServer.LoginSecure = TRUE
SQLServer.Connect ServerName
For each Database in SQLServer.Databases
If Database.SystemObject = False Then
For Each Table In Database.Tables
'write out the tables to a file here
Next
End if
Next
HTH
Stu

Dynamic SQL Statement for Cursor

Hi
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>

Dynamic Sql: Truncate Table: Trap @@ERROR

Greeting All, I have a stored proc that dynamically truncates all the
tables in my databases. I use a cursor and some dynamic sql for this:

.....
create cursor
Loop through sysobjects and get all table names in my database.

...
exec ('truncate table ' + @.TableName)

Now, I want to be able to determine if an error occurred or not nad log
that error to a table in another database.

However, when I try to trap the value of @.@.ERROR after the
exec ('truncate table ' + @.TableName) when an actual error occurs it
fails. My error was synthetically created by placing a foreign key on
the table which precludes the option of truncation:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.

The actual relevant code snippet is:

BEGIN
BEGIN
SET @.v_RowCount = (
SELECT rowcnt
FROM sysindexes
WHERE id = (
SELECT id
FROM sysobjects
WHERE name = @.v_Name
)
AND indid IN (0,1)
)
EXEC('truncate table ' + @.v_Name)
-- If there was an error truncating the current table.
-- Write the event to the MessageLog table.
IF (@.@.ERROR <> 0)
BEGIN
SET @.v_OutputMessage = ('There was an error ' + @.v_name)
INSERT INTO MessageLog (message) values (@.v_outputmessage)
RETURN (-1)
END

Like I was saying, when the error is generated because of the foreign
key the variable @.@.error is never set to 4712, in fact if I were to put
a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
would never be executed. The only thing that would show up in
Enterprise Manager would be the:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.
Any ideas as to what is going on here?

Thanks, TFD."LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1106171149.461401.53660@.f14g2000cwb.googlegro ups.com...
> Greeting All, I have a stored proc that dynamically truncates all the
> tables in my databases. I use a cursor and some dynamic sql for this:
>
> ....
> create cursor
> Loop through sysobjects and get all table names in my database.
> ...
> exec ('truncate table ' + @.TableName)
> Now, I want to be able to determine if an error occurred or not nad log
> that error to a table in another database.
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> The actual relevant code snippet is:
> BEGIN
> BEGIN
> SET @.v_RowCount = (
> SELECT rowcnt
> FROM sysindexes
> WHERE id = (
> SELECT id
> FROM sysobjects
> WHERE name = @.v_Name
> )
> AND indid IN (0,1)
> )
> EXEC('truncate table ' + @.v_Name)
> -- If there was an error truncating the current table.
> -- Write the event to the MessageLog table.
> IF (@.@.ERROR <> 0)
> BEGIN
> SET @.v_OutputMessage = ('There was an error ' + @.v_name)
> INSERT INTO MessageLog (message) values (@.v_outputmessage)
> RETURN (-1)
> END
>
> Like I was saying, when the error is generated because of the foreign
> key the variable @.@.error is never set to 4712, in fact if I were to put
> a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
> would never be executed. The only thing that would show up in
> Enterprise Manager would be the:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> Any ideas as to what is going on here?
> Thanks, TFD.

That particular error terminates the current batch, which is why the rest of
the code doesn't execute - Erland has a detailed article about error
handling in MSSQL, in which he points out that there is unfortunately no
real consistency about which errors will do this:

http://www.sommarskog.se/error-hand...#statementbatch

If your goal is to delete all rows from all tables, then truncation won't
work anyway, so you would have to look at something else. Cascading foreign
keys with DELETE would work, but of course it wouldn't perform as well (and
it wouldn't reset any identity seeds, if that's relevant). Rebuilding the
tables (and constraints and indexes) from scripts or restoring an 'empty'
backup would be other options.

Simon|||LineVoltageHalogen (tropicalfruitdrops@.yahoo.com) writes:
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.

This error can be avoided before-hand by

IF NOT EXISTS (SELECT * FROM sysforeignkeys
WHERE rkeyid = object_name(@.tbl))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and Simon, thank you for your responses. The example I showed
you was created so that I could create an error (testing phase of a big
project) during the truncation operation, it was the only way I knew of
that would guarentee an error. This database actually has no foreign
keys in it, it is a persistent store which feeds a dimensional data
store for an OLAP server. This small snippet I brought forth is just a
tiny piece of a much larger data load. I just wanted to be able to
trap an error during the truncation process if one should arise, I
can't imagine any such thing happening but I just wanted to be able to
log it should it occur.

Regards, TFD.

Dynamic SQL Statement for Cursor

Hi
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>

Dynamic SQL into a cursor

basically im creating a user defined function that i want to take the parameters that are passed to it to create the sql for a cursor to loop through. As far as i can tell a cursor cannot take dynamic sql, anyone got any bright ideas or an easy way round this?

My code so far is as follows (the variable @.sql is the sql statement i want passed in to the cursor):

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

set @.sql = 'select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

declare archivecur cursor
for
@.sql

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
right i thought id found the answer to append the rows from the dynamic sql into a table variable and loop through that but unfortunately im now getting must declare the variable @.curtable

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

declare @.curtable table (Field1 varchar(50), modifdate varchar(8))

set @.sql = 'insert into @.curtable (field1, modifdate) convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + convert(varchar(20),@.inprefno) + ' order by modif_dttm'

execute(@.sql)

declare archivecur cursor
for
select field1, modifdate
from @.curtable
order by modifdate

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
|||

Lamffy,

In order to execute dynamic sql, you need EXEC(...) or sp_executesql and no one of them could be used inside a user defined function.

To play with a cursor and dynamic sql , you can define and open the cursor inside the dynamic sql and return it in an output variable.

Example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.customerid nchar(5)

declare @.c cursor

declare @.orderid int

set @.sql = N'

set @.c = cursor local fast_forward

for

select orderid

from dbo.orders

where customerid = @.customerid;

open @.c'

exec sp_executesql @.sql, N'@.customerid nchar(5), @.c cursor output', 'ALFKI', @.c output

if cursor_status('variable', '@.c') = 1

begin

while 1 = 1

begin

fetch next from @.c into @.orderid

if @.@.error != 0 or @.@.fetch_status != 0 break

print @.orderid

end

close @.c

deallocate @.c

end

what are you trying to accomplish, creating a user defined function that involves dynamic sql and cursors?

AMB

|||

Try

set @.sql = 'declare archivecur cursor for
select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

exec (@.sql)

open archivecur

|||

Maybe it's me, but this seems like extreme overkill.

What function or looping is required to carry out this task?

I'm thinking a SELECT CASE would accomplish what you are looking for.

Adamus

|||Cheers Mark that worked. You're a lifesaver!! :-)
|||I agree Adamus it does look a bit like overkill but when you see the state of NHS data and the data warehouses it's all stored in you'd understand how much of a nightmare doing anything is!

dynamic sql in Cursor

a table named tbl_customerMaster

create table(customerID int, customerName varChar(50), Adderss varChar(100))

---

create procedure sp_saTest ( @.customerID varChar(20) )

AS

declare curCustomer cursor

for SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (@.customerID)

open curCustomer

fatch....

....

xyz, xyz, close cursor

---

the sp created successfully, but when i try to execute that sp i found a error there

Exec sp_saTest (1,2,5,7,10)

Server: Msg 245, Level 16, State 1, Procedure sp_Customer
Syntax error converting the varchar value '1,2,5,7,10' to a column of data type int.

what is the problem there?

suppose when i fire as follow

SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (1,2,5,7,10)

directly in it will be work properly, then why there is error with parameter name?

i m using SQL Server 2000.

The problem is that you're passing in a string, when it wants a list of numbers. I know the string happens to look like a list of numbers, but it's actually not one.

You could do something like:

WHERE ',' + @.customerID + ',' like '%,' + cast(customerID as varchar(20)) + ',%'

But this is kinda nasty. Another alternative is to split the string into a series of numbers. There is code for this at
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

It's down near there end, an example of just this type of behaviour.

Hope this helps...

Rob

Dynamic SQL in Cursor

Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'
Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegro ups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>
|||Thanks, I tried that.
|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegro ups.com...
> Thanks, I tried that.
>
|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help
|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegr oups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>
|||I don't really understand how to use what you sent me for my code.
|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegr oups.com...
>I don't really understand how to use what you sent me for my code.
>
|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov
|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.

Dynamic SQL in Cursor

Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>|||Thanks, I tried that.|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegroups.com...
> Thanks, I tried that.
>|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegroups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>|||I don't really understand how to use what you sent me for my code.|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegroups.com...
>I don't really understand how to use what you sent me for my code.
>|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.|||As already suggested, try accomplish this in a set based manner. It will in most cases produce less
code that is easier to maintain and in vast majority oc cases significantly better preformance.
Anyhow, what you'd need to do is to EXEC the whole DECLARE statement as a string:
DECLARE @.sql nvarchar(2000)
SET @.sql = 'DECLARE c CURSOR FOR SELECT ...'
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<mosheallen@.gmail.com> wrote in message news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>

Dynamic SQL in cursor

I need to pass a list of values into a cursor as such...

DECLARE
@.group_SQL varchar(255)

SET @.group_SQL = 'SELECT group_id FROM groups where group_id in (' + @.group_id + ')'

DECLARE groupContact_import_cursor CURSOR
FOR EXEC(@.group_SQL)
OPEN groupContact_import_cursor
FETCH NEXT FROM groupContact_import_cursor INTO @.group_id
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
insert into groupContacts (group_id, contact_id) values (@.group_id, @.new_cid)
FETCH NEXT FROM groupContact_import_cursor INTO @.group_id
END
CLOSE groupContact_import_cursor
DEALLOCATE groupContact_import_cursor

But MS SQL doesn't seem to like the FOR EXEC(@.group_SQL). Can someone shed some light?

TIAI think it should work like this.

DECLARE groupContact_import_cursor CURSOR
FOR SELECT group_id FROM groups where group_id = @.group_id|||Why? A contact can be assigned to many group_id values, not just one.|||SET @.group_SQL = 'SELECT group_id FROM groups where group_id in (' + @.group_id + ')'

set @.group_SQL = 'DECLARE groupContact_import_cursor CURSOR FOR ' + @.group_SQL

exec (@.group_SQL)
OPEN groupContact_import_cursor
...|||bingo, that did it. I didn't know I needed to include the DECLARE portion, oh well...

Thanks a lot.|||bingo, that did it. I didn't know I needed to include the DECLARE portion, oh well...

Thanks a lot.

It did?

I'm highly sceptical...

What does @.group_id look like

Dynamic SQL in Cursor

Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>|||Thanks, I tried that.|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegroups.com...
> Thanks, I tried that.
>|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegroups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>|||I don't really understand how to use what you sent me for my code.|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegroups.com...
>I don't really understand how to use what you sent me for my code.
>|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.

Dynamic SQL help

I am trying to create a cursor that gathers a tag value and column name
for use in a sp_execute sql statement. The text is as below
SET @.qry = N'SELECT @.tag_value_out = ' +
CONVERT(nvarchar(25),@.column_name) + N' FROM FSFORMULA WHERE formula_id
= @.formula_id_in'
But when I use PRINT on the next line, nothing is printed out.
@.qry is nvarchar(1000)
Any help? This is in a stored procedure.if @.column_name is null it will return null
<cknobs@.gmail.com> wrote in message
news:1143835555.976735.287650@.g10g2000cwb.googlegroups.com...
>I am trying to create a cursor that gathers a tag value and column name
> for use in a sp_execute sql statement. The text is as below
> SET @.qry = N'SELECT @.tag_value_out = ' +
> CONVERT(nvarchar(25),@.column_name) + N' FROM FSFORMULA WHERE formula_id
> = @.formula_id_in'
> But when I use PRINT on the next line, nothing is printed out.
> @.qry is nvarchar(1000)
> Any help? This is in a stored procedure.
>|||it was, turns out i didn't have the @.column_name variable in the fetch
next statement. DERP!
thx!

dynamic sql doesnt work?

I use dynamic sql to write the following PL/SQL block, but it always states that i got error on the part for opening cursor.
Actually I dunno why there's such an error.
Please help
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
c cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================I believe it has to do with this syntax and it will not work:
OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

You may not be able to specify a dynamic SQL build in an OPEN. The OPEN needs to be OPEN rankCursor FOR SELECT DISTINCT(rank) FROM tbl;

Originally posted by wakuku
I use dynamic sql to write the following PL/SQL block, but it always states that i got error on the part for opening cursor.
Actually I dunno why there's such an error.
Please help
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
c cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================|||Originally posted by dmmac
I believe it has to do with this syntax and it will not work:
OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

You may not be able to specify a dynamic SQL build in an OPEN. The OPEN needs to be OPEN rankCursor FOR SELECT DISTINCT(rank) FROM tbl;

YOU sulrely are able to specify dynamic sql in an open, look at following example:

SQL>var vcsr refcursor;
SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):='table_name,initial_extent,next_ext ent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>print :vcsr

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- ----- ----
AEEMAIL 204800 204800
AETABLE 204800 204800
APPLICATION_CONTROL 5242880 1048576
CHAINED_ROWS 1081344 2129920
CHURNSTEP2 204800 204800

5 rows selected.

SQL>

Also, I do not see 'rankCursor' defined as a ref cursor anywhere!!

:cool:|||Sorry to post the wrong code before.

But in my coding, I did define rankCursor before as follows:
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
rankCursor cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
But the same errors still occurred.

ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================

I've tried LKBrwn_DBA's example. But it stated an error, "Bind variable not declared".|||Which version of Oracle are you using?

Did you try this example?:

SQL>var vcsr refcursor;

SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):= 'table_name,initial_extent,next_extent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /|||Originally posted by LKBrwn_DBA
Which version of Oracle are you using?

Did you try this example?:

SQL>var vcsr refcursor;

SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):= 'table_name,initial_extent,next_extent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

I've tried, but failed again...saying
"Bind variable vscr not declared"

The one i'm using is Orcale8 Enterprise Edition Release 8.0.6.3.0|||Originally posted by wakuku
Sorry to post the wrong code before.

But in my coding, I did define rankCursor before as follows:
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
rankCursor cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
But the same errors still occurred.

ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================

I've tried LKBrwn_DBA's example. But it stated an error, "Bind variable not declared".
Your error suggests that you used double quotes (") not single quotes (') in your example:

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for "select * from dept";
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 14:
PLS-00201: identifier 'select * from dept' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for 'select * from dept';
6* end;
SQL> /

PL/SQL procedure successfully completed.|||Originally posted by andrewst
Your error suggests that you used double quotes (") not single quotes (') in your example:

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for "select * from dept";
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 14:
PLS-00201: identifier 'select * from dept' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for 'select * from dept';
6* end;
SQL> /

PL/SQL procedure successfully completed.

I've tried this, but failed again.

It seems that "OPEN sqlstatement FOR cursor" can't be run in the SQL I'm using.

I've used other method, but it works. Here's my coding...
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source_Table.table_name%TYPE;
vSQLstr varchar(9999);
vTotal_Rank Integer;

TYPE cur_typ is REF CURSOR;

Cursor tableCursor IS
SELECT table_name FROM Source_Table;

rankCursor integer;
exeCursor integer;
updateCursor integer;

BEGIN
OPEN tableCursor;

LOOP

FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

rankCursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(rankCursor, 'SELECT rank, new_rank, count(rank) FROM rank_Mapping, '
|| vTable_Name || ' WHERE ltrim(rtrim(rank)) = ltrim(rtrim(old_rank))
GROUP BY rank, new_rank', DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(rankCursor, 1, vMap_Old_Rank, 10);
DBMS_SQL.DEFINE_COLUMN(rankCursor, 2, vCor_New_Rank, 10);
DBMS_SQL.DEFINE_COLUMN(rankCursor, 3, vTotal_Rank);

exeCursor := DBMS_SQL.EXECUTE(rankCursor);

updateCursor := DBMS_SQL.OPEN_CURSOR;

LOOP

IF DBMS_SQL.FETCH_ROWS(rankCursor) > 0 THEN

DBMS_SQL.COLUMN_VALUE(rankCursor, 1, vMap_Old_Rank);
DBMS_SQL.COLUMN_VALUE(rankCursor, 2, vCor_New_Rank);
DBMS_SQL.COLUMN_VALUE(rankCursor, 3, vTotal_Rank);

DBMS_OUTPUT.PUT_LINE(vTable_Name || ' ' || vMap_Old_Rank ||'-'||
vCor_New_Rank || ': ' || vTotal_Rank);

DBMS_SQL.PARSE(updateCursor, 'UPDATE ' || vTable_Name ||
' SET rank = :vCor_New_Rank WHERE rank = :vMap_Old_Rank',
DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(updateCursor, 'vMap_Old_Rank', vMap_Old_Rank);
DBMS_SQL.BIND_VARIABLE(updateCursor, 'vCor_New_Rank', vCor_New_Rank);

/* exeCursor := DBMS_SQL.EXECUTE(updateCursor); */

ELSE
EXIT;
END IF;

END LOOP;
IF DBMS_SQL.IS_OPEN(rankCursor) THEN
DBMS_SQL.CLOSE_CURSOR(rankCursor);
END IF;

IF DBMS_SQL.IS_OPEN(updateCursor) THEN
DBMS_SQL.CLOSE_CURSOR(updateCursor);
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

..............

END;
==============================================|||but it doesnt work if the ref cursor is declared with a return value.
for example, following would work
--------------
CREATE OR REPLACE
PACKAGE rcpackage IS

TYPE Recx IS RECORD
(a VARCHAR2(100));

TYPE MODULESCurType IS REF CURSOR ;

function x return rcPackage.MODULESCurType ;

END;
/

CREATE OR REPLACE
PACKAGE BODY rcpackage IS

function x return rcPackage.MODULESCurType is

t rcPackage.MODULESCurType;
s varchar2(500);

begin

s :='select * from tab';

open T for s;

return t;
end;

END;
/
----------

but not following
-----------
-- Start of DDL Script for Package RECON.RCPACKAGE
-- Generated 2-Oct-2003 22:33:06 from RECON@.CGEN

CREATE OR REPLACE
PACKAGE rcpackage IS

TYPE Recx IS RECORD
(a VARCHAR2(100));

TYPE MODULESCurType IS REF CURSOR return Recx ;

function x return rcPackage.MODULESCurType ;

END;
/

CREATE OR REPLACE
PACKAGE BODY rcpackage IS

function x return rcPackage.MODULESCurType is

t rcPackage.MODULESCurType;
s varchar2(500);

begin

s :='select * from tab';

open T for s;

return t;

end;

END;
/
-----------

Originally posted by LKBrwn_DBA
YOU sulrely are able to specify dynamic sql in an open, look at following example:

SQL>var vcsr refcursor;
SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):='table_name,initial_extent,next_ext ent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>print :vcsr

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- ----- ----
AEEMAIL 204800 204800
AETABLE 204800 204800
APPLICATION_CONTROL 5242880 1048576
CHAINED_ROWS 1081344 2129920
CHURNSTEP2 204800 204800

5 rows selected.

SQL>

Also, I do not see 'rankCursor' defined as a ref cursor anywhere!!

:cool:|||hi actually i really wanna help in this TASK could u please help me out as soon as possible.....i kindly thanxs for that...
----------------

There are a number of subtasks to this weekly task. PL/SQL is not necessary. It will be
sufficient to use SQL. However you will need a firm grasp of the concepts of Week 7
lectures to be able to do the subtasks.
1) Create the following table
StudentTableNotNormalised
StudId Name CourseCode CourseDesc Lecturer Grade Office
S1234 Jack C224 Database Codd D 381
S1234 Jack C225 Algorithms Djikstra P 380
S2345 Jill C224 Database Codd HD 381
S2345 Jill C226 Architecture Ritchie HD 390
S4567 Jack C226 Architecture Ritchie D 390
S4567 Jack C224 Database Codd F 381
S9872 Howard Cpol Politics Marx F 380
2) Design a schema for this table that is in second normal form but not third normal
form.
Demonstrate this schema (you can if you like use views from your original table
to demonstrate the schema and populations: Hint use Distinct, , alternatively
create the tables and populations with a script).
3) Design a schema for this that is in third normal form.
Demonstrate this schema (again you can use views, alternatively create the
tables and populations with a script)
4) Functional dependencies.
Create two SQL queries on the table StudentTableNotNormalised
to ascertain whether Lecturer functionally determines Office and conversely
whether Office functionally determines Lecturerer.
(Hint Use Distinct, Group by and count)