Thursday, 28 August 2014

Few Examples on Dynamic SQL

------------------------------------------------------------------
DECLARE
v_table_name varchar2(100) := 'test_dept';
v_stmt varchar2(1000);
BEGIN
v_stmt := 'insert into '||v_table_name||' values(50,''HR'')';
execute immediate v_stmt;
dbms_output.put_line(v_stmt);
END;

-------------------------------------------------------------------
DECLARE
type sob_record is record
(
name gl_sets_of_books.NAME%type,
currency_code gl_sets_of_books.CURRENCY_CODE%type,
set_of_books gl_sets_of_books.SET_OF_BOOKS_ID%type
);
v_stmt varchar2(1000);
curr sys_refcursor;
row_rec sob_record;
BEGIN
v_stmt := 'select name,currency_code,set_of_books_id from gl_sets_of_books where set_of_books_id=:input';
open curr for v_stmt using 2023;
loop
fetch curr into row_rec;
exit when curr%notfound;
dbms_output.put_line(row_rec.currency_code || '    '||row_rec.name);
end loop;
END;


-------------------------------------------------------------------------------

DECLARE
type status_record is record
(
cid xx_account_status_swap.customer_number%type,
acc_status xx_account_status_swap.new_account_status%type
);
type status_table is table of status_record;

v_stmt varchar2(1000);
curr sys_refcursor;
status_coll status_table;
BEGIN
v_stmt := 'select customer_number,new_account_status from xx_account_status_swap where success_status=:input';

open curr for v_stmt using 'P';
fetch curr bulk collect into status_coll;

for i in 1..status_coll.count loop
dbms_output.put_line(status_coll(i).cid||'   '||status_coll(i).acc_status);
end loop;

dbms_output.put_line('----------------------------------------------');
dbms_output.put_line(status_coll.count);
dbms_output.put_line('----------------------------------------------');
END;

--------------------------------------------------------------------------------


Ur's
AmarAlam

50 comments:

Unknown said...

Its impressive to know something about your note on Oracle apps Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on Cub training oracle apps course.

Sekhar Byna said...

Very intersting notes on ebusiness Suite
Please do share your articles like this.THis link also useful to Find info On Oracle apps-jobs,interview tips,Resume preparation,functional issuesOracle eBusiness Suite

Sekhar Byna said...

Very intersting notes on ebusiness Suite
Please do share your articles like this.THis link also useful to Find info On Oracle apps-jobs,interview tips,Resume preparation,functional issuesOracle eBusiness Suite

Unknown said...

The modules in SAP Training are highly integrated in real-time, in the sense that information is shared across, and between the modules. Also, data integrity is maintained and errors are minimized as data is entered only once. Visit Us,
Sap training in chennai | SAP Success factor training in chennai | Workday training in chennai | Success factor training in chennai

glennparyy said...

UIKit framework rather than the AppKit framework. Unix-compatible either.Major versions of iOS are appear annually. The accepted release, iOS 8.2, was appear on March 9, 2015

Java training in Chennai | Android training in chennai | IOS training in chennai

Anonymous said...

In iOS, there are four absorption layers: the Core OS layer, the Core Services layer, the Media layer, and the Cocoa Touch layer. The accepted adaptation of the operating arrangement.

Java training in Chennai | Android training in chennai | IOS training in chennai

Anonymous said...

The user interface of iOS is based on the absorption of absolute manipulation, application multi-touch gestures. Interface ascendancy elements abide of sliders, switches, and buttons.

Java training in Chennai | Android training in chennai | Java training in Chennai

Jessycharl said...

The accepted release, iOS 8.2, was appear on March 9, 2015. In iOS, there are four absorption layers: the Core OS layer, the Core Services layer, the Media layer, and the Cocoa Touch layer.

Java training in Chennai | Android training in chennai | Java training in Chennai

Unknown said...

Thank you for the information ...
very interesting and useful ...
steady info ...
Behind yes watching flim visit online at: http://inibos.com
.

Unknown said...

Wiztech Automation Solutions is the Best Training Institute in Chennai,started in the year 2006 and it Extended its circle through providing the best Education as per the Global Quality Standards. Hence our Training Center in Chennai was Recognized by IAO and ISO for its inspiring Education Quality Standards. Wiztech Automation Solution, the PLC SCADA Training Academy in Chennai offers both PLC, SCADA, DCS, VFD, Drives, Control Panels, HMI, Pneumatics, Embedded systems, VLSI, IT, Web Designing, AutoCad Training courses in chennai with latest various brands. Wiztech Automation Solutions offers Real Time Training Courses with 100% Placement support in chennai.

