ORA-06550: line 1, column 7: PLS-00306 ERROR!! RSS

• • Reply
  

  rapatel83

  Member

  7 Points

  4 Posts

  ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 07:11 AM|LINK

  Hello to all members out there.

  I am very new to .net ado 

  i am using visual studio to connect to oracle data base and insert records into a table.

  my package and store procedure are as under 

  CREATE OR REPLACE PACKAGE pkg_supplier
  as
     PROCEDURE sp_insert_hpsuppliers(a_companyname IN VARCHAR2, a_contactname IN VARCHAR2, a_address IN VARCHAR2, a_city IN VARCHAR2);
  end;
  /
  
  create or replace package body pkg_supplier
  as
  procedure sp_insert_hpsuppliers
  (a_companyname in varchar2,a_contactname in varchar2,a_address in varchar2,a_city in varchar2)
  as 
  begin
  
  insert into hp_suppliers values (a_companyname,a_contactname,a_address,a_city);
  end sp_insert_hpsuppliers ;
  end pkg_supplier;
  /

  and here is the code which i use to call store procedure

    ' Declare variables
          Dim li_rc As Integer
          Dim myString As String
          Dim myConn As New OleDb.OleDbConnection()
  
          ' Create new connection
          myString = "Provider=msdaora;Data Source=orcl;User Id=id;Password=pass;"
          Try
              myConn.ConnectionString = myString
              myConn.Open()
          Catch ex As OleDb.OleDbException
              MessageBox.Show(ex.Message)
              Exit Sub
          End Try
  
          ' CALL ACI SP
          Dim companyname As String
          Dim contactname As String
          Dim address As String
          Dim city As String
  
          companyname = TextBox1.Text
          contactname = TextBox2.Text
          address = TextBox3.Text
          city = TextBox4.Text
  
          Dim myCommand As New OleDb.OleDbCommand("pkg_supplier.sp_insert_hpsuppliers", myConn)
          myCommand.CommandType = CommandType.StoredProcedure
  
  
  
          Dim prmcompanyname As New OleDb.OleDbParameter()
          prmcompanyname.Direction = ParameterDirection.Input
          prmcompanyname.ParameterName = "a_companyname"
          prmcompanyname.Size = 50
          prmcompanyname.DbType = DbType.AnsiString
          myCommand.Parameters.Add(prmcompanyname)
  
  
          Dim prmcontactname As New OleDb.OleDbParameter()
          prmcontactname.Direction = ParameterDirection.Input
          prmcontactname.ParameterName = "a_contactname"
          prmcontactname.Size = 50
          prmcontactname.DbType = DbType.AnsiString
          myCommand.Parameters.Add(prmcontactname)
  
  
  
          Dim prmaddress As New OleDb.OleDbParameter()
          prmaddress.Direction = ParameterDirection.Input
          prmaddress.ParameterName = "a_address"
          prmaddress.Size = 50
          prmaddress.DbType = DbType.AnsiString
          myCommand.Parameters.Add(prmaddress)
  
  
          Dim prmcity As New OleDb.OleDbParameter()
          prmcity.Direction = ParameterDirection.Input
          prmcity.ParameterName = "a_city"
          prmcity.Size = 50
          prmcity.DbType = DbType.AnsiString
          myCommand.Parameters.Add(prmcity)
  
          Try
              li_rc = myCommand.ExecuteNonQuery()
              MessageBox.Show(li_rc & " Rows have been affected")
          Catch ex As Exception
              MessageBox.Show(ex.Message)
          End Try
  
          
      End Sub
  End Class

  i tried , checked many times but i am still getting an error message of 

  ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  each time i run.

  please help me with it and let me know where i am going worng.

  Thanks

• • Reply
  

  kedarrkulkar...

  All-Star

  34013 Points

  5468 Posts

  Re: ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 07:43 AM|LINK

  shouldnt you end the package as

  CREATE OR REPLACE PACKAGE pkg_supplier
  as
     PROCEDURE sp_insert_hpsuppliers(a_companyname IN VARCHAR2, a_contactname IN VARCHAR2, a_address IN VARCHAR2, a_city IN VARCHAR2);
  end pkg_supplier;
  /
  
  check this for more details

  http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/09_packs.htm

  hope this helps...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
  • Edited by kedarrkulkarni on Dec 07, 2011 07:43 AM
  • Edited by kedarrkulkarni on Dec 07, 2011 07:43 AM
• • Reply
  

  rapatel83

  Member

  7 Points

  4 Posts

  Re: ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 08:13 AM|LINK

  even after changing the package error is still there ........

  when i try to run the code it give me an error mesage 

• • Reply
  

  kedarrkulkar...

  All-Star

  34013 Points

  5468 Posts

  Re: ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 09:33 AM|LINK

  have u compiled the package and prcoedure correctly (without error)?

  tr calling the procedure from sql plus (or any other tool) like this

  BEGIN
      sp_insert_hpsuppliers('company name', 'contact', 'address', 'city');
  END; 

  u should replace the values with actual values u r entering in textboxes... does it give any error?

  hope this helps...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
• • Reply
  

  rapatel83

  Member

  7 Points

  4 Posts

  Re: ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 06:00 PM|LINK

  package and procedure are correctly compiled. i dont get any error when i try to execute it from sql plus but then i try to call it from VB it gives me an error message.

• • Reply
  

  kedarrkulkar...

  All-Star

  34013 Points

  5468 Posts

  Re: ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 06:50 PM|LINK

  i just noticed... u have not set value to these parameters

  example...

          Dim prmcompanyname As New OleDb.OleDbParameter()
          prmcompanyname.Direction = ParameterDirection.Input
          prmcompanyname.ParameterName = "a_companyname"
          prmcompanyname.Size = 50
          prmcompanyname.DbType = DbType.AnsiString
          myCommand.Parameters.Add(prmcompanyname)

  here, this line is missing

          prmcompanyname.Value = companyname

  hope this helps...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
  • Marked as answer by rapatel83 on Dec 07, 2011 07:00 PM
• • Reply
  

  rapatel83

  Member

  7 Points

  4 Posts

  Re: ORA-06550: line 1, column 7: PLS-00306 ERROR!!

  Dec 07, 2011 06:59 PM|LINK

  Thanks a lot kedar.

  it worked.

  • Edited by rapatel83 on Dec 07, 2011 06:59 PM
  • Edited by rapatel83 on Dec 07, 2011 07:00 PM