Home » SQL & PL/SQL » SQL & PL/SQL » T -SQL to PL/SQL conversion
T -SQL to PL/SQL conversion [message #247461] Tue, 26 June 2007 00:08 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi ALL,

Can anybody help me!
below code is for SQL SERVER can you tell me what is replacement for tha same in oracle PL - SQL.

v_sqlString := 'set @updated_decimal = CAST(''' || v_updated_char || ''' as DECIMAL(20, ' || v_scale || '))';
                        sp_executesql(v_sqlString,
                                      N'@updated_decimal decimal(20,2) output',
                                      v_updated_decimal);




Thanks & Regards

Rajesh
Re: T -SQL to PL/SQL conversion [message #247478 is a reply to message #247461] Tue, 26 June 2007 01:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I don't know T_SQL, but if you told me what it has to do, I'm sure we get a solution for this.

MHE
Re: T -SQL to PL/SQL conversion [message #247505 is a reply to message #247461] Tue, 26 June 2007 02:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You appear to be mixing languages here. I'm pretty sure (but apologise if I'm wrong) That || is not a valid concatenation 'character' in T-SQL
It looks like you are simply building a dynamic string, then running it. Is sp_executesql a user defined procedure or is it a supplied proc? If it is simply intended to execute the dynamic string that you have created then have a look at what I assume would be the Oracle equivalent(s) of either EXECUTE IMMEDIATE or DBMS_SQL. You can get the documentation on them at
http://tahiti.oracle.com
Re: T -SQL to PL/SQL conversion [message #247514 is a reply to message #247505] Tue, 26 June 2007 03:08 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
yeah you are right................
actual thing is as below for SQL SERVER
I am looking code for oracle's for tha same.
SET @sqlString = 'set @updated_decimal = CAST(''' + @updated_char + ''' as DECIMAL(20, ' + @scale + '))'
						EXEC sp_executesql @sqlString, N'@updated_decimal decimal(20,2) output', @updated_decimal output
						


Sorry for first post........

Re: T -SQL to PL/SQL conversion [message #247546 is a reply to message #247514] Tue, 26 June 2007 06:04 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
May be some thing like this

declare
updt_char varchar2(10) := '1.78';
updated_decimal number;
scale char(1) := '4';
sqlstring long;
begin
sqlstring := 'Begin Select cast(''' ||updt_char ||''' as number(10, '||scale ||')) into :x from dual; end;';
dbms_output.put_line(sqlstring);
execute immediate sqlstring using out updated_decimal;
dbms_output.put_line('Updated Value: '|| updated_decimal);
end;
/

Begin Select cast('1.78' as number(10, 4)) into :x from dual; end;
Updated Value: 1.78

PL/SQL procedure successfully completed.




Re: T -SQL to PL/SQL conversion [message #247560 is a reply to message #247546] Tue, 26 June 2007 06:48 Go to previous message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Thanx bonker this is helpful.
thanx again.
Previous Topic: knowing the referenced tables
Next Topic: How to import excel data to oracle table?
Goto Forum:
  


Current Time: Sun Dec 04 16:40:24 CST 2016

Total time taken to generate the page: 0.06246 seconds