Error

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 4, 2010 at 7:37 am

  #96803

  I m trying to develop function :

  CREATE FUNCTION NEW_GET_CARD_ACCOUNT(

  p_Accounts_list varchar(20)

  p_sequence int

  p_account_number varchar

  p_account_priority varchar

  p_account_Type varchar

  p_account_currency varchar

  p_account_status varchar

  p_account_status_date varchar

  p_account_listing varchar

  )

  RETURNS int AS

  DECLARE

  v_nbr_account int ,

  v_accounts_info_table int ,

  v_offset int ,

  Response int ,

  v_spy_counter int ,

  BEGIN

  v_nbr_account = (p_Accounts_list)/54

  FOR v_account_id ins 1. .. v_nbr_account

  LOOP

  v_accounts_info_table(v_account_id).account_sequence = v_account_id;

  v_offset = v_offset + 2.;

  v_accounts_info_table(v_account_id).account_number = SUBSTR(p_Accounts_list,v_offset,24.);

  v_offset = v_offset + 24.;

  v_accounts_info_table(v_account_id).account_priority = SUBSTR(p_Accounts_list,v_offset,2.);

  v_offset = v_offset + 2.;

  v_accounts_info_table(v_account_id).account_Type = SUBSTR(p_Accounts_list,v_offset,2.);

  v_offset = v_offset + 2.;

  v_accounts_info_table(v_account_id).account_currency = SUBSTR(p_Accounts_list,v_offset,3.);

  v_offset = v_offset + 3.;

  v_accounts_info_table(v_account_id).account_status = SUBSTR(p_Accounts_list,v_offset,1.);

  v_offset := v_offset + 1.;

  v_accounts_info_table(v_account_id).account_status_date := SUBSTR(p_Accounts_list,v_offset,19.);

  v_offset = v_offset + 19.;

  v_accounts_info_table(v_account_id).account_listing := SUBSTR(p_Accounts_list,v_offset,1.);

  v_offset = v_offset + 1.;

  END LOOP;

  p_account_number = v_accounts_info_table(p_sequence).account_number;

  p_account_priority = v_accounts_info_table(p_sequence).account_priority;

  p_account_Type = v_accounts_info_table(p_sequence).account_type;

  p_account_currency = v_accounts_info_table(p_sequence).account_currency;

  p_account_status = v_accounts_info_table(p_sequence).account_status;

  p_account_status_date = v_accounts_info_table(p_sequence).account_status_date;

  p_account_listing = v_accounts_info_table(p_sequence).account_listing;

  RETURN 0

  EXCEPTION WHEN OTHERS

  THEN

  DECLARE

  p_application_traces_type APPLICATION_TRACES%ROWTYPE;

  BEGIN

  p_application_traces_type.line_number = 400

  p_application_traces_type.system_error = 'Oracle Error:' ||SUBSTR(SQLERRM,1.,500.);

  p_application_traces_type.module_code = 'CRD';

  p_application_traces_type.package_name = 'EPS_CARD_CAPTURE_TOOLS';

  p_application_traces_type.function_name = 'NEW_GET_CARD_ACCOUNT';

  p_application_traces_type.trace_level = 0.;

  EPS_GENERAL_TOOLS.TRACES_MANGEMENT (p_application_traces_type);

  END;

  RETURN -2

  END NEW_GET_CARD_ACCOUNT;

  And i have this errors :

  Msg 102, Level 15, State 1, Procedure NEW_GET_CARD_ACCOUNT, Line 2

  Incorrect syntax near 'p_Accounts_list'.

  Msg 155, Level 15, State 2, Procedure NEW_GET_CARD_ACCOUNT, Line 14

  'int' is not a recognized CURSOR option.

  Msg 102, Level 15, State 1, Procedure NEW_GET_CARD_ACCOUNT, Line 20

  Incorrect syntax near 'v_nbr_account'.

  Msg 155, Level 15, State 2, Procedure NEW_GET_CARD_ACCOUNT, Line 53

  'APPLICATION_TRACES' is not a recognized CURSOR option.

  Msg 102, Level 15, State 1, Procedure NEW_GET_CARD_ACCOUNT, Line 55

  Incorrect syntax near 'p_application_traces_type'.

  Msg 178, Level 15, State 1, Procedure NEW_GET_CARD_ACCOUNT, Line 63

  A RETURN statement with a return value cannot be used in this context.

  thanks for ur help

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 4, 2010 at 7:42 am

  #1161798

  the function you pasted is for Oracle, and contains a lot of stuff that is Oracle-only syntax.

  it cannot be directly compiled into SQL Server, you'll have to re-write it.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 4, 2010 at 7:53 am

  #1161808

  that's what i m trying to do

  if u have any idea

  u welcome

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 4, 2010 at 8:00 am

  #1161816

  You're going to have to rewrite it from scratch. SQL doesn't have For loops for starters, while it does have while loops, loops are not a good way to work in SQL, it's not optimised for row-by-row processing

  I can't even begin to tell what you're trying to do there. If you want someone to help you rewrite it, you're going to have to give us a lot more. Starting with table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 4, 2010 at 8:04 am

  #1161821

  oki I agree trying to move the function from oracle to sqlserver

  if ever there has the help and ideas not hesitate

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 4, 2010 at 10:47 am

  #1161946

  this what is come after modification :

  CREATE FUNCTION NEW_GET_CARD_ACCOUNT(

  @p_Accounts_list varchar(20),

  @p_sequence int ,

  @p_account_number varchar(20),

  @p_account_priority varchar(20),

  @p_account_Type varchar(20),

  @p_account_currency varchar(20),

  @p_account_status varchar(20),

  @p_account_status_date varchar(20),

  @p_account_listing varchar(20)

  )

  RETURNS int AS

  BEGIN

  DECLARE

  @v_nbr_account int,

  @v_accounts_info_table int,

  @v_offset int,

  @Response int,

  @v_spy_counter int,

  @v_account_id int;

  SET @v_nbr_account = LEN (@p_Accounts_list)/54;

  WHILE @v_nbr_account < @v_account_id

  SET @v_account_id = @v_accounts_info_table(@v_account_id).account_sequence

  @v_offset = @v_offset + 2 ;

  @v_accounts_info_table(v_account_id).account_number = SUBSTRING(@p_Accounts_list,@v_offset,24.);

  @v_offset = @v_offset + 24.;

  @v_accounts_info_table(v_account_id).account_priority = SUBSTRING(@p_Accounts_list,@v_offset,2.);

  @v_offset = v_offset + 2.;

  @v_accounts_info_table(v_account_id).account_Type = SUBSTRING(@p_Accounts_list,@v_offset,2.);

  @v_offset = v_offset + 2.;

  @v_accounts_info_table(v_account_id).account_currency = SUBSTRING(@p_Accounts_list,@v_offset,3.);

  @v_offset = v_offset + 3.;

  @v_accounts_info_table(v_account_id).account_status = SUBSTRING(@p_Accounts_list,@v_offset,1.);

  @v_offset := v_offset + 1.;

  @v_accounts_info_table(v_account_id).account_status_date = SUBSTRING(p_Accounts_list,@v_offset,19.);

  @v_offset = v_offset + 19.;

  @v_accounts_info_table(v_account_id).account_listing = SUBSTRING(@p_Accounts_list,@v_offset,1.);

  @v_offset = v_offset + 1.;

  @p_account_number = @v_accounts_info_table(p_sequence).@account_number;

  @p_account_priority = @v_accounts_info_table(p_sequence).@account_priority;

  @p_account_Type = @v_accounts_info_table(p_sequence).@account_type;

  @p_account_currency = @v_accounts_info_table(p_sequence).@account_currency;

  @p_account_status = @v_accounts_info_table(p_sequence).@account_status;

  @p_account_status_date = @v_accounts_info_table(p_sequence).@account_status_date;

  @p_account_listing = @v_accounts_info_table(p_sequence).@account_listing;

  RETURN 0

  EXCEPTION WHEN OTHERS

  THEN

  PRINT 'Mon ami arrete toi et gere l exeption ';

  END;

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 4, 2010 at 2:17 pm

  #1162096

  Firstly, that syntax is still incorrect.

  In SQL, a SET cannot reference a table, unless it has is a subquery complete with SELECT and FROM

  EXCEPTION WHEN OTHERS is not SQL syntax. I suspect you're looking for TRY... CATCH

  That's not the important point though. SQL Server is not optimised to loops and row by row processing. I can near-guarantee that if you do get this working with a WHILE loop in it, it'll be dog slow. You should try and do whatever that is in the loop as a set-based statement instead

  If you want our help in that, you need to provide what I asked for earlier. Table definitions, sample data and expected output. Without those, there's very little that we're going to be able to do for you.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 5, 2010 at 5:09 am

  #1162339

  Finally i had this function so plz if u could correct :

  CREATE FUNCTION NEW_GET_CARD_ACCOUNT(

  @p_Accounts_list varchar(20),

  @p_sequence int ,

  @p_account_number varchar(20),

  @p_account_priority varchar(20),

  @p_account_Type varchar(20),

  @p_account_currency varchar(20),

  @p_account_status varchar(20),

  @p_account_status_date varchar(20),

  @p_account_listing varchar(20)

  )

  RETURNS int AS

  BEGIN

  DECLARE

  @v_nbr_account int,

  @v_accounts_info_table int,

  @v_offset int,

  @Response int,

  @v_spy_counter int,

  @v_account_id int;

  SELECT @v_nbr_account as v_nbr_account

  SET @v_nbr_account = LEN (@p_Accounts_list)/54;

  SELECT @v_account_id as v_account_id

  SET @v_account_id = 1;

  WHILE @v_account_id < @v_nbr_account

  BEGIN TRY

  @v_accounts_info_table(v_account_id).account_sequence = v_account_id

  @v_offset = @v_offset + 2 ;

  @v_accounts_info_table(v_account_id).account_number = SUBSTRING(@p_Accounts_list,@v_offset,24.);

  @v_offset = @v_offset + 24.;

  @v_accounts_info_table(v_account_id).account_priority = SUBSTRING(@p_Accounts_list,@v_offset,2.);

  @v_offset = v_offset + 2.;

  @v_accounts_info_table(v_account_id).account_Type = SUBSTRING(@p_Accounts_list,@v_offset,2.);

  @v_offset = v_offset + 2.;

  @v_accounts_info_table(v_account_id).account_currency = SUBSTRING(@p_Accounts_list,@v_offset,3.);

  @v_offset = v_offset + 3.;

  @v_accounts_info_table(v_account_id).account_status = SUBSTRING(@p_Accounts_list,@v_offset,1.);

  @v_offset := v_offset + 1.;

  @v_accounts_info_table(v_account_id).account_status_date = SUBSTRING(p_Accounts_list,@v_offset,19.);

  @v_offset = v_offset + 19.;

  @v_accounts_info_table(v_account_id).account_listing = SUBSTRING(@p_Accounts_list,@v_offset,1.);

  @v_offset = v_offset + 1.;

  @v_account_id = @v_account_id + 1;

  @p_account_number = @v_accounts_info_table(p_sequence).@account_number;

  @p_account_priority = @v_accounts_info_table(p_sequence).@account_priority;

  @p_account_Type = @v_accounts_info_table(p_sequence).@account_type;

  @p_account_currency = @v_accounts_info_table(p_sequence).@account_currency;

  @p_account_status = @v_accounts_info_table(p_sequence).@account_status;

  @p_account_status_date = @v_accounts_info_table(p_sequence).@account_status_date;

  @p_account_listing = @v_accounts_info_table(p_sequence).@account_listing;

  END TRY

  RETURN 0

  BEGIN CATCH

  PRINT ERROR_MESSAGE();

  END CATCH

  END

  thanks 😉

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 5, 2010 at 5:55 am

  #1162362

  apres avoir consulté de certain cour j'ai pu arrivé a realiser cette fonction sa me genere une erreur (Msg 443, Level 16, State 15, Procedure NEW_GET_CARD_ACCOUNT, Line 59

  Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.)

  CREATE FUNCTION NEW_GET_CARD_ACCOUNT

  (

  @p_card_accounts VARCHAR (4000),

  @p_sequence INTEGER ,

  @p_account_number VARCHAR(4000) ,

  @p_account_priority VARCHAR(4000) ,

  @p_account_Type VARCHAR(4000) ,

  @p_account_currency VARCHAR(4000) ,

  @p_account_status VARCHAR(4000) ,

  @p_account_status_date VARCHAR(4000) ,

  @p_account_listing VARCHAR(4000)

  )

  RETURNS INTEGER

  AS

  BEGIN

  DECLARE @adv_rowcount INT

  DECLARE @adv_error INT

  DECLARE @v_nbr_account INT

  DECLARE @v_accounts_info_table VARCHAR(4000)

  DECLARE @v_offset INT

  SET @v_offset = 1

  DECLARE @Response INT

  DECLARE @v_spy_counter INT

  SET @v_spy_counter = 0

  SELECT @v_nbr_account = LEN(@p_card_accounts)/ 54

  DECLARE @v_account_id INT

  SELECT @v_account_id = 1

  WHILE @v_account_id <= @v_nbr_account

  BEGIN

  INSERT INTO v_accounts_info_table( tempIdx , account_sequence ) VALUES (@v_account_id, @v_account_id)

  SELECT @v_offset = @v_offset + 2

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_number = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_number ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 24))

  SELECT @v_offset = @v_offset + 24

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_priority = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_priority ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 2))

  SELECT @v_offset = @v_offset + 2

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_Type = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_Type ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 2))

  SELECT @v_offset = @v_offset + 2

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_currency = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_currency ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 3))

  SELECT @v_offset = @v_offset + 3

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_status = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_status ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 1))

  SELECT @v_offset = @v_offset + 1

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_status_date = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_status_date ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 19))

  SELECT @v_offset = @v_offset + 19

  IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)

  UPDATE v_accounts_info_table SET account_listing = @p_card_accounts WHERE tempIdx = @v_account_id

  ELSE

  INSERT INTO v_accounts_info_table( tempIdx , account_listing ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 1))

  SELECT @v_offset = @v_offset + 1

  SELECT @v_account_id = @v_account_id + 1

  END

  SELECT @p_account_number = DBO.v_accounts_info_table_account_number(@p_sequence)

  SELECT @p_account_priority = DBO.v_accounts_info_table_account_priority(@p_sequence)

  SELECT @p_account_Type = DBO.v_accounts_info_table_account_type(@p_sequence)

  SELECT @p_account_currency = DBO.v_accounts_info_table_account_currency(@p_sequence)

  SELECT @p_account_status = DBO.v_accounts_info_table_account_status(@p_sequence)

  SELECT @p_account_status_date = DBO.v_accounts_info_table_account_status_date(@p_sequence)

  SELECT @p_account_listing = DBO.v_accounts_info_table_account_listing(@p_sequence)

  RETURN (0 )

  GOTO ExitLabel1

  Exception1:

  BEGIN

  RETURN (-2 )

  END

  ExitLabel1:

  RETURN (0 )

  END

  GO

  plz i standing for ur help and thanks for all

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 5, 2010 at 6:06 am

  #1162372

  UPDATE (and insert and delete) are not allowed within a SQL function. A function cannot make changes to the database.

  I assume you don't want us to help you rewrite it?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 5, 2010 at 7:15 am

  #1162412

  Sure i want your help

  what i have to do ??

  i give the function & the table

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 5, 2010 at 7:20 am

  #1162413

  sur i want ur help

  so what i have to do to have it

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 5, 2010 at 7:27 am

  #1162421

  I've looked at this at least half a dozen times, and still cannot see what it is doing, because i never see the table definition it is fiddling with.

  this oracle function does stuff and returns 0 or -2, i assume representing success or failure.

  show us the CREATE TABLE v_accounts_info_table ....definition.

  you'll need to make this a procedure with a return parameter if it is going to insert/update/delete to that table, as laready explained.

  we are at what 20 posts, and you still failed to define any of the things we need:

  1. table definitions.

  2. explaination of what the function is supposed to do.(why does it loop thru the v_accounts_info_table table?)

  3.expected results.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 5, 2010 at 7:35 am

  #1162426

  this is the implementation on oracle :

  FUNCTION NEW_GET_CARD_ACCOUNT( p_card_accounts IN CARD_DATA.card_accounts%TYPE,

  p_sequence IN PLS_INTEGER,

  p_account_number OUT NOCOPY VARCHAR2,

  p_account_priority OUT NOCOPY VARCHAR2,

  p_account_Type OUT NOCOPY VARCHAR2,

  p_account_currency OUT NOCOPY VARCHAR2,

  p_account_status OUT NOCOPY VARCHAR2,

  p_account_status_date OUT NOCOPY VARCHAR2,

  p_account_listing OUT NOCOPY VARCHAR2

  )

  RETURN PLS_INTEGER IS

  v_nbr_account PLS_INTEGER;

  v_accounts_info_table p_accounts_table_type;

  v_offset PLS_INTEGER := 1.;

  Response PLS_INTEGER;

  v_spy_counter PLS_INTEGER := 0;

  BEGIN

  v_nbr_account := LENGTH(p_card_accounts)/54.;

  FOR v_account_id IN 1. .. v_nbr_account

  LOOP

  v_accounts_info_table(v_account_id).account_sequence := v_account_id;

  v_offset := v_offset + 2.;

  v_accounts_info_table(v_account_id).account_number := SUBSTR(p_card_accounts,v_offset,24.);

  v_offset := v_offset + 24.;

  v_accounts_info_table(v_account_id).account_priority := SUBSTR(p_card_accounts,v_offset,2.);

  v_offset := v_offset + 2.;

  v_accounts_info_table(v_account_id).account_Type := SUBSTR(p_card_accounts,v_offset,2.);

  v_offset := v_offset + 2.;

  v_accounts_info_table(v_account_id).account_currency := SUBSTR(p_card_accounts,v_offset,3.);

  v_offset := v_offset + 3.;

  v_accounts_info_table(v_account_id).account_status := SUBSTR(p_card_accounts,v_offset,1.);

  v_offset := v_offset + 1.;

  v_accounts_info_table(v_account_id).account_status_date := SUBSTR(p_card_accounts,v_offset,19.);

  v_offset := v_offset + 19.;

  v_accounts_info_table(v_account_id).account_listing := SUBSTR(p_card_accounts,v_offset,1.);

  v_offset := v_offset + 1.;

  END LOOP;

  p_account_number := v_accounts_info_table(p_sequence).account_number;

  p_account_priority := v_accounts_info_table(p_sequence).account_priority;

  p_account_Type := v_accounts_info_table(p_sequence).account_type;

  p_account_currency := v_accounts_info_table(p_sequence).account_currency;

  p_account_status := v_accounts_info_table(p_sequence).account_status;

  p_account_status_date := v_accounts_info_table(p_sequence).account_status_date;

  p_account_listing := v_accounts_info_table(p_sequence).account_listing;

  RETURN(0.);

  EXCEPTION WHEN OTHERS

  THEN

  DECLARE

  p_application_traces_type APPLICATION_TRACES%ROWTYPE;

  BEGIN

  p_application_traces_type.line_number := 400.;

  p_application_traces_type.system_error := 'Oracle Error:' ||SUBSTR(SQLERRM,1.,500.);

  p_application_traces_type.module_code := 'CRD';

  p_application_traces_type.package_name := 'EPS_CARD_CAPTURE_TOOLS';

  p_application_traces_type.function_name := 'NEW_GET_CARD_ACCOUNT';

  p_application_traces_type.trace_level := 0.;

  EPS_GENERAL_TOOLS.TRACES_MANGEMENT (p_application_traces_type);

  END;

  RETURN(-2.);

  END NEW_GET_CARD_ACCOUNT;

  i m trying to develop the same function with SqlServer

  function must complete a sequence of account list.

  and this the implementation of the package on oracle

  PACKAGE EPS_CARD_CAPTURE_TOOLS

  IS

  TYPE p_account_info IS RECORD ( account_sequence PLS_INTEGER,

  account_number VARCHAR2(24),

  account_priority VARCHAR2(2),

  account_Type VARCHAR2(2),

  account_currency VARCHAR2(3),

  account_status VARCHAR2(1),

  account_status_date VARCHAR2(19),

  account_listing VARCHAR2(1)

  );

  TYPE p_accounts_table_type IS TABLE OF p_account_info INDEX BY BINARY_INTEGER;

  i know that on sqlserver there no defenition of package

  so i try to create a table who will manage v_accounts_info_tbale

• b.noof

  SSC Enthusiast

  Points: 180

  More actions

  May 5, 2010 at 7:39 am

  #1162430

  the loop is made to fill the sequence each time it reaches the threshold