No errors.
BEGIN
*
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-06512: at "CDSC.ENCRYPTPW", line 1
ORA-06512: at line 2
No errors.
SQL>
I TRIES TO TEST SERVICE NAMES CONNECTION FROM NETMANAGER, I CAN CONNECT TO DATABASE BUT I WHEN I TRY I CONNECT TO EXTERNAL PROCEDURE IT FAILS
MY LISTENER.ORA AND TNSNAME.ORA ARE CONFIGURED AS FOLLOWS
LISTENER\:
SID_LIST_LISTENER =
(SID_LIST =
I'm struggling with my configuration to be able to run some external procedures.I'm under 11gR2 and RHEL5 64bits. Following is my extproc.ora file :
CODESET LD_LIBRARY_PATH=/path/to/my/lib SET EXTPROC_DLLS=lib1:lib2 My LD_LIBRARY_PATH is valid and permissions are 755 but I'm getting this error message: CODEDECLARE * ERROR at line 1: ORA-06520: PL/SQL: Error loading external library ORA-06522: lib1: cannot open shared object file: No such file or directory ORA-06512: at line 51
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have created two new procedures. When I log in as ADMIN, I can execute these procedures.. but when i log in as WC_GUEST or WC_TECH, I cannot.. One of the procedure name is WCL_RPRTD_PARTS_REP_SEARCH, to which, I have given this: grant execute on WCL_RPRTD_PARTS_REP_SEARCH to WC_GUEST,WC_TECH;
Also, I have changed the ROLE of ADMIN, to the same role as these two users and tried.. it works fine.
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
I'd like to call an existing function/procedure from a resource template. For example, I have a procedure called 'rest' with an out parameter of 'p_out' - how do I use this within a resource template?
At the moment I have defined the template as type 'PL/SQL Block' and with the 'GET' defined as:
begin myschema.rest(:p_out); end;
APEX_PUBLIC_USER has been granted 'all' on the procedure. The p_out is populated with a piece of text.
When I call this resource, I get a '500 Internal Error' returned. I've tried adding p_out as an X-APEX-FORWARD parameter, but nothing seems to work.
I am using oracle 11.2.0 in linux platform.i have created a listener for the asm database. and i am using non default port of 1525 whenever i go for lsnrctl>status always shows for the port 1521. i don't want the default port 1521 and i have changed the local listener as listener1 with port 1525 protool tcp in spfile and registered with the asm database.Is there any option to change the default listener as listener1.
also,everytime when i give lsnrctl>show current_listener it is showing "listener"
i have set the current-listener to listener1 and save_config listener1 and reloaded.After exit and enter lsnrctl>status it will show for port 1521 When i attempt to start listener1 every time. it will prompt listener1 is already running.
In one of our RAC envrionment, we have more than 15 databases in a server running in AIX Operating system.the Listener file I am able to find only one entry (SID). But while trying to execute lsnrctl status it displays all the 15 SIDs in the list hosted in the ser ver. Not sure how it works?
As we created another one new database in the database, where i need to add this SID in the listener.ora file to reload the listener.
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.
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?
We have many many APEX applications in APEX 3.0 running on a 10.2.0.4 database that needs desperately to be upgraded. As a test, I've set up a clean 11gR2 database and copied the production APEX database into it via datapump. I set up APEX Listener, as I don't have any OAS sitting around and the EPG doesn't seem to be supported for APEX 3.0... the Listener doesn't say one way or the other.
When trying to log in, I get the login page, but it tries to reference files such as apex_get_3_1.js while I only have files such as htmldb_get.js in my images directory in production. I noticed it is looking for what appears to be 3.1 files instead of 3.0 files... which concerns me.
The APEX listener appears to be more than just a Java PL/SQL gateway.
Is there a minimum version of APEX the APEX Listener supports?
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob
how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?
we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared
Eg: SPIdSP_Name CPU TimeI/OLine Executed 1Emp_AllDetails221 1Emp_AllDetails582 1Emp_AllDetails25103
[code]...
Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.
I come from a Microsoft SQL background and am having trouble adjusting to the PL/SQL syntax. I'd like to build this up to pass back a certain value back into a web form.
IF (SELECT COUNT(*) FROM EXCURSION WHERE EXCURSION_ID = 75)= 1 THEN DBMS_OUTPUT.PUT_LINE 'True'; ELSE DBMS_OUTPUT.PUT_LINE 'False'; END IF;
I'm having trouble writing values out at the moment
I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.
CREATE OR REPLACE PROCEDURE PROC1 IS temp varchar2(500); tmp_name varchar2(5); tmp_no varchar2(4); . BEGIN WHILE NOT end_of_file LOOP
IF i = 18 THEN tmp_no := temp; END IF;
IF i = 21 THEN tmp_name := temp; END IF;
END LOOP; END; /If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);) Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));
how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..
By referring through net I have written the following PCK file. I have installed my Oracle 11g database in VirtualBox(Win XP). I can able to select the tables using query from the host(Windows 7)
CREATE OR REPLACE PACKAGE APEX_SYS_PCK AS TYPE refcursortype IS REF cursor PROCEDURE GET_USER_DETAILS( D1 OUT refcursortype, P_USER IN VARCHAR2, P_PASS IN VARCHAR2);END APEX_SYS_PCK; / CREATE OR REPLACE PACKAGE BODY APEX_SYS_PCKAS PROCEDURE GET_USER_DETAILS( D1 OUT refcursortype, P_USER IN VARCHAR2, P_PASS IN VARCHAR2) IS BEGIN OPEN D1 FOR SELECT * FROM APEX.PERSONS; END GET_USER_DETAILS; END APEX_SYS_PCK;/
When I try to run the script I am getting the following error. How to solve this problem?
Project: sqldev.temp:/IdeConnections%23vAPEX.jprF:PACKAGESAPEX_SYS_PCK.sqlError: PL/SQL: Compilation unit analysis terminatedError(1,14): PLS-00905: object APEX.APEX_SYS_PCK is invalidError(1,14): PLS-00304: cannot compile body of 'APEX_SYS_PCK' without its specification
Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.
Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?
It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.
Scenario is like below:
create user a identified by a; grant connect,resource to a; create user b identified by b; grant connect,resource to b; create user c identified by c; grant connect,resource to c;
[code]....
Not getting why the schema is not changing... and how to resolve the error.
I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?
Give sql query to find the list of unused tables and procedures.
