2 Replies Latest reply: Apr 4, 2012 3:13 AM by Asif Ansari RSS

    Actuate 7 PL/SQL Stored Procedure Call Before the Report Run.

    Asif Ansari
      Hi,

      I need a small help to call a pl sql stored procedure in actuate 7.
      I wrote a code a on component content

      Function runProc(procName as String, connection As AcDBConnection )
      ' Insert your code here
      Dim statement As AcDBStatement
      Dim results as string
      ' Prepare statement
      Set statement = connection.Prepare("BEGIN " & procName & "; END;" )
      If statement Is Nothing Then
      results = "Failed to prepare statement."
      results = results & " " & connection.GetSpecificErrorText( )
      results = results & " " & connection.GetGeneralErrorText( )
      Exit sub
      End If
      ' Execute sprocoutparam
      If statement.Execute() = 0 Then
      results = "Stored procedure " & procName & "execution failed."
      results = results & " " & connection.GetSpecificErrorText( )
      results = results & " " & connection.GetGeneralErrorText( )
      Else
      results = "Stored procedure " & procName & " execution success."
      End if
      ShowFactoryStatus(results)
      End Function
      Now this Code I am calling on the Chart Start Method
      Sub Start( )
          Super::Start( )
          ' Insert your code here
          Dim ProcName as String
          Dim connectionObject As AcDBConnection
          ProcName="DELETECUSTOMER(1234)"
          runProc(ProcName, connectionObject )
      End Sub
      Now when i Run the Report i get this Error !
      NewReportApp::Frame::Chart%Start(9): Invalid procedure reference. - (runProc)
      1 Semantic Error(s) found

      Thanks,
      Asif
        • 1. Re: Actuate 7 PL/SQL Stored Procedure Call Before the Report Run.
          Asif Ansari
          hi,

          Finally Figured Out Here....

          Steps to Call the Stored Procedure In Actuate 7

          1 – Right Click on Content – Frame and go to properties and method Tab.
               1 – Click on New Button to you create own custom method.
               2 – Write the following code :
          Sub runProc(procName as String, connection As AcDBConnection )
               
               Dim statement As AcDBStatement
               Dim results as string
               Set connection = GetConnection()
               'Prepare statement
               Set statement = connection.Prepare("BEGIN " & procName & "; END;" )
                    If statement Is Nothing Then
                         results = "Failed to prepare statement."
                         results = results & " " & connection.GetSpecificErrorText( )
                         results = results & " " & connection.GetGeneralErrorText( )
                         MsgBox "Procedure Not Called....Badddddd"
                         Exit sub
                    End If
          
          
               ' Execute sprocoutparam
                    If statement.Execute() = 0 Then
                         results = "Stored procedure " & procName & "execution failed."
                         results = results & " " & connection.GetSpecificErrorText( )
                         results = results & " " & connection.GetGeneralErrorText( )
                         MsgBox "Procedure Not Called....Badddddd" & results
          
                    Else
                         results = "Stored procedure " & procName & " execution success."
                         MsgBox "Procedure Called....Wowwww"
                    End if
                    
                    ShowFactoryStatus(results) 
          End Sub
          2 – Now let call the stored procedure before our actual report query runs.

               1 - Right Click on Content – Frame and go to properties and method Tab.
               2 - Select the Start Method and Click on Override button.     
               3 - Write the following code:
          Sub Start( )
               Dim ProcName as String
               Dim connectionObject As AcDBConnection
               Set connectionObject = GetConnection()
               ProcName="XXTEST ()"
               runProc( ProcName, connectionObject ) 
              Super::Start ( )
          End Sub
          3 – Now design your report as usual using the Textual Query window.
          • 2. Re: Actuate 7 PL/SQL Stored Procedure Call Before the Report Run.
            Asif Ansari
                      If statement.Execute() = 0 Then
                           results = "Stored procedure " & procName & "execution failed."
                           results = results & " " & connection.GetSpecificErrorText( )
                           results = results & " " & connection.GetGeneralErrorText( )
                           MsgBox "Procedure Not Called....Badddddd" & results
             
                      Else
                           results = "Stored procedure " & procName & " execution success."
                           MsgBox "Procedure Called....Wowwww"
                      End if
            the code is always going in If part and give the ORA-24333 error.

            Can anyone help on this please ?


            Thanks,
            Asif