CREATE OR REPLACE PROCEDURE log(repname in varchar2) AS PACKAGE_NAME VARCHAR2,– give size here like varchar2(50) START_TIME DATE, END_TIME DATE, STATUS; BEGIN SELECT PACKAGE_NAME , PRCS_START_TIME , PRCS_END_TIME, STATUS — add into clause to assign selected –values in variables FROM CONTCL_OWNER.PROCESSLOG WHERE PACKAGE_NAME LIKE ‘%REPNAME%’ And ROW_NUMBER <=7 ORDER BY PRCS_START_TIME ; END; solved My PL/SQL … Read more
If by private, you mean a procedure that’s defined in the package body and not exposed in header, then no. The other package won’t be able to “see” the procedure. SQL> CREATE OR REPLACE PACKAGE foo AS END; — No “public” procedures/functions 2 / Package FOO compiled SQL> CREATE OR REPLACE PACKAGE BODY foo 2 … Read more
You can just change the last part to have a subquery (I will use in so you can have multiple values in the WHERE clause) SELECT a.CLIENTID, a.CLIENTNAME, a.CREATEDATE, b.AREACODE, b.PHONENUMBER FROM CLIENT a INNER JOIN PHONE b ON a.CLIENTID = b.CLIENTID WHERE b.AREACODE IN /* You can put your query inside the ()s */ … Read more
I’m using this same functionality in one of my project, please check my code below you can either make a page that will render the image <img src=”https://stackoverflow.com/questions/50098121/image.php?id=123″ /> That image.php page would have this: $sql = “SELECT image FROM images WHERE image_id = ” . (int) $_GET[‘id’]; $stid = oci_parse($conn, $sql); oci_execute($stid); $row = … Read more
Maybe something like this: SELECT b.id FROM iv_stock_details a, iv_stock_details b WHERE a.id + 1 = b.id AND b.stock_stat_no >= a.stock_stat_no AND b.stock_stat_no < a.stock_end_no AND b.stock_end_no <= a.stock_end_no AND b.stock_end_no > a.stock_stat_no; SQLFiddle: http://sqlfiddle.com/#!2/94722/7 solved Compare nth row with n+1 th row and if it lies in range of n th row print n+1th … Read more
select order_number, line_number, isnull(max(case when linenum=1 then name else null end),”) name1 , isnull(max(case when linenum=1 then value else null end),”) value1, isnull(max(case when linenum=2 then name else null end),”) name2, isnull(max(case when linenum=2 then value else null end),”) value2, isnull(max(case when linenum=3 then name else null end),”) name3, isnull(max(case when linenum=3 then value else … Read more
Use a regular expression to replace everything from each ~ tilde character until the next comma , or pipe | character (exclusive of that final character): Oracle Setup: CREATE TABLE your_data ( input_string ) AS SELECT ‘(,QUESTION-3914~Please enter the unique identification number associated with the IRQ.|3~Greater Than|5~5,AND,QUESTION-3920~Select the contract action that applies to this IRQ.|5~Equal … Read more
The INTO goes just after the SELECT part, before the FROM. select e.editionid into tagnow from webtags w, edition e Also, try to learn standard ANSI JOIN, instead of using this old Oracle way, and pay attention in using aliases in joiur join conditions. You query would become: SELECT e.editionid INTO tagnow FROM webtags w … Read more
the program is not accepting today’s date. it gives me error (the program should only accept today’s date and date in the future ) solved the program is not accepting today’s date. it gives me error (the program should only accept today’s date and date in the future )
Since your procedure has an out variable, provide it with a fifth variable to get the out value: declare p_user_id int; begin insert_user(‘user’, ‘password’, ‘[email protected]’, ‘5’, p_user_id); end; Note: You don’t set the value in your procedure, so you could either let it away at all or you have to set it in your procedure. … Read more
Try this Query:- SELECT CMPI_PRCINX AS CURRENT_PRICE, (SELECT MAX(CMPI_PRCINX) FROM CMD_MTRL_PRICE_INF WHERE CMPI_PRCINX<(SELECT MAX(CMPI_PRCINX) FROM CMD_MTRL_PRICE_INF)) AS PRIVIOUS_PRICE FROM CMD_MTRL_PRICE_INF WHERE CMPI_PRCINX = (SELECT MAX(CMPI_PRCINX) FROM CMD_MTRL_PRICE_INF GROUP BY CMI_CODE) here is the SQL Fiddle Code. http://sqlfiddle.com/#!4/46a23/2 solved getting previous data (one less running column)
Have you seen MSDN Document as it clearly says in the class definiton [ObsoleteAttribute(“OracleConnection has been deprecated. http://go.microsoft.com/fwlink/?LinkID=144260”, false)] public sealed class OracleConnection : DbConnection, ICloneable Follow the link mentioned in attribute constructor parameter (Oracle and ADO.NET) You should rather use the specific Data provider from Oracle An Example: Connecting to Oracle Database through C#? … Read more
Assuming 10,john should be 15,john this looks like a very simple join. If the below model is incorrect please copy it, edit it and add to your question. drop table if exists t1,t2,t3; create table t1(u_id int, name varchar(10), prd int); create table t2(u_id int, temp int); create table t3(u_id int, Office int); insert into … Read more
SELECT DISTINCT A.EMPLOYEENAME FROM Attendance AS A JOIN Attendance AS B ON B.LEAVE_DATE = A.LEAVE_DATE + 1 AND B.EMPLOYEENAME = A.EMPLOYEENAME JOIN Attendance AS C ON C.LEAVE_DATE = B.LEAVE_DATE + 1 AND C.EMPLOYEENAME = B.EMPLOYEENAME The inner joins will remove all employee who were not absent three consecutive days. 14 solved Find out the Employees … Read more
You are missing IN in the FOR statement; it should be: FOR rec IN (SELECT privilege, admin_option FROM dba_sys_privs WHERE grantee = l_username) LOOP See http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/static.htm#CIHCGJAD 2 solved SQL Procedure Removing System Privledges [closed]