Questions tagged [plsql]

Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.

Filter by
Sorted by
Tagged with
3
votes
2answers
108 views

How to use “regular expression” to separate specific strings in Oracle

I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output: field1 field2 level 1 30 1 2 4 2 52 0 3 8 1 ...
1
vote
0answers
28 views

How to capture 'Deleted' and 'Updated' record after Merge in PL/SQL.TSQL 'Output' equivalent in PL/SQL

hope you're doing well. imagine I have a source and destination table like: Source(Column1 int , column2 varchar2(50)) Destination (Column1 int , column2 varchar2(50)) I want to merge these two ...
0
votes
1answer
22 views

Executing an oracle variable string as I do in SQL Server

In SQL Server I can do something like: DECLARE @VAR VARCHAR(100); SELECT @VAR = SELECT * FROM SYS.DATABASES EXECUTE(@VAR) How can I execute a string that I've created inside a variable in Oracle? ...
0
votes
1answer
38 views

Facing error 'No data found ' while trying to write a query using 'Cursor' PL/SQL in oracle

Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query just with the aim of practicing 'Cursor's . I have three tables with below structures : Student:(Student_id , ...
1
vote
2answers
46 views

Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE

Hope you're doing well. Since I'm new to Oracle database and PL/SQL it's a bit difficult for me to understand complicated queries and now I'm trying to figure out what this procedure exactly does.I ...
0
votes
1answer
44 views

How to pass table name and column name dynamically in Cursor

I would like to delete millions of records from lots many tables. In the below code, i'm trying to select a table with some filter condition and trying to delete using its row id. In the below, for ...
0
votes
1answer
28 views

How to get my result from Procedure

How can i get my out parameters from my procedure. My stl_mbc_cdr table has over 100 records and i cant see my results. declare call_charge VARCHAR(10); MSG_OUT VARCHAR(50); BEGIN FOR ...
1
vote
1answer
45 views

Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL

Hi everyone hope you're doing well. AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger as you can see below : CREATE OR REPLACE TRIGGER T_Insert AFTER INSERT ON tag ...
1
vote
0answers
41 views

Start dbms_services on standby database

I'm developing a trigger which starts and stops services when the pdb is open. The point is that my trigger doesn't stop the service if I execute the ALTER PLUGGABLE DATABASE ALL SAVE STATE; with ...
2
votes
1answer
85 views

Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL

Hi everyone hope you're doing well I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors . this is my function create or replace function ...
1
vote
2answers
49 views

After Inserting , Update / Insert into another table in pl/sql trigger

I am working on PL/SQL trigger i have two table TAG Table SR.NO ID TAG BRAND_ID ------ ---- ----- ---------- 1 10 ABC 99 MY_TAGS Table SR_NO TAG COUNT ...
2
votes
1answer
139 views

Facing error while trying to run dynamic pl/sql

Hope you're doing well I'm new to PL/SQL and I'm trying to write a dynamic PL/SQL. What I've written so far is : declare sql_stmt varchar2(100); v_student_id number := 1; v_new_stname ...
1
vote
1answer
45 views

Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL

Hi everyone hope you're doing well. I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not ...
0
votes
2answers
36 views

Split the results of a query into parts

I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor: An extra 4% for the first quarter with the highest salary. An extra 6% ...
1
vote
0answers
39 views

Cannot get the new table's FK-constraints in an AFTER ALTER trigger

I'm trying to create a trigger for AFTER ALTER event on schema's tables which would work with the new FK-constraints, here's my test code: CREATE OR REPLACE TRIGGER Test_Trigger AFTER ALTER ON SCHEMA ...
5
votes
2answers
539 views

PL/SQL function to receive a number and return its binary format

I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for ...
1
vote
2answers
51 views

Facing error while executing function inside the package in PL/SQL

Hi everyone hope you're doing well. I'm new to oracle and I'm reading about the concept of 'Package' and 'Package body'. I have a function as you can see below CREATE OR REPLACE Function ...
0
votes
0answers
25 views

How to execute pl/pgsql do block in a bash script? [duplicate]

I want to migrate my databases to a new server, and some of them contain views, which I don't want to migrate. So I wrote a bash script that roughly looks like this # Some commands to migrate the ...
2
votes
1answer
52 views

How can i select columns from two tables using REF

I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects ...
0
votes
1answer
27 views

Insert iterated values from one table to another in OracleDB

I'm pretty new in Oracle universe and I need help. I have to iterate through Users Table and insert these 3 specific rows into Task_Group Table. The hard thing to do is to check if these rows even ...
0
votes
1answer
17 views

Disable Oracle substitution parsing

I need to update a string (VARCHAR2) value in an Oracle database which contains Javascript code. The update script contains the following string: c===l&&p(a,b) which when executed from "...
1
vote
1answer
35 views

Can someone help me with this PL/SQL block?

Write a PL/SQL block to display the details of five employees who are getting the highest salary (table name is Employee) Is this how you do it?? DECLARE Cursor C is select * from Employee ORDER BY ...
0
votes
0answers
17 views

Extract table data in a BLOB field

I am trying to extract data from tables in a delimiter separated value format. To extract data i am concatenating all the columns of the table with each other and then converting the concatenated ...
0
votes
2answers
79 views

“Fire” a trigger to populate columns that are NOT NULL?

Working out of my SQL book at the moment. The question is, Create a trigger based upon the ENROLLMENT table. The trigger should “fire” before an INSERT statement. The trigger will populate all the ...
3
votes
1answer
67 views

If then else case PL/SQL if text is in list

I have a column that will return something like this: 01 05 Z7, Z4, 01 09,08 I then have two static lists: list a = [01, 02, 03, 04] list b = [Z7, Z2, 05] and our case statement returns one of the ...
3
votes
4answers
170 views

I don't know how to fix ORA-04091 mutating trigger error

I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table. The error is: error ORA-04091: table USUARIO.LINEAS is mutating, ...
0
votes
1answer
163 views

Not all variables bound ORA-01008: not all variables bound error

I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, ...
0
votes
1answer
33 views

Oracle - SQL ID from which procedure

I found few SQL IDs in AWR report. How to know, from which PLSQL object is it getting called based on sql id? As AWR removes extra spaces, user_source will not work.
0
votes
0answers
44 views

Oracle 10.2 unable to dynamically execute DBA command

I would like to execute the analyze index command for every user index within a database in order to populate the index_stats view in order to detect fragmented indexes by (DEL_LF_ROWS/LF_ROWS). The ...
0
votes
2answers
57 views

Sequence of columns displayed in output by sproc

CREATE OR REPLACE PROCEDURE test_1 ( p_table_owner IN VARCHAR2, p_table_name IN VARCHAR2, p_result OUT SYS_REFCURSOR ) AS BEGIN OPEN p_result FOR SELECT index_name, ...
0
votes
1answer
38 views

Why can i not call COUNT on a varray column in a select statement using oracle

I have a simple array type definition like this CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS VARRAY(10) OF VARCHAR2 (100); Now I have a column items of type LIST_OF_NAMES_T in my table TABLE1 ...
0
votes
1answer
29 views

Access tables from multiple schemas

I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles ...
0
votes
1answer
153 views

Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors. The query below informs you that ...
0
votes
1answer
56 views

Oracle PL/SQL find nearest object

There are two tables containing objects and their coordinates. One contains hotels, the other contains restaurants. How to build a table that for each hotel lists nearest restaurant name and the ...
1
vote
1answer
133 views

perform multiple conditional join on multiple tables

I have a situation where I need to get result based on conditional joins For example, SELECT CGI.SALES_BRAND_BSNSS_ID, CGI.SALES_BRAND_CD, NVL(MTRX. matrix_score_cd,'RV1') ...
-4
votes
2answers
64 views

Space usage procedure oracle for an input table [closed]

Is there any space usage display procedure in oracle for an input table like sp_space_used in sql server...
0
votes
1answer
23 views

Store Function Value into temporary table

Hi i'm currently trying to run a SQL query on a 15 000 + entry dataset. I'm using a custom Mysql function to determine the start of the current week and the end of it. Probleme is, I'm using those ...
0
votes
1answer
65 views

Syntax error in PL SQL [closed]

i'm trying to do a command that gather stats of tables that has more than 10 million rows. Right now only one table has, but i want the command to execute the gathering to more tables as soon as they ...
0
votes
1answer
49 views

Oracle schema object name when fire trigger

I need to get the object name and user name from oracle schema after fire a trigger. It executes on DDL statements. I need to get executed user name and object name which trigger executed on. Below is ...
0
votes
1answer
33 views

What slows down this histogram query?

I have a table my_link_t with columns t.weight type number and categorical speedcat type integer values from 2 to 8. I want to split data in buckets from min=0, max=0.6, step = 0.001 and build a 3D ...
1
vote
1answer
33 views

Is there any other way to replace cursor for the following scenario

Table IA Id Status Details 1 open aaa 2 close bbb 3 open ccc 4 open ddd 5 open eeee 6 open ffff 7 open gggg 8 close iii 9 close hhh 10 ...
0
votes
1answer
96 views

Extract values from oracle pl sql cursor

I have a function that returns a cursor. When I execute select t.id, t.f_rc(id) from my_table t I get id F_RC(TID) 1 < cursor > 2 < cursor > 3 < cursor > ... How to expand ...
0
votes
1answer
98 views

Custom return type of a function in Oracle PL SQl

I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different ...
0
votes
1answer
64 views

How to make a function from a concrete SELECT statement in Oracle SQL?

How to replace a concrete value with a variable in Oracle SQL? I have select 5, min(id) from my_table where id > 5 --AND ... complex condition UNION ALL select 6, min(id) from my_table ...
1
vote
0answers
48 views

Count neighbors and sum of the neighbors values

I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct ...
0
votes
1answer
80 views

Updating Database table from another machine

I am trying to update my database using a batch file. Batch file is located on machine say ABC which should pick a text file from the local directory on ABC and upload that file to DATABASE located on ...
1
vote
3answers
187 views

PL/SQL date or to_char question

begin if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN DBMS_OUTPUT.PUT_LINE('FRIDAY'); else DBMS_OUTPUT.PUT_LINE('Not Friday'); end if; end; / I am trying to ...
1
vote
2answers
37 views

Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL

I am much more familiar with SQL Server, but I am doing a project now in Oracle. There is a concept from SQL Server that I am not sure how to accomplish in Oracle. In SQL Server if I create a ...
0
votes
1answer
2k views

is it possible to use utl_file_dir = '*' scope = spfile in 12c

Oracle 12c has deprecated utl_file_dir = '*' scope = spfile, but I need to use this functionality. Is there any option to do it? Is it possible to give permission using Java classes?
0
votes
1answer
49 views

Variable Assignment and Usage in Oracle

Why does this fail in SQL Developer against an Oracle database and what would the an appropriate way of achieving the same? DECLARE v_pNbr NUMBER := 100150; begin select * from TARGETTABLE ...