1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

need output from EXECUTE IMMEDIATE but it's not a SQL data type

Discussion in 'General' started by n2singing, Sep 10, 2009.

  1. n2singing

    n2singing Guest

    Hi, there. I'm passing a pl/sql script from an ASP page to PL/SQL database as a VARCHAR because Microsoft ADO doesn't have a command type to match the ORACLE data type for this particular variable. I want to run this script inside a stored procedure using EXECUTE IMMEDIATE which effectively will create a variable that IS of the ORACLE data type I need (complex data type). However, I don't see how I can reference the variable that gets created when this script runs w/ the EXEC IMMEDIATE command. I can't use the "USING" or "OUT" parms because the data type of what I need to get back is not a SQL data type. Any suggestions??? Basically this script is populating several elements of a record type, and then I need to pass on the newly-created variable to another procedure.

    EX: here's a bit of my script that I run with the EXEC IMMEDIATE

    m_eval_request_data.eval_hdr.eval_request_type_id := 4; m_eval_request_data.eval_hdr.orig_system_reference := 'TEST'; m_eval_request_data.eval_hdr.duration_days := 45;

    Then, here's some of the stored procedure I call wherein I run the EXEC IMMEDIATE:

    -- (note: the s_eval_req_data contains the script that creates the complex variable)
    x_eval_request_data ix_lm_eval_request_pvt.eval_req_data_type;


    EXECUTE IMMEDIATE s_eval_req_data USING OUT x_eval_request_data;

    So, in essence, I need to have the script run in order to create a variable that IS of the required data type that I need later on, but I can't pass the variable into the stored procedure as that complex data type because ADO doesn't have a compatible "type".