PLC Training in chennai
SCADA Training in chennai
Embedded Systems Training in chennai
VLSI Training in chennai
Automation Training in chennai
Industrial Automation Training in chennai
Process Automation Training in chennai
DCS Training in chennai
Inplant Training in chennai
Placement
PLC Course in chennai
Best PLC Training in chennai
PLC Training in chennai
Robotics Training in chennai
Embedded Training in chennai
IT Training in chennai
Web designing Training in chennai
AutoCad Training in chennai

Anonymous said...


Excellent post, some great resources. Styling your blog the right way is key. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the time to discuss this, I feel happy abouit and I love learning more about this topic.

Java Training in Chennai

Anonymous said...

The information you have given here is truly helpful to me. ANDROID Training in Chennai it hat helps improve your investment in knowledge of developers and increase the value of employer’s developing..
ANDROID Training in Chennai

kanmani karthick said...

About the SQL information really very excellent and easily observe all given information,thanks for sharing that valuable information.
Oracle SQL Training in Chennai

IICT said...

Amazing article on Dynamic Sql..

Regards
CCNA Training in Chennai

rekhila said...

Your blog is more inforamtive..You have clearly explained about..Keep on blogging more like this ..
weblogic training in chennai

Ridhima said...


All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.


Java training in Adyar

Mithun said...

Awesome blogpost.
Android Training in Chennai | Best Android Training in Chennai

Unknown said...

Thank you regarding Wonderful and also Beneficial Post.App Buzz This article is very is made up of lot more information regarding This particular Matter.

Anonymous said...

SQL and PLSQL are best course providing in this blog..
Best SEO training in hyderabad all modules are clearly solved and practice with guide.seo training in hyderabadAnd clearly getting knowledge of seo.

Regina Hilary said...

The war between humans, orcs and elves continues earn to die . Lead your race through a series of epic battles, using your crossbow to fend off foes and sending out units to destroy castleshappy wheels . Researching and upgrading wisely will be crucial to your success! There are 5 ages total and each one will bring you new units to train to fight in the war for you cause.
earn to die 2
Whatever you do, don’t neglect your home base because you cannot repair it and once it is destroyed, you lose! Age of War is the first game of the series and really sets the tone for the Age of War games . Also try out the Age of Defense series as it is pretty similar.
In this game, you start at the cavern men’s age, then evolvetank trouble ! There is a total of 5 ages, each with its units and turrets. Take control of 16 different units and 15 different turrets to defend your base and destroy your enemy.
The goal of the game also differs depending on the level. In most levels the goal is to reach a finish line or to collect tokens. Many levels feature alternate or nonexistent goals for the player.

Unknown said...

Your have beautifully presented your thought in this blog.Our Informatica Training in Chennai aims to teach beginners and employees.JoinFITA offers a best training with experienced professionals.For more details visit our website.

Unknown said...

Excellent post..
SAS Institute introduced the SAS Certified Professional Program,training proper understanding of how the SAS software works. Among the five certification programs that SAS Institute has come up with, SAS training can be considered as the entry point into the big data and the data analytics industry.
SAS online training in hyderabad

franklin said...

excellent post.This was really useful.Really awesome.Keep Blogging

Hadoop training in coimbatore
Java training in coimbatore
Oracle training in coimbatore
Informatica training in coimbatore
Oracle training in coimbatore
Informatica training in coimbatore

Unknown said...

Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
salesforce training in hyderabad

Anonymous said...

nice posts..
SAP HANA

online training

Anonymous said...

Excellent ppsts..
SAP GRC training in hyderabad


Anonymous said...

Nice Information:
Telugu Cinema Contains Telugu Cinema News, Latest Movie Reviews, Actor, Actress, Movie Galleries And Many More Telugu Cinema News

Unknown said...

sql server is simpliest database management system,and very interesting to learn thanks for sharing this .for best SAP HANA Online Training SAP HANA Online Training
get certified and full knowledge on the course.

Anonymous said...

Valuable post...Amazing psot.. with all details SAP Training in Chennai

swapna said...


Nice Information

Merry Christmas Santa Gifts - Merry Christmas Images

