I have Stored Procedure which works fine in SQL Developer as;
create or replace
PROCEDURE "LIST_NAME" (S_NAME IN VARCHAR2) AS
REPORT_CON_VAL VARCHAR2(100);
TYPE my_cursor IS REF CURSOR;
the_cursor my_cursor;
BEGIN
OPEN the_cursor
FOR 'SELECT P_NAME
FROM REPORT_C_VALUES
WHERE SR_NAME = :S_NAME'
USING S_NAME;
DBMS_OUTPUT.PUT_LINE('Source is :' || S_NAME || ':');
LOOP
FETCH the_cursor INTO report_con_val;
EXIT WHEN the_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(report_con_val);
END LOOP;
CLOSE the_cursor;
END LIST_SOURCE_NAME;
I have to execute the above procedure from BODS, I have imported this SP as function in BODS and able to execute it via a script like below;
The above script does not give any error and complets successfully, but I don’t know how can I access the data generated by above SP in BODS.
Please help me to understand how can I capture and utilize the output of above SP in BODS… ?
Data Services will not use the result of that stored procedure. Getting results from an Oracle procedure is more work in DS. The stored procedure has to return the result set as a table. I haven’t written PL/SQL that does that in quite some time so I’ll have to suggest that you check one of the Oracle forums for information on that.
An alternative is to write an Oracle function instead of a procedure. The function can return a value. Or, add an OUT parameter to the procedure. Both suggestions are only useful if you’re returning a single value, not a multiple values.
Thanks a lot for your reply, So it means that we can use stored procedure only when it is returning a single value and not multiple values because I don’t think even if PL/SQL returns a table of values, BODS can read and store that table using Scripts ?
In the right circumstances an Oracle procedure that returns a result set could probably be used by Data Services. I proved this using SQL Server stored procedures some time ago in the SQL transform.
The challenge is finding someone that can write PL/SQL to return a result set properly. I’ve done a lot of PL/SQL in the past 16 years. Only once that I can remember did I write a stored procedure to return a result set.
Intetesting to know that, I was just wondering that people might be using PL/SQL in BODS to return the data set as an option when BODS performance gets slow and retriving data using SQL is fast.
This is urging me to ask you another question that , then what is he main purpose of using PL/SQL in BODS , one I know that we can use SQL to update our process tables when the job initiates and to keep logs of execution processes, but other than that where does PL/SQL used most?
I try to avoid using PL/SQL whenever I can. Instead I stick to using the native transforms as much as possible. There are two reasons for this:
Ease of maintenance - a set of transforms are usually easier to understand than 200 lines of a PL/SQL block, assuming both are equally documented/commented.
Metadata - once you stop using the native transforms you have lost the ability to use the View Where Used on a database table to see every Dataflow where the table is used.
One place where I will almost ALWAYS use PL/SQL is to perform DDL operations. For example, I have a client that disables the foreign keys on a large number of tables before the ETL starts loading data. We have run into problems where the ALTER TABLE statement fails due to a lock established by a different session.
If you were to write a Custom Function to retry the ALTER TABLE statement when it fails you still end up with an error in the ETL error log. But if you write PL/SQL to retry the ALTER TABLE statement within a loop, say once every 5 seconds up to 10 times, then you won’t get any error (assuming that it eventually is able to execute the command).
I did not tried it within SQL Transform as my previous experiences says that it take much longer to execute a SQL statement using a SQL Trnasorm then executing the same statement via SQL script.
But I am going to try and check if it will work with SQL Transform, Thanks for your suggestion …
And thank you Jim for your valuable comments as always.
(BOB member since 2007-09-12)