asked on

{"ORA-01036: illegal variable name/number\n"} Urgent.

I am stuck and I don't know where I am going wrong. any help would be greatly appreciated.

{"ORA-01036: illegal variable name/number\n"}

{"Method may only be called on a Type for which Type.IsGenericParameter is true."}

{Name = "Int32" FullName = "System.Int32"}

System.Data.OracleClient.OracleException was caught
Message="ORA-01036: illegal variable name/number\n"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=1036
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)
at Cwb.InitialPriceReporting.Form1.Mode0_SaveInsertPriceListHeader() in C:\SvnWork\InitialPriceReporting\DotNet\InitialPriceReporting\Form1.cs:line 180

try
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
// Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");

Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");

// Create the DbCommand object
DbCommand dbCommand = db.GetStoredProcCommand("insert_price_list_hdr");
dbCommand.CommandTimeout = 180;

db.AddInParameter(dbCommand, "@p_price_list_hdr_id", DbType.String, txtCropYear.Text + txtListNo.Text);
db.AddInParameter(dbCommand, "@p_wht_tough_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_wht_damp_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_wht_stone_disc_amt", DbType.Decimal, 0);

db.AddInParameter(dbCommand, "@p_durum_tough_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_durum_damp_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_durum_stone_disc_amt", DbType.Decimal, 0);

db.AddInParameter(dbCommand, "@p_bly_tough_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_bly_damp_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_bly_stone_disc_amt", DbType.Decimal, 0);

db.AddInParameter(dbCommand, "@p_desigtd_bly_tough_disc_amt", DbType.Decimal, 0);
db.AddInParameter(dbCommand, "@p_desigtd_bly_damp_disc_amt", DbType.Decimal, 0);

db.AddInParameter(dbCommand, "@p_price_list_status_type_name", DbType.String, "0");
db.AddInParameter(dbCommand, "@p_load_dtm", DbType.DateTime, System.DateTime.Now);
db.AddInParameter(dbCommand, "@p_user_name", DbType.String, "Mathieu");

db.AddInParameter(dbCommand, "@p_wht_indr_flag", DbType.String, "0");
db.AddInParameter(dbCommand, "@p_durum_indr_flag", DbType.String, "0");
db.AddInParameter(dbCommand, "@p_bly_indr_flag", DbType.String, "0");
db.AddInParameter(dbCommand, "@p_desigtd_bly_indr_flag", DbType.String, "0");

db.AddInParameter(dbCommand, "@p_load_type_name", DbType.String, "Adjustment");

db.AddInParameter(dbCommand, "@p_wht_basis_num", DbType.Decimal, 1);
db.AddInParameter(dbCommand, "@p_durum_basis_num", DbType.Decimal, 1);
db.AddInParameter(dbCommand, "@p_bly_basis_num", DbType.Decimal, 1);
db.AddInParameter(dbCommand, "@p_desigtd_bly_basis_num", DbType.Decimal, 1);

db.AddInParameter(dbCommand, "@p_pool_part_code", DbType.String, "1");
db.AddInParameter(dbCommand, "@p_sct_price_list_name", DbType.String, "1");
db.AddInParameter(dbCommand, "@p_effctv_dtm", DbType.DateTime, System.DateTime.Now);
db.AddInParameter(dbCommand, "@p_row_cnt_qty", DbType.Decimal, 1);
db.AddInParameter(dbCommand, "@p_price_sum_amt", DbType.Decimal, 0);
db.ExecuteNonQuery(dbCommand);

}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

Stored proc:
 
