Home » SQL & PL/SQL » SQL & PL/SQL » Pass sql file as argument in oracle stored procedure/package (10.2.0.4.0)
Pass sql file as argument in oracle stored procedure/package [message #617731] Thu, 03 July 2014 03:16 Go to next message
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 #617736 is a reply to message #617731] Thu, 03 July 2014 04:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
grpatwari wrote on Thu, 03 July 2014 13:46

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?


That's too much of work to do. I would create a job using DBMS_SCHEDULER to execute the sql scripts in SQL*Plus.
Re: Pass sql file as argument in oracle stored procedure/package [message #617739 is a reply to message #617731] Thu, 03 July 2014 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. Like any other parameter
2. UTL_FILE
3. No.

Re: Pass sql file as argument in oracle stored procedure/package [message #617742 is a reply to message #617739] Thu, 03 July 2014 05:17 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I didn't get the first question answer.

how to pass sql file to stored procedure(s)/package?

Please elaborate clearly.
Re: Pass sql file as argument in oracle stored procedure/package [message #617747 is a reply to message #617742] Thu, 03 July 2014 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is not clear?

Re: Pass sql file as argument in oracle stored procedure/package [message #617750 is a reply to message #617747] Thu, 03 July 2014 06:10 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
what is 1. Like any other parameter?

Re: Pass sql file as argument in oracle stored procedure/package [message #617752 is a reply to message #617750] Thu, 03 July 2014 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you understand answer 2?

Re: Pass sql file as argument in oracle stored procedure/package [message #617754 is a reply to message #617752] Thu, 03 July 2014 06:17 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
UTL_FILE is the package, PL/SQL PROGRAMS READ, write operating system text files.
Re: Pass sql file as argument in oracle stored procedure/package [message #617755 is a reply to message #617754] Thu, 03 July 2014 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So what do you need to read a file? its name and location, so this what you pass as parameter and as they are strings they are passed like any other string parameters.

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 Go to previous messageGo to next message
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.
Re: Pass sql file as argument in oracle stored procedure/package [message #617766 is a reply to message #617758] Thu, 03 July 2014 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Advices are the same ones.

Re: Pass sql file as argument in oracle stored procedure/package [message #617836 is a reply to message #617766] Thu, 03 July 2014 19:48 Go to previous message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much.

I will try ...
Previous Topic: Processing data from flat files to base files via staging tables
Next Topic: Simultaneous update in child table
Goto Forum:
  


Current Time: Fri Mar 29 07:45:01 CDT 2024