Parallelism in PL/SQL
hi how can i run one query in pl-sql- in parallel? i need all the flow... ...
plsql
how can i optimize this query taking 30 seconds for 1746 rows
query: SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID) FROM USER_ROLE A, USER B, ROLE C WHERE A.ROLE_ID = C.ROLE_ID AND C.GROUP_ID = 3 AND A.USER_ID = B.USER_ID AND B.TEMPLATE_IND = 'N' AND B.ONAP_PARTCODE IS NULL ...
Extract all tables of Oracle schema using PL/SQL Procedure
Hi, I am using a PL/SQL procedure to extract Oracle tables of a particular schema to csv format. I execute the procedure by the following command: exec dump_table_to_csv ( 'schema.tablename', 'directory', 'tablename.csv'); The directory is created using the following command: CREATE DIRECTORY <directory_name> AS '<operating_system...
Oracle PL/SQL script generation from excel sheet
Hi, I'm having requirement to generate a insert script file from excel sheet. I'm partly successfully in generating script file. But i got struck in a situation,I need help from any1. My logic is some thing like this, Read first cell,check if the value in the cell already exists in DB.If not, generate an insert script as follow declar...
Nested Begin in a script file ,Stack Overflow during script execution
Hi, Recently I'm having requirement to generate script file from excel sheet. I'm able to acomplish task almost. Bu the problem is usage of Local varibales are more in script file are more.Becuase of more Local variale, During execution of script file in toad,it throws an error PL/SQL 102 Stack overflow exception during insertion script...
Looking for a utility that converts a SQL statement into a dynamic SQL statement
I am looking for a utility that will convert Oracle SQL to a string that can executed dynamically. Edit: Yes, consider this simple SQL SELECT * FROM TABLE WHERE COLUMN_NAME = 'VALUE' I have a utility which for T-SQL which converts the above SQL to a synamic SQL as follows: BEGIN DECLARE @Exe_String VarChar(2000) DECLARE @Qt ...
Oracle: Store "5-" as a number
SHORT VERSION: What is the best way of casting "5-" as a number for storing into a table as a number? LONG VERSION: Here is the issue, I have a table I am reading from primary made up of VARCHAR2s. I am grabbing the data from some fields and storing them in another table without modification. In one field I am getting "5-" and trying to...
how to convert global temp table to collection/type in oracle procedure
I have a procedure that is using a temp table. I want to get rid of the temp table and use a collection to remove I/O. it has about 5000 records. I want to insert data into this collection then I want to access the collection like: select * from table(my_type_for_gtt) I could not find an example of this. I'm confused, do I have to fi...
Oracle using variable in where like
I'm trying to run SQL in a PL/SQL procedure. If I were to say: SELECT * FROM myTable WHERE FNAME like 'Joe%' AND dept = 'xyz'; this runs instantly. if I try putting 'Joe' in a variable it takes forever to run, about 3 minutes. Here is some syntax I found for using like with variables. PROCEDURE myProcedure(firstName IN VARCHAR, ...
How can I determine the actual database row insertion order?
I have a multithreaded process which inserts several records into a single table. The inserts are performed in a stored procedure, with the sequence being generated INTO a variable, and that variable is later used inside of an INSERT. Given that I'm not doing mysequence.nextval inside the INSERT itself, it makes me think that it is pos...
Regarding sql script
I have the line below in a .sql script. Not sure what it does. Does it wait for input from user for dbuserid and password or does it take the values from environment? What does && denote? In test.sql file I saee the line below before creating tables: CONNECT &&usr/&&pass@&&dbname ...
run sql script from shell script
I have the below shell script that calls an sql script. The problem is it calls the sql script, i enter the username,password ,dbname and after that it creates the table but it doesn't exit from the sql prompt. test.sh #!/usr/bin/ksh sql_test=test.sql $ORACLE_HOME/bin/sqlplus /nolog @${sql_test} exit test.sql CONNECT &&usr/&&pa...
ORA-14551: cannot perform a DML operation inside a query
I have the following inside a package and it is giving me an error: ORA-14551: cannot perform a DML operation inside a query Code is: DECLARE CURSOR F IS SELECT ROLE_ID FROM ROLE WHERE GROUP = 3 ORDER BY GROUP ASC; BEGIN FOR R IN F LOOP DELETE FROM my_gtt_1; COMMIT; INSERT INTO my_gtt_1 ...
how to write loop for a record type in oracle
I have the following statement which compiles fine in my package: package header: TYPE role_user_type IS RECORD ( ROLE_ID some_table.ROLE_ID%TYPE, SUBGROUP some_table.USER_ID%TYPE ); body: ROLE_USER_REC MY_PACKAGE.ROLE_USER_TYPE; SELECT B.USER_ID, B.ROLE INTO ROLE_USER_REC FROM some_t...
How can I insert a record and lock that row simultaneously?
I'm inserting a row using an Oracle stored procedure which is configured to use an autonomous transaction. I'd like to insert this record, commit that transaction, and then lock the newly-inserted record so that nobody else can modify it except my current session (in another transaction, obviously, since the one that inserted it is auto...
How to convert char into an array in Informix database?
I have a stored procedure like this: CREATE PROCEDURE Proc_Test(testvalue set(char(1000) not null)) RETURNING int; DEFINE z char(7000); LET z = ' '; FOREACH select * into z from table(testvalue) END FOREACH; return 1; end procedure; Due to the change in the requirement I would like to change this stored procedure as:...
long-time Oracle user switching to MySQL, any gotchas to be aware of?
It happened that all projects during the last 10 years were with Oracle as a database server. Now I'm starting on a side project on my own with MySQL (the latest stable). Are there any gotchas (things that do not work as expected by an Oracle user)? Anything to do with transaction management, locking, isolation levels, indexes, that ki...
sql select into
I have the below code in test.sql file. When there is a record in test_summary table everything works fine. But when there is no record inside the table it fails. I want it to continue to print the dbms_output message and process when there is no record. How can I do that? declare total_var number(20,2) := 0.0; nl_var ...
Are there any tools for converting SAS to SQL?
I am confronted with a 4-page SAS thing - macro? procedure? it has procedures within it, DATA blocks and PROC blocks, one PROC SQL block for importing from Oracle. Is there a tool that could translate SAS to something readable? Ideal would be SQL or PL/SQL (both seem applicable here), but almost anything procedural would be an improvem...
ORACLE Send Email using SSL
is it possible to send e-mail over ssl in plsql? thanks for helping. ...