SQL & PL/SQL :: How To Execute Function In Developer
Oct 9, 2012
I have converted one function from sql to oracle through sql developer. so it's created with package name. I execute package name and function also
here is my package name and function:
CREATE OR REPLACE PACKAGE FnFetchEmployees_pkg
AS
TYPE tt_v_employees_type IS TABLE OF tt_v_employees%ROWTYPE;
END;
create or replace
FUNCTION FnFetchEmployees
(
v_user_id IN NUMBER
)
RETURN FnFetchEmployees_pkg.tt_v_employees_type PIPELINED
AS
[code]........
it's executed successfully. when i am executing this function like this:
select emp_id from fnfetchemployees_pkg.fnfetchemployees(1) from dual;
getting error: sql command is not properly ended;
View 9 Replies
ADVERTISEMENT
Jul 5, 2011
Is it possible to install and execute Developer 2000 in a laptop using windows 7 64 bits?
View 1 Replies
View Related
Mar 25, 2013
I migrated a Oracle 6i Report to Oracle Report 9i so I'm trying to execute a this report in the Run Web Layout, but this doesn't bring me any information, how can i run this reports in the Web layout?
When i run this report en the Paper Layout it's bring me data. When a run the report just bring me a blank web page.
View 1 Replies
View Related
Apr 27, 2012
I have a requirement where i need to execute a unix shell script "a.sh" having permissions user1,group1 and no access to public. This should be executed from Oracle function.
So i wrote a routine in Java and it works fine when i execute a command like Date etc.
But when i execute "a.sh" from oracle client, its returning error that "could not be executed-permission denied". This is because once login happened through the "sqlplus" the shell takes user as "oracle" and group as "dba" but the script "a.sh" having only permissions user1,group1. Unfortunately we are not allowed to change the permission for "a.sh" to give execute permission to all(public).
After searching in internet, i understand that one way is to make the password (/etc/passwd) for the user "user1" as no password and can use "su" command so that it wont ask password while invoking it.
Is there any other way apart from this doing changes in password file at UNIX level when executing a script from oracle client?
if further information is required.
Oracle: version: 11.2.0.1
UNIX : AIX -5.9
View 3 Replies
View Related
Jun 6, 2013
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;
I got the blow error.
ORA-00905: missing keyword
View 2 Replies
View Related
Jun 25, 2012
I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.
CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);
[code]....
SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11
View 12 Replies
View Related
Jun 6, 2013
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.
ORA-00905: missing keyword
View 5 Replies
View Related
Mar 15, 2011
I am trying to execute the PL/SQL block below:
DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
View 2 Replies
View Related
Jun 6, 2013
I cant compile & execute this function.
create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc
[code]....
View 6 Replies
View Related
Oct 4, 2012
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
View 1 Replies
View Related
Feb 17, 2010
I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.
Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...
I am using the following command:
sqlplus u/p@<someserver> @<path_to_sqlfile>.sql
this sql file contains create procedure pl/sql code
Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?
View 8 Replies
View Related
May 20, 2013
i`m trying to use pl/sql function to execute java class. I created 2 tables with around 100 values and java class with simple functionality. I need to create function to loop through my table1 and get with each iteration one value from table and pass that value as parameter to java class.
table1 is something like this:
ID NAME
1 name1
2 name2
3 name3
. .
. .
100 name100
table2 is empty with same columns as table1 ( table2 is for data obtained from java returns ) I created in sql+ java class.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "myClass" AS
import java.io.*;
import java.util.*
public class myClass {
public static String fun(String strName)
[code]....
Then i wanna make a pl/sql function for:
1)For i = 1 Obtain first value from table1 (column name) and pass it throught java class then return value and save it in table2 with the same id as id from table1
2) change i=1 to i=2 and do that same
3) end if i=101
View 1 Replies
View Related
Oct 23, 2013
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
View 14 Replies
View Related
Jan 8, 2009
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
View 5 Replies
View Related
Jun 15, 2012
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
View 5 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
Jun 10, 2010
What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
View 4 Replies
View Related
Jul 14, 2013
I am not able to debug PL/SQL and i have following error..how I can enable privileges and I want to know the steps of debugging. Connecting to the database My_connection.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUEExecuting PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCPORA-01031: insufficient privilegesORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68ORA-06512: at line 1This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.Process exited.Disconnecting from the database My_connection.
View 1 Replies
View Related
Dec 22, 2010
I have upgraded my Operating Syatem from Vista to Win7. I have SQL nav version but is not supported on Win7. So I have installed PLSQL developer. When I try to connect its giving the error connection not open. But when I tried the same credentials with SQLPLUS it's working fine.
View 3 Replies
View Related
Jul 3, 2012
i try to open PL/SQL Developer and i get this error:
Could not locate OCI dll
OracleHomeKey:SOFTWARE\ORACLE\KEY_OraOracle11g_home1
OracleHomeDir: d:\app\...................\product\11.1.0\client_1
what can be the problem ?
View 2 Replies
View Related
Oct 26, 2011
I am having trouble with creating a view based on the following: -
Total hours flown EVER, for each crew of the FBN001 on 20th October 2011.
Here is what i got so far (only works for each members flown on a particular flight, NOT on their whole hours flown in a period of a lifetime) : -
SELECT e.Employee_Number, e.FirstName, e.LastName,
SUM( ( (s.ESTIMATED_ARRIVAL) - (s.ESTIMATED_DEPARTURE) )*24)AS
TOTAL_HOURS_FLOWN
FROM SCHEDULE s, CREW_ASSIGNMENT ca, EMPLOYEE e
WHERE
s.Flight_Number = ca.Flight_Number
AND
s.Serial_Number = ca.Serial_Number
[code]...
View 20 Replies
View Related
Apr 5, 2011
I have installed Developer 10 suite and 6i on same machine, developer 6i run time works properly but when i open URL with "?config=icc" it says form generated in previous version of forms builder, i have already compile called form (configure in default.env and formsweb.cfg) in forms builder 10g.
View 1 Replies
View Related
Feb 10, 2010
How can i get URL value from Developer10gR1?
View 1 Replies
View Related
Sep 21, 2010
Is it possible to connect a Oracle based application designed in Developer 6i to a remote Oracle database server?
If YES then
How open can do this?
Else
View 4 Replies
View Related
Dec 27, 2010
how to run oracle developer 10g report and what a runtime file extation and where i save my reports
View 1 Replies
View Related
Feb 12, 2011
I have installed oracle form developer10g. without installing the oracle database. Oracle developer 10g can run independently.
I get the Error TNS: Protocol Adapter Error
My listener has the following:
# listener.ora Network Configuration File: D:DevSuiteHome_2NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Oracle8)
(SID_NAME = ORCL)
[code].......
tnsnames.ora has following words:
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
View 2 Replies
View Related
Apr 26, 2009
i've been trying to connect to the Oracle DB with Oracle SQL Developer.I get a message
State: Test Failed - IO Exception "The network adapter could not establish the connection"
When i go try tnsping 1521 It detects a connection by Oracle Database.And finally i get TNS-03505: Failed to resolve name
When i went into the "services.msc" and tryed to run the "OracleOraDb10g_home1TNSListener"
Got a message: "Error 3: The sytem could not find the specified path"
View 9 Replies
View Related
May 13, 2009
I work with several tables at a time in SQL Developer and I find myself having to reopen and freeze every table everytime I start the application. Is there a way to save all those frozen views for next time?
View 4 Replies
View Related
Nov 25, 2011
i install the PL/SQL developer version 9.0.3.1641 on Windows-7 64 bit...i have Oracle 11g on this computer.when i run PL/SQL i got this error:
initialization error
could not initilize "c:\.......oci.dll"
make sure you have the 32 bits oracle client installed..what i need to install and from where to download ? (can i have link to download)
View 1 Replies
View Related