What is the difference between function and procedure in PL/SQL?
Duplicate: what is the difference between a function and a procedure What is the difference between function and procedure in PL/SQL ? ...
plsql
Is it possible to know from which package a procedure has been called ?
Suppose that I have 2 packages : A and B. In package A, I call a procedure in package B. In procedure of packcage B, is it possible to know that procedure has been called from package A ? Thank you very much for these informations. It was very instructive. I appreciate. ...
PLSQL : Get sum for each day of week and total sum for week in a single query.
Let's say , I have a table, ClientTrade, like thus : ClientName , TradeDate , Quantity And I want to create a query in Oracle PLSQL which should return the result like this : (The days are derived from the TradeDate column and Mon = sum(Quantity) for Mon , Tue = sum(Quantity) for Tue ... etc.) ClientName Mon Tue Wed Thu Fri Sat Sun ...
Shift operators in PL/SQL
Whether there is an alternative of shift operators in PL/SQL? There is bitand function, but it accepts only *binary_integer*-type arguments. What should i do if i need check up lower/higher bit of really long number (probably set in the line)? In C there are << and >> operators. How I can realise them in PL/SQL? ...
PHP array binding on Oracle PL/SQL call
I have a PL/SQL procedure that takes as input parameter a TABLE OF TYPE NUMBER(10). I'am trying to call it from php right now. The binding doesn't rise any error, everything is fine. But the content of my binding is not the one I expected! For example I bind an array: array(19465,19467) On the pl/sql side I get this kind of values: -2....
SYSDATE - 1 error on pl/sql function
Hi curtisk/all, I have an issue: when i issue this function below ti gives me the following error: select 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS=>DBMS_LOGMNR.NEW);' from v\$archived_log where name is not null; select 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS=>DBMS_LOGMN...
PLS-00306: wrong number or types of arguments in call to...
Can anyone help me with this error. It works when I try the first procedure but not the second when I enter two numbers. Any ideas? create or replace package LE2_P1 is procedure GENERATE_MULTIPLICATION_TABLE(p_axis_both in number); procedure GENERATE_MUTLIPLICATION_TABLE(p_axis_x in number, p_axis_y in number); end LE2_P1; / create or ...
Oracle PL/SQL: Loop Over Trigger Columns Dynamically
Inside of a trigger I'm trying to loop over all columns on a table and compare the new values to the old values. Here is what I have so far: CREATE OR REPLACE TRIGGER "JOSH".TEST#UPD BEFORE UPDATE ON "JOSH"."TEST_TRIGGER_TABLE" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare oldval varchar(2000); newval varchar(200...
validation on email / postcode fields in sql/oracle
Hi, Would be gratefull for some advice on the following - Is it possible to validate email and postcode fields through some kind of check constraint in the sql in oracle ? or this kind of thing as i suspect pl/sql with regular expressions ? Thanks ...
PLSQL - Triggers: Cannot modify a column which maps to a non key-preserved table
What I'm suppose to do is use a trigger to update the quantity sold in the inventory table when an order is placed in the concessions_sold table(an insert is used). I admit that I suck at PLSQL so I'm not sure if I'm going about this the right way. The error I'm getting is: SQL> insert into concessions_sold 2 values(33, 104, '26-Apr...
Oracle Ref Cursor Vs Select into with Exception handling
I have Couple of scenarios 1) need to read the value of a column from three different tables in a predefined order and only 1 tabel will have the data 2) read data from table1 if records are present for criteria given else Read Data from Table2 for Given Criteria In Oracle Stored Procedures The way these are being handled right ...
Can't select from dba_tab_cols from within stored procedure (PL/SQL)
Hi. I'm trying to SELECT from the dba_tab_cols view from within a stored procedure. It's not working and I don't know why. If I execute the following SQL as a query: SELECT t.data_type FROM dba_tab_cols t WHERE t.table_name = 'ACCOUNTTYPE' AND t.column_name = 'ACCESSEDBY'; it works fine. However if I copy it into a stored p...
PLSQL order by issue
Rolled back to revision one, then edited somewhat. See revised question. I have an interesting issue with the below SELECT. Its about ORDER BY clause; I am trying to sort using a variable called "p_sortby". Order by can be used by column name or column position, (1, 2, … etc. ). Somehow, if I use position in the PL/SQL, it doesn’t w...
SQL order by Issue-Continued
This issue is a continuation of my earlier query. It's still not working. It's about an ORDER BY clause. I am trying to sort using a variable called "sortby". Here, now the ORDER BY clause is selected as a separate column using the DECODE() function (as suggested in the answer by @devio in the original version of this question). Let’s ...
What's a good PL/SQL source code analysis tool?
What's a good PL/SQL source code analysis tool? ...
SQL Sorting using Order by
Can you all please help me with this? Presently, I have this SELECT which returns data ordered by this way SELECT DISTINCT gl.group_id, gl.group_name, gl.group_description, gl.status_code, gl.member_count, ( SELECT grpp.group_name FROM test_group_relationship grel JO...
scalar subquery in if statement Condition in PL/SQL
I have an If Statement block similar to the below which is failing with the error - PLS-00103: Encountered the symbol "SELECT" when expecting one of the following.... Begin If (select count(*) from Table1) > 0 then dbms_output.put_line('Test'); end if; end; I have similar Case statement which works fine select case ...
Separating SQL Results Into Ranges
I would like to take a simple query on a list of members that are indexed by a number and group them into 'buckets' of equal size. So the base query is: select my_members.member_index from my_members where my_members.active=1; Say I get 1000 member index numbers back, now I want to split them into 10 equally sized groups by a max and...
SQL ORDER BY issue
Duplicate of this question, which itself cross-references 3 other minor variants of the same question. Hi all, I am sorry but i am putting my question as a new question in this post.I need all your help very much now and i appreciate your help very much SELECT * FROM (SELECT distinct gl.group_id, gl.group_name, ...
PL/SQL Developer - ignore/limit large data in queries
In PL/SQL Developer v7.1.x, is there way way to ignore large data types in queries or the "Query Data" feature. For example: If you right click on table FOO, and select "Query Data" this will execute a SELECT * FROM FOO. If that table contains BLOB data the query will take a while to complete and temporarily lock up the application. T...