This discussion is archived
2 Replies Latest reply: Apr 4, 2012 1:13 AM by Asif Ansari RSS

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

Asif Ansari Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
              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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points