r/plsql • u/pianowow • Jul 23 '11
r/plsql • u/apexysatish • Jul 22 '24
Oracle PLSQL Tutorial 38- Trigger Part 1 in Oracle PLSQL #plsql
r/plsql • u/rohitsingh26 • Apr 17 '23
Plsql query
Why do we use dbms_stats.gather_table_stats('schema_name','table_name', cascade=>true, degree=>10);
r/plsql • u/apexysatish • Jun 27 '22
Preview Multiple Images Before Upload Using JavaScript in Oracle APEX
r/plsql • u/kibamar • May 12 '22
Learning from scratch
I have found myself in a position where I need to learn PLSQL. I have other mostly front end experience and was hoping someone here would be able to set me on the best path for picking up PLSQL. I have done the initial googling I would do to pick up any new skill but there does not seem to be many resources out there and what I have seen is very old.
r/plsql • u/zeroxthegrim • Apr 08 '22
Is there a website with pl/sql challenges?
Something like Codewars but with pl/sql?
r/plsql • u/Keitaru84 • Jan 26 '22
Need help understanding PLSQL for a specific ask.
Hi so I'm totally new to plsql and have taken a course from udemy on it and that's it. Have an ask from work to make use of PLSQL with sql to answer a very specific business question.
I have a set of road equipment data, using PLSQL I'm suppose to create dataset that would link a specific equipment to another equipment along a network of roads.
e.g. Equipment A (downstream equip), Equipment B (selected equip), Equipment C (upstream equip) with camera B being the middle camera to find out what's the other 2 Camera ID's that comes before and after Equipment B. (ID number not in sequential order that's it logical issue I've found myself stuck at)
I've identified what functions and features within PLSQL I may need to use to be able to solve this but I'm not sure how do I go about doing it. I do know I'll need to use cursors, for or while loop, variables, parameters. Not sure if I'm missing anything else.
Have created a temp table from my pre-exiting data sets to pull all the relevant information together that is required as a sample for this.
Picture sample to illustration of my end desire:
Created dataset from all the other pre-existing tables.
Lets call this Equipment table.
EQUIPMENT_ID
link_ID (portion of road)
NODE_FR_ID (starting node of link_id)
NODE_TO_ID (ending node of link_id [to-id >>>> fr-id])
ROAD_NAME (Name of the road)
DIRECTION = (1 or 2)
r/plsql • u/Liebe_Cyber • Jun 29 '21
in sql rows to columns conversion for one person having 2 or more emails in rows want output in single column
r/plsql • u/apexysatish • May 05 '21
Online Environment Setup of PL/SQL in Hindi || Javainhand Tutorial
r/plsql • u/ass-holes • Apr 22 '20
Best way to clear temporary table
Hi all
I have a temp table that I fill with temp data, mostly for testing purposes at this time.
I fill this table using an Excel that I upload via ASP.NET which all works fine. The 'problem' is that I use a stored procedure to do this. Every row in Excel has 3 columns and I call the stored prod per row to fill the table. This works well.
My problem is that I have no idea how to clear it before I insert new temp data. I wanted to create a seperate stored prod that does just that but I found out I have no idea how to just execute one statement without input or output parameters .. Google isn't helping me at all, probably since I don't really know the right keywords. I can use the delete statement in the SQL worksheet just fine, I just don't know how to do it through stored prod. Or is this not a good way to do this? I used the delete statement in the stored procedure that fills the table but since I'm an idiot, it always clears it for every row.
I'm still learning so please do guide me in the right direction.
Thanks!
r/plsql • u/mr85albi85 • Nov 07 '19
Suggests for a good start at new job
Hi all guys, as i said I started a new job, lots of plsql inside. I know sql, but really I'm a Java guy... I live in debug, I'd like to know if you can suggest me some good debug practices in Pl/Sql.
Thanks a lot in advance, any help is appreciated
r/plsql • u/Shilpa_Opencodez • Sep 09 '19
Simple guide to open Socket Connection using Oracle PL SQL Programming
r/plsql • u/takacsroland • Aug 14 '19
Comparing query methods to display rows as columns
r/plsql • u/Joyal1995 • Apr 10 '19
What should be known for PL SQL developer with 4 years of experience?
If possible share the links or document
r/plsql • u/javycane • Dec 01 '18
Help with a Trigger
Hey guys first time posting here but I was looking for some guidance with this
I want to make a Trigger that updates an attribute in Table B after an insert in Table A. Still learning triggers and the class isn't really providing much info on this so any help is appreciated.
Create or Replace Trigger JavierRewards
After INSERT
on Purchases
For each row
declare
v_earned_points number;
v_extra_points number;
Begin
select earned_points into v_earned_points from customers where customers.cust_id = new.cust_id;
select extra_points into v_extra_points from rewards_tier where rewards_tier.tier_id = customers.tier_id;
Update customers
set customers.earned_points = Round((new.Purchase_amount * 1.5) + (new.Purchase_amount * v_extra_points)) + earned_points
where :new.cust_id = :old.cust_id;
end;
r/plsql • u/lightblue9 • Dec 10 '17
Insert values using SELECT statement
My table has three columns: id, lname, username.
Username column is null. Requirements for username are [ 'z' + 2 first letters of lname + last 4 digits of id ]
select 'Z' || substr(lname, 1, 2) || substr(ssn, -4) as USERNAME from students27;
How can i insert this into my username column? I tried:
insert into table (username) select concat('z', substr(lname, 1, 2), substr(ssn, -4), username) from table;
and
insert into table (username) select 'Z' || substr(lname, 1, 2) || substr(ssn, -4)|| from table;
What am i doing wrong?
r/plsql • u/atcervantes • Aug 21 '17
Querying view from view
I have a view with a lot of tables and view. I was wondering if it is a good practice to call a view from another view and if this affects somehow the performance?
Wouldn't it be a better idea to call directly to the tables?
Thanks in advance!
r/plsql • u/Naheka • Jun 13 '17
SELECT CASE statement help..
I have two fields; let's call them 'skill name' and 'group'. Both have valid values.
My desired result is to use a CASE statement (SELECT, not UPDATE) to read that when 'skill name' is 'A', 'B' or 'C', the 'group' value will be overwritten with a different value of 'groupA', 'groupB', 'groupC' with an ELSE of the original values.
Note that I want to do this in a SELECT only as this modification is for reporting only and I do not want to UPDATE prod data.
I struggle with the syntax and now beginning to question if it's feasible. Disclosure, SQL Server syntax is what we use 90% of the time so I might be missing something here.
Thanks for the help.
r/plsql • u/mitch_durango • Mar 09 '17
A very first project-oriented, environment-aware and code versioning deploy manager for Oracle.
r/plsql • u/O_GOLEM_O • Mar 07 '17
why exception doesn't work in loop? (example given below)
consider this simple example from employees table of HR schema in oracle 12c.
declare
e_id number;
fname varchar2(20);
lname varchar2(20);
sal number;
cursor emp_cursor is select employee_id,first_name,last_name,salary from employees;
begin
open emp_cursor;
loop
fetch emp_cursor into e_id,fname,lname,sal;
dbms_output.put_line(e_id||' '||fname||' '||lname||' earns '||sal||' per month');
if sal > 10000 then
dbms_output.put_line('this person should be robbed');
end if;
exit when emp_cursor%notfound;
end loop;
end;this runs properly, however when I put exception in it
declare
no_val exception;
e_id number;
fname varchar2(20);
lname varchar2(20);
sal number;
cursor emp_cursor is select employee_id,first_name,last_name,salary from employees;
begin
open emp_cursor;
loop
fetch emp_cursor into e_id,fname,lname,sal;
dbms_output.put_line(e_id||' '||fname||' '||lname||' earns '||sal||' per month');
if sal > 10000 then
raise no_val;
end if;
exception
when no_val then
dbms_output.put_line('this person should be robbed');
exit when emp_cursor%notfound;
end loop;
end;it will throw an error, I tried many such examples using cursor, loop and exception however exception within loop never worked. kindly let me know if there is any way for using it inside loop.
r/plsql • u/apc0243 • Oct 04 '16
How relevant is "Sams Learn PLSQL in 21 Days" today?
I'm trying to get a better handle on plsql and found this from 2001. There are cheap dead tree copies on amazon for the full text, but it uses an 8i database while I'm using 11g. I'm wondering if there's anything to be learned from it?
I am a fairly competent programmer using python and SQL. My boss wants me to get better acquainted with PLSQL which hasn't been hard really. but I get lost on a lot of the system variables available, and how oracle structures everything behind the scenes in general. I get stuck in an OOP mindset and I don't think that's appropriate with PLSQL.
I used Sams Learn SQL in 10 minutes (or whatever it's called) and liked having it as a reference as I was learning, so I was thinking of reading through this dated piece too. Any thoughts?
r/plsql • u/rhetormagician • Aug 31 '11
Hinting a query that can't be modified: Here's one way
r/plsql • u/pianowow • Aug 26 '11