r/plsql • • Jul 23 '11

This site is a great way to tune your PL/SQL skills... I recommend this daily quiz highly.

Thumbnail plsqlchallenge.com
4 Upvotes

r/plsql • • Jul 22 '24

Oracle PLSQL Tutorial 38- Trigger Part 1 in Oracle PLSQL #plsql

Thumbnail
youtube.com
3 Upvotes

r/plsql • • Apr 17 '23

Plsql query

3 Upvotes

Why do we use dbms_stats.gather_table_stats('schema_name','table_name', cascade=>true, degree=>10);


r/plsql • • Jun 27 '22

Preview Multiple Images Before Upload Using JavaScript in Oracle APEX

Thumbnail
javainhand.com
3 Upvotes

r/plsql • • May 12 '22

Learning from scratch

3 Upvotes

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 • • Apr 08 '22

Is there a website with pl/sql challenges?

3 Upvotes

Something like Codewars but with pl/sql?


r/plsql • • Jan 26 '22

Need help understanding PLSQL for a specific ask.

3 Upvotes

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 • • Jun 29 '21

in sql rows to columns conversion for one person having 2 or more emails in rows want output in single column

Post image
3 Upvotes

r/plsql • • Jun 14 '21

FTP From PL/SQL

Thumbnail
dbexamstudy.blogspot.com
3 Upvotes

r/plsql • • May 05 '21

Online Environment Setup of PL/SQL in Hindi || Javainhand Tutorial

Thumbnail
youtube.com
3 Upvotes

r/plsql • • Apr 22 '20

Best way to clear temporary table

3 Upvotes

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 • • Nov 07 '19

Suggests for a good start at new job

3 Upvotes

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 • • Sep 09 '19

Simple guide to open Socket Connection using Oracle PL SQL Programming

Thumbnail
opencodez.com
3 Upvotes

r/plsql • • Aug 14 '19

Comparing query methods to display rows as columns

Thumbnail
rolkotech.blogspot.com
3 Upvotes

r/plsql • • Apr 10 '19

What should be known for PL SQL developer with 4 years of experience?

3 Upvotes

If possible share the links or document


r/plsql • • Dec 01 '18

Help with a Trigger

3 Upvotes

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 • • Apr 21 '18

Top 10 Oracle pl/sql Interview Questions

Thumbnail
asporacle.com
3 Upvotes

r/plsql • • Dec 10 '17

Insert values using SELECT statement

3 Upvotes

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 • • Aug 21 '17

Querying view from view

3 Upvotes

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 • • Jun 13 '17

SELECT CASE statement help..

3 Upvotes

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 • • Mar 09 '17

A very first project-oriented, environment-aware and code versioning deploy manager for Oracle.

Thumbnail
mitchdurango.wixsite.com
3 Upvotes

r/plsql • • Mar 07 '17

why exception doesn't work in loop? (example given below)

3 Upvotes

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 • • Oct 04 '16

How relevant is "Sams Learn PLSQL in 21 Days" today?

3 Upvotes

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 • • Aug 31 '11

Hinting a query that can't be modified: Here's one way

Thumbnail
kerryosborne.oracle-guy.com
3 Upvotes

r/plsql • • Aug 26 '11

All Things Oracle - Content for Oracle Developers and DBAs

Thumbnail
allthingsoracle.com
3 Upvotes