Execute ORACLE Stored procedure from Crystal

[iamyellow]

I would like to execute an ORACLE 11g Stored procedure from Crystal 2008 I inserted the following through Database Expert Add command:

DECLARE
WCTODO_UPCEXP WCTODO_UPCEXP_PACKAGE.WCTODO_UPCEXP_TYPE;
P_PERSON_ID VARCHAR2(32767);


BEGIN
-- WCTODO_UPCEXP := NULL; Modify the code to initialize this parameter
P_PERSON_ID := {?p_person_id};
TEST_REPORTS.WCTODO_UPCEXP_PROCEDURE ( WCTODO_UPCEXP, P_PERSON_ID );
COMMIT;
END;
-------------------------------------------
I encountered an ORA-06550 error. I know we can directly point the report to the stored procedure but we would like to do it this way to be able to progammatically manipulate the data connection.

 

[Turkbear]

Hi,
What are the details of the 6550 error...It should tell you what line(s) are causing a problem..

Does the SP compile and run when called in SqlPlus?

Google finds lots of examples of that error:

http://www.google.com/#hl=en&source...MWiCgAAAKoEBU_QwT2n&pbx=1&fp=d02fdb297b7f62dd

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[iamyellow]

I can run the stored procedure successfully from SQL Plus. I can also run the stored procedure successfully when I set the Stored Procedure as the datasource in Crystal. It is only when I try calling it as a SQL Command within Crystal the error occurs.
The error is:
Failed to retrieve data from the database
Details: SQL Exception: [SQL State:] line 7, column 22: PLS-00103: Encounterd the symbol '192d' when expecting one of the following:

*& = = + ; </> at in is mod remainder not rem
<an exponent(**)> <> or != or ~= >= <= <> and or like like2

The symbol '192d' is value that I entered in the person_id parameter.

 

[Turkbear]

Hi,
I am not sure that Oracle can process that assignment statement. How are you supplying the parameter when running from SqlPlus ?
Try this version as a command instead:

DECLARE   WCTODO_UPCEXP WCTODO_UPCEXP_PACKAGE.WCTODO_UPCEXP_TYPE;
P_PERSON_ID VARCHAR2(32767);

BEGIN   -- WCTODO_UPCEXP = NULL; 
TEST_REPORTS.WCTODO_UPCEXP_PROCEDURE (WCTODO_UPCEXP,{?p_person_id} );  
COMMIT; 
END;

To Paraphrase:"The Help you get is proportional to the Help you give.."