,Christmas Images, Merry christmas pictures, Merry Christmas Images Free Download

swapna said...

Nice Information

New Year 2017 - New Year 2017 Images Happy New year

Wishes 2017, Happy New Year 2017, New Year Wishes, New Year Greetings

Mithun said...

Thanks of sharing this post…Android and iOS is the fastest growing technology that helps to get your dream job in a best way, so if you wants to become a expertise in Mobile Apps get some training on that Technology.
Regards,
Android Training Institutes in Chennai | iOS Training Institutes in Chennai

Unknown said...

Its really helpfull.., thanks for sharing this.

Papa jobs Providing latest information about Current affairs, Bank Recruitment, Government jobs, Bank jobs, IT jobs., check this link papa jobs i hope this is very use full to you and all the very best Guys.

Unknown said...

Nice information for job seekers. Thanks for sharing the awesome information , i also recommend online Notification

Unknown said...

Thanks , you are giving very important information for many job seekers. In my experience , i got job through Recruitment Voice and according to me it is also one of the best site.

asp .net web development company said...

Awesome blog,Thanks for sharing very useful information article keep sharing knowledge with us.
qa testing services .
software testing services company.
qa and testing services .
quality assurance and testing services.
software testing company india

Unknown said...

Awesome post, Thanks for sharing useful information article

swapna said...


Nice Information
Telugu Cinema News, Latest Movie Reviews, Actor, Actress, Movie Galleries And Many More http://www.telugucinema.com/ Telugu Movie Reviews

Unknown said...

happy new year 2018 sms
happy new year quotes in hindi
advance happy new year 2018
whatsapp new year dp
happy new year 2018 wallpaper 3d
new year whatsapp dp
this post is really informative, I want to share it with all of my friends. Please allow me to share it with my friends who will subscribe to your site.

Sumaya Manzoor said...

Your article inspired me to learn more and more about this technology, waiting for your next content!!
Selenium Training in Chennai
Selenium Course in Chennai
iOS Course in Chennai
Digital Marketing Training in Chennai
J2EE Training in Chennai
Future of testing professional
SEO Course in Chennai
SEO Training Institute in Chennai

Anbarasan14 said...

You have posted a worthy blog keep sharing. Nice article I was really impressed by seeing this article, it was very interesting and it is very useful for me.

Spoken English Franchise
Franchise For Spoken English Classes
Education Franchise
Education Franchise India
Education Franchise Opportunities In India
Education Franchise India
Top Education Franchises

Digital Marketing Company in Vijayawada said...

Useful Information :

Looking for the Best [url= Digital Marketing Company in Vijayawada ][/url] , We provide training on live projects, internship, Flexible Classes, Free Demo @ praiseads

Mithun said...

Great Post with lots of useful information. Special thanks for this blog highly useful..
SAP Training in Chennai | SAP ABAP Training in Chennai | SAP FICO Training in Chennai | SAP MM Training in Chennai

Training for IT and Software Courses said...

Thanks For sharing a nice post about SAP FICO Training Course.It is very helpful and SAP FICO useful for us.SAP FICO training in bangalore

Jerry said...

Best and comprehensive guidelines to start a computer store. if you need any further help in this regard you may contact Same Day Geek

Joyal said...

Effective blog with a lot of information. I just Shared you the link below for Courses .They really provide good level of training and Placement,I just Had Digital Marketing Classes in this institute , Just Check This Link You can get it more information about the Digital Marketing course.


Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

Chavi. Appfinz said...

Excellent pieces. Keep writing such kind of information on your
page. Im really impressed by it.
Hi there, You have performed a great job. I’ll certainly digg
it and in my opinion recommend to my friends.
I am confident they’ll be benefited from this web site.
website designing company in karol bagh

website designing company in pitampura

website designing company in rohini

website designing company in noida
responsive web design company in india
website designing company in noida
website development company in delhi
wordpress development company delhi
magento development company in delhi

Unknown said...

What may you recommend about your publish that you simply made some days ago...
leather jacket
wedding photography

Anonymous said...

dit is geweldig, ik ben je heel erg dankbaar. Kijk gerust op mijn website https://mindrops.com/nl/webdesign-bureau-belgie

Mikaz Renik said...

This is a really good blog that is sharing valuable knowledge with its readers. I liked it very much. If you are looking for Restaurants in Virginia you can look it through our website Movingist Tavel Tips
.

Post a Comment