Pass sql file as argument in oracle stored procedure/package [message #617731] |
Thu, 03 July 2014 03:16 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I have one requirement in my assignment. I need to write the stored procedure or package to pass set of sql files one by one as argument and process those sql files. These sql files are exists in the Linux server and contain select or update queries. Select/update queries contain bind variables. Stored procedure read the select/update queries from sql file and process them.
Procedure to process the select/update queries:
i. Find out the bind variable column name(s), tablename from select/update query
ii. Take the distinct column values from the table
iii. Pass these values to select/update query which are exist in the sql file. Query should return the values.If it returns the value then we will generate the dat file like @sqlfile name <bindvarvalue>. We will generate the dat file with 5 set of values for each sql.
1. How to pass sql file to stored procedures package?
2. How to read the sql file in the stored procedure.
3. Is there any option to process my sql files?
Please advise on this.
|
|
|
|
|
|
|
|
|
|
|
Re: Pass sql file as argument in oracle stored procedure/package [message #617758 is a reply to message #617755] |
Thu, 03 July 2014 06:54 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Sorry, I am giving clear explanation with the file name and its contents. SQL file contains select or update queries like as below
Filename: R13_1_1_2060.sql
define b1=&1;
define b2=&2;
DECLARE
BEGIN
EXECUTE IMMEDIATE '/* R13_1_1_2060 */ UPDATE SCREEN_MESSAGING SCME
SET SCME.IS_DIRTY = ''Y'',
SCME.LAST_UPDATED_DATE = sysdate,
SCME.LAST_UPDATED_BY = :1
WHERE SCME.DATA_GROUP = :2
AND SCME.PROMPT_TYPE = 3 ' using &b1 , &b2 ;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('DBError: in Information DB Fetch Operation' || SQLCODE || ' and ' || SQLERRM );
END;
So I need to find the bind variables count,respective bind variable column name, tablename and extract the query.After extracting the query I suppose to run in the database by providing the bind variable values.
I hope this clarifies.
Please advice.
|
|
|
|
|