create or replace PACKAGE BODY PRICE_LIST_REPORTING
AS
    PROCEDURE insert_price_list_hdr (
        p_price_list_hdr_id             IN OUT   price_list_hdr.price_list_hdr_id%TYPE,
        p_wht_tough_disc_amt		IN	 price_list_hdr.wht_tough_disc_amt%TYPE,
        p_wht_damp_disc_amt		IN	 price_list_hdr.wht_damp_disc_amt%TYPE,	
        p_wht_stone_disc_amt		IN	 price_list_hdr.wht_stone_disc_amt%TYPE,
        
        p_durum_tough_disc_amt		IN	 price_list_hdr.durum_tough_disc_amt%TYPE,
        p_durum_damp_disc_amt		IN	 price_list_hdr.durum_damp_disc_amt%TYPE,
        p_durum_stone_disc_amt		IN	 price_list_hdr.durum_stone_disc_amt%TYPE,
        
        p_bly_tough_disc_amt		IN	 price_list_hdr.bly_tough_disc_amt%TYPE,
        p_bly_damp_disc_amt		IN	 price_list_hdr.bly_damp_disc_amt%TYPE,
        p_bly_stone_disc_amt		IN	 price_list_hdr.bly_stone_disc_amt%TYPE,
        
        p_desigtd_bly_tough_disc_amt	IN	 price_list_hdr.desigtd_bly_tough_disc_amt%TYPE,
        p_desigtd_bly_damp_disc_amt	IN	 price_list_hdr.desigtd_bly_damp_disc_amt%TYPE,
      
        p_price_list_status_type_name   IN       price_list_hdr.price_list_status_type_name%TYPE,
        p_load_dtm                      IN       price_list_hdr.load_dtm%TYPE,
        p_user_name                     IN       price_list_hdr.user_name%TYPE,
       
        p_wht_indr_flag                 IN       price_list_hdr.wht_indr_flag%TYPE,
        p_durum_indr_flag               IN       price_list_hdr.durum_indr_flag%TYPE,
        p_bly_indr_flag                 IN       price_list_hdr.bly_indr_flag%TYPE,
        p_desigtd_bly_indr_flag         IN       price_list_hdr.desigtd_bly_indr_flag%TYPE,
       
        p_load_type_name                IN       price_list_hdr.load_type_name%TYPE,
          
       
        p_wht_basis_num                 IN       price_list_hdr.wht_basis_num%TYPE,
        p_durum_basis_num               IN       price_list_hdr.durum_basis_num%TYPE,
        p_bly_basis_num                 IN       price_list_hdr.bly_basis_num%TYPE,
        p_desigtd_bly_basis_num         IN       price_list_hdr.desigtd_bly_basis_num%TYPE,
        
        p_src_file_path_desc            IN       price_list_hdr.src_file_path_desc%TYPE,
        p_pool_part_code                IN       price_list_hdr.pool_part_code%TYPE,
        p_sct_price_list_name           IN       price_list_hdr.sct_price_list_name%TYPE,
        p_sct_effctv_dtm                IN       price_list_hdr.sct_effctv_dtm%TYPE,
        
        p_row_cnt_qty                   IN       price_list_hdr.row_cnt_qty%TYPE,
        p_price_sum_amt                 IN       price_list_hdr.price_sum_amt%TYPE
    )
    IS
    BEGIN
        INSERT INTO price_list_hdr
                    (price_list_hdr_id, 
                     wht_tough_disc_amt,
                     wht_damp_disc_amt,
                     wht_stone_disc_amt,
                     durum_tough_disc_amt,
                     durum_damp_disc_amt,
                     durum_stone_disc_amt,
                     bly_tough_disc_amt,
                     bly_damp_disc_amt,
                     bly_stone_disc_amt,
                     desigtd_bly_tough_disc_amt,
                     desigtd_bly_damp_disc_amt,
                     price_list_status_type_name,
                     load_dtm,
                     user_name, 
                     
                     wht_indr_flag,
                     durum_indr_flag,
                     bly_indr_flag,
                     desigtd_bly_indr_flag,
                     
                     load_type_name,
                     
                     wht_basis_num,     
                     durum_basis_num, 
                     bly_basis_num,
                     desigtd_bly_basis_num,
                     
                     src_file_path_desc,
                     pool_part_code,  
                     sct_price_list_name,
                     sct_effctv_dtm, 
                     
                     row_cnt_qty, 
                     price_sum_amt
                    )
             VALUES ( p_price_list_hdr_id,           
                      p_wht_tough_disc_amt,		
                      p_wht_damp_disc_amt,		
                      p_wht_stone_disc_amt,		
                      
                      p_durum_tough_disc_amt,		
                      p_durum_damp_disc_amt,		
                      p_durum_stone_disc_amt,		
                      
                      p_bly_tough_disc_amt,		
                      p_bly_damp_disc_amt,		
                      p_bly_stone_disc_amt,		
                      
                      p_desigtd_bly_tough_disc_amt,	
                      p_desigtd_bly_damp_disc_amt,	
                      
                      p_price_list_status_type_name, 
                      p_load_dtm,                    
                      p_user_name,                   
                      
                      p_wht_indr_flag,               
                      p_durum_indr_flag,             
                      p_bly_indr_flag,               
                      p_desigtd_bly_indr_flag,       
                      
                      p_load_type_name,              
                        
                      
                      p_wht_basis_num,               
                      p_durum_basis_num,             
                      p_bly_basis_num,               
                      p_desigtd_bly_basis_num,       
                      
                      p_src_file_path_desc,          
                      p_pool_part_code,              
                      p_sct_price_list_name,         			
                      p_sct_effctv_dtm,              
                      
                      p_row_cnt_qty,                 
                      p_price_sum_amt               
);
 END insert_price_list_hdr;

