To all you SQL Guru's out there......I bow down to you!!
I was wondering if I may pick ur brains for a moment or two.....
Basically, I need to insert the following code into a Button on a Form. The code works perfectly in SQL + but i am unsure as how to adapt it into the button, as the Procedure needs to get the data from fields on the form, as opposed to me entering the data wen executing it from SQL +..........
Create or Replace procedure insert_promoinfo (WrkFrc_ID varchar2, NGrade varchar2)
Use a when-button-pressed trigger. You can put the PL/SQL block in the trigger (starting with DECLARE), or simply have a one line in the trigger:
insert_promoinfo;
If you do the one-liner, add a procedure in the program units except I wouldn't pass in parameters at this point (you can do that later when it works without them).
The insert_promoinfo procedure body would look like this:
i am a little hazy about wat u mean with this.....
"If you do the one-liner, add a procedure in the program units except I wouldn't pass in parameters at this point (you can do that later when it works without them)."
Could u explain this to me a bit more, please?? i dont understand how it could work without the parameters......
Sorry, i'm a bit of a newbie at all of this, so there maybe more questions on the way...i hope u dont mind.....
I could do the simple insert method, but we have to make as many procedures as we can for this subject, and also i am going to develop the function further by adding some conditions.
thanks again!!
regards
Oh, i almost forgot..... I would also like to find out how to link an Oracle DB to an ASP page. nothing fancy, just to bring up records from a table and display them on the ASP page........ Open house on that question people!! thank you in advance!
"If you do the one-liner, add a procedure in the program units except I wouldn't pass in parameters at this point (you can do that later when it works without them)."
Could u explain this to me a bit more, please?? i dont understand how it could work without the parameters......
You can use parameters or not, it is up to you. I suggested not using parameters because that is a bit more complex than picking off values inside of data block fields.
I need some background on your experience level to better help you, so please answer the following questions. I'll tailor future posts based on what I think you know.
Do you know how to add a trigger to a button or data block item?
What can you put inside an item trigger?
Do you know how to create a function or procedure in the program units area?
Do you understand what is meant by ":datablock_name.item_name" as far as using it as a value?
1) Do you know how to add a trigger to a button or data block item?
I know how to add a new trigger by either clicking on the CREATE (green plus sign) button in the Object Navigator in Oracle Forms Builder, or by using the Smart Triggers menu on the Right-Click popup menu.
2) What can you put inside an item trigger?
You can place a procedure of some kind that will run depending on the function EG: Save button "Begin COMMIT_FORM End;".
3) Do you know how to create a function or procedure in the program units area?
Apart from Clicking the CREATE (green plus sign) button, I do not know how to do the above.
4) Do you understand what is meant by ":datablock_name.item_name" as far as using it as a value?
More or less. The ":datablock_name" refers to the name that i have given the datablock that contains the values i need to retrieve, or update. "item.name" refers to the specific item or field name that reside within the datablock specified.
Alrighty then. I could do the simple insert method, but we have to make as many procedures as we can for this subject, and also i am going to develop the function further by adding some conditions.
Why do you have to make as many procedures as you can? If a simple insert statement works, why complicate it? You can always build around the basic insert statement.
Using procedures vs triggers (when button pressed, etc) is a matter of technique. Definitely, if something is going to be used more than once, put it in a procedure. If the action or processing of data is simple, leave it in the trigger. When it starts getting complicated or lengthy, you can convert the trigger to a procedure (copy the code in the trigger, make a new procedure in the program units, comment out the trigger body, add a line at the top that identifies what to do (using a procedure, just type the procedure name, and the form will jump to the procedure).
If you are using a procedure that is complex or has a lot of testing in it, that's when parameters come in handy. You can call a function or another procedure from the calling procedure and pass parameters as needed. Most of the time it is just as convenient to refer to values using :datablock.item, but that is just technique.
So back to your original question, you aren't doing a "create or replace procedure as" in the form. You don't need the "create or replace" part. You create the procedure by making a new procedure (using the green + button) in the program units area. The + button creates a template for you.
the reason why we have to make as many procs as possible is coz this is for an assignment, and we get marks for watever we do. but we havent exactly been taugh how to attach the procs to Forms, so thats where we get difficulty. I am hoping that i can adapt the help you give me to the other procs i have.
ok, so if i do the simple insert using the datablocks, do i have to structure the insert line like the table? ie:
This is my promotions table. it is called ADVANCEMENTS.
ADVANCEMENT_DATE NOT NULL DATE
NEW_GRADE VARCHAR2(2)
LAST_ADVANCEMENT_DATE DATE
OLD_GRADE VARCHAR2(2)
WRKFRCE_WORKFORCE_ID NOT NULL VARCHAR2(5)
When i write the Insert query are the the values in order of the above?
Insert into Advancements values (AdvancementDate,:datablockname:NGrade,'',OldGrade,WF_ID);
Reply With Quote
Bookmarks