Open in new window

Sean Stuber

remove the "@" from the paramter names

mathieu_cupryk

ASKER

how do i test the stored proc in oracle sql developer

Sean Stuber

create an anonymous block and invoke it.

declare
p1
p2
p3
...

begin
your_procedure(p1,p2,p3...);
end;

mathieu_cupryk

ASKER

In

{
What should I put here.
db.ExecuteNonQuery(dbCommand);????
because we set the ID to input or output?
}

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mathieu_cupryk

ASKER

{System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'INSERT_PRICE_LIST_HDR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)
at Cwb.InitialPriceReporting.Form1.Mode0_SaveInsertPriceListHeader() in C:\SvnWork\InitialPriceReporting\DotNet\InitialPriceReporting\Form1.cs:line 206}

what does this mean?

Sean Stuber

oops.

sorry, put your package name in front of it.

PRICE_LIST_REPORTING.INSERT_PRICE_LIST_HDR.

no just

INSERT_PRICE_LIST_HDR

mathieu_cupryk

ASKER

Sorry this question is answerend I have to post the next.

mathieu_cupryk

ASKER

ORA-06550: line 33 column 21:
PLS-00103: Encouunter the symbol "=" when expecting on of the following:

:=.(@%;
06550.00000 0 "line %s, column %s:\n%s"

Cause: Usually a PL/SQL compilation error.
Action:
Error at line 1

DECLARE
p_price_list_hdr_id price_list_hdr.price_list_hdr_id%TYPE;
p_wht_tough_disc_amt price_list_hdr.wht_tough_disc_amt%TYPE;
p_wht_damp_disc_amt price_list_hdr.wht_damp_disc_amt%TYPE;
p_wht_stone_disc_amt price_list_hdr.wht_stone_disc_amt%TYPE;
p_durum_tough_disc_amt price_list_hdr.durum_tough_disc_amt%TYPE;
p_durum_damp_disc_amt price_list_hdr.durum_damp_disc_amt%TYPE;
p_durum_stone_disc_amt price_list_hdr.durum_stone_disc_amt%TYPE;
p_bly_tough_disc_amt price_list_hdr.bly_tough_disc_amt%TYPE;
p_bly_damp_disc_amt price_list_hdr.bly_damp_disc_amt%TYPE;
p_bly_stone_disc_amt price_list_hdr.bly_stone_disc_amt%TYPE;
p_desigtd_bly_tough_disc_amt price_list_hdr.desigtd_bly_tough_disc_amt%TYPE;
p_desigtd_bly_damp_disc_amt price_list_hdr.desigtd_bly_damp_disc_amt%TYPE;
p_price_list_status_type_name price_list_hdr.price_list_status_type_name%TYPE;
p_load_dtm price_list_hdr.load_dtm%TYPE;
p_user_name price_list_hdr.user_name%TYPE;
p_wht_indr_flag price_list_hdr.wht_indr_flag%TYPE;
p_durum_indr_flag price_list_hdr.durum_indr_flag%TYPE;
p_bly_indr_flag price_list_hdr.bly_indr_flag%TYPE;
p_desigtd_bly_indr_flag price_list_hdr.desigtd_bly_indr_flag%TYPE;
p_load_type_name price_list_hdr.load_type_name%TYPE;
p_wht_basis_num price_list_hdr.wht_basis_num%TYPE;
p_durum_basis_num price_list_hdr.durum_basis_num%TYPE;
p_bly_basis_num price_list_hdr.bly_basis_num%TYPE;
p_desigtd_bly_basis_num price_list_hdr.desigtd_bly_basis_num%TYPE;
p_src_file_path_desc price_list_hdr.src_file_path_desc%TYPE;
p_pool_part_code price_list_hdr.pool_part_code%TYPE;
p_sct_price_list_name price_list_hdr.sct_price_list_name%TYPE;
p_sct_effctv_dtm price_list_hdr.sct_effctv_dtm%TYPE;
p_row_cnt_qty price_list_hdr.row_cnt_qty%TYPE;
p_price_sum_amt price_list_hdr.price_sum_amt%TYPE;
BEGIN
p_price_list_hdr_id = '200801'
p_wht_tough_disc_amt = -8.00
p_wht_damp_disc_amt = -11.50
p_wht_stone_disc_amt = -5.00
p_durum_tough_disc_amt = -7.00
p_durum_damp_disc_amt = -10.50
p_durum_stone_disc_amt = -4.00
p_bly_tough_disc_amt = -6.00
p_bly_damp_disc_amt = -9.50
p_bly_stone_disc_amt = -3.00
p_desigtd_bly_tough_disc_amt = -5.00
p_desigtd_bly_damp_disc_amt = -8.50
p_price_list_status_type_name = 'Initial'
p_load_dtm = '06/11/2008'
p_user_name = 'gxgervais'
p_wht_indr_flag = 'Y'
p_durum_indr_flag = 'Y'
p_bly_indr_flag = 'Y'
p_desigtd_bly_indr_flag = 'Y'
p_load_type_name = 'Initial'
p_wht_basis_num = 0
p_durum_basis_num = 0
p_bly_basis_num = 0
p_desigtd_bly_basis_num = 0
p_src_file_path_desc = 'C:\Backups\Test.xls'
p_pool_part_code = 'A'
p_sct_price_list_name = 'Initial Price List for Aug 2008 (test)'
p_sct_effctv_dtm = null
p_row_cnt_qty = 0
p_price_sum_amt = 0

insert_price_list_hdr (p_price_list_hdr_id,
p_wht_tough_disc_amt,
p_wht_damp_disc_amt,
p_wht_stone_disc_amt,
p_durum_tough_disc_amt,
p_durum_damp_disc_amt,
p_durum_stone_disc_amt,
p_bly_tough_disc_amt,
p_bly_damp_disc_amt,
p_bly_stone_disc_amt,
p_desigtd_bly_tough_disc_amt,
p_desigtd_bly_damp_disc_amt,
p_price_list_status_type_name,
p_load_dtm,
p_user_name,
p_wht_indr_flag,
p_durum_indr_flag,
p_bly_indr_flag,
p_desigtd_bly_indr_flag,
p_load_type_name,
p_wht_basis_num,
p_durum_basis_num,
p_bly_basis_num,
p_desigtd_bly_basis_num,
p_src_file_path_desc,
p_pool_part_code,
p_sct_price_list_name,
p_sct_effctv_dtm,
p_row_cnt_qty,
p_price_sum_amt
);
END;

Sean Stuber

use := to do assignments

= to do comparisons

mathieu_cupryk

ASKER

DECLARE
p_price_list_hdr_id price_list_hdr.price_list_hdr_id%TYPE;
p_wht_tough_disc_amt price_list_hdr.wht_tough_disc_amt%TYPE;
p_wht_damp_disc_amt price_list_hdr.wht_damp_disc_amt%TYPE;
p_wht_stone_disc_amt price_list_hdr.wht_stone_disc_amt%TYPE;
p_durum_tough_disc_amt price_list_hdr.durum_tough_disc_amt%TYPE;
p_durum_damp_disc_amt price_list_hdr.durum_damp_disc_amt%TYPE;
p_durum_stone_disc_amt price_list_hdr.durum_stone_disc_amt%TYPE;
p_bly_tough_disc_amt price_list_hdr.bly_tough_disc_amt%TYPE;
p_bly_damp_disc_amt price_list_hdr.bly_damp_disc_amt%TYPE;
p_bly_stone_disc_amt price_list_hdr.bly_stone_disc_amt%TYPE;
p_desigtd_bly_tough_disc_amt price_list_hdr.desigtd_bly_tough_disc_amt%TYPE;
p_desigtd_bly_damp_disc_amt price_list_hdr.desigtd_bly_damp_disc_amt%TYPE;
p_price_list_status_type_name price_list_hdr.price_list_status_type_name%TYPE;
p_load_dtm price_list_hdr.load_dtm%TYPE;
p_user_name price_list_hdr.user_name%TYPE;
p_wht_indr_flag price_list_hdr.wht_indr_flag%TYPE;
p_durum_indr_flag price_list_hdr.durum_indr_flag%TYPE;
p_bly_indr_flag price_list_hdr.bly_indr_flag%TYPE;
p_desigtd_bly_indr_flag price_list_hdr.desigtd_bly_indr_flag%TYPE;
p_load_type_name price_list_hdr.load_type_name%TYPE;
p_wht_basis_num price_list_hdr.wht_basis_num%TYPE;
p_durum_basis_num price_list_hdr.durum_basis_num%TYPE;
p_bly_basis_num price_list_hdr.bly_basis_num%TYPE;
p_desigtd_bly_basis_num price_list_hdr.desigtd_bly_basis_num%TYPE;
p_src_file_path_desc price_list_hdr.src_file_path_desc%TYPE;
p_pool_part_code price_list_hdr.pool_part_code%TYPE;
p_sct_price_list_name price_list_hdr.sct_price_list_name%TYPE;
p_sct_effctv_dtm price_list_hdr.sct_effctv_dtm%TYPE;
p_row_cnt_qty price_list_hdr.row_cnt_qty%TYPE;
p_price_sum_amt price_list_hdr.price_sum_amt%TYPE;
BEGIN
p_price_list_hdr_id := '200801';
p_wht_tough_disc_amt := 8.00;
p_wht_damp_disc_amt := -11.50;
p_wht_stone_disc_amt := -5.00;
p_durum_tough_disc_amt := -7.00;
p_durum_damp_disc_amt := -10.50;
p_durum_stone_disc_amt := -4.00;
p_bly_tough_disc_amt := -6.00;
p_bly_damp_disc_amt := -9.50;
p_bly_stone_disc_amt := -3.00;
p_desigtd_bly_tough_disc_amt := -5.00;
p_desigtd_bly_damp_disc_amt := -8.50;
p_price_list_status_type_name := 'Initial';
p_load_dtm := '06/11/2008';
p_user_name := 'gxgervais';
p_wht_indr_flag := 'Y';
p_durum_indr_flag := 'Y';
p_bly_indr_flag := 'Y';
p_desigtd_bly_indr_flag := 'Y';
p_load_type_name := 'Initial';
p_wht_basis_num := 0;
p_durum_basis_num := 0;
p_bly_basis_num := 0;
p_desigtd_bly_basis_num := 0;
p_src_file_path_desc := 'C:\Backups\Test.xls';
p_pool_part_code := 'A';
p_sct_price_list_name := 'Initial Price List for Aug 2008 (test)';
p_sct_effctv_dtm := null;
p_row_cnt_qty := 0;
p_price_sum_amt := 0;

insert_price_list_hdr (p_price_list_hdr_id,
p_wht_tough_disc_amt,
p_wht_damp_disc_amt,
p_wht_stone_disc_amt,
p_durum_tough_disc_amt,
p_durum_damp_disc_amt,
p_durum_stone_disc_amt,
p_bly_tough_disc_amt,
p_bly_damp_disc_amt,
p_bly_stone_disc_amt,
p_desigtd_bly_tough_disc_amt,
p_desigtd_bly_damp_disc_amt,
p_price_list_status_type_name,
p_load_dtm,
p_user_name,
p_wht_indr_flag,
p_durum_indr_flag,
p_bly_indr_flag,
p_desigtd_bly_indr_flag,
p_load_type_name,
p_wht_basis_num,
p_durum_basis_num,
p_bly_basis_num,
p_desigtd_bly_basis_num,
p_src_file_path_desc,
p_pool_part_code,
p_sct_price_list_name,
p_sct_effctv_dtm,
p_row_cnt_qty,
p_price_sum_amt
);
END;

screen3.jpg

{&quot;ORA-01036: illegal variable name/number\n&quot;} Urgent.

{"ORA-01036: illegal variable name/number\n"} Urgent.