I don’t think you can with a procedure. Edit: Thanks to DCookie for simplifying my original answer. But as a work-around you can write a function that calls the procedure and then invoke that using SQL. e.g. create or replace function callmyproc return sys_refcursor IS rc sys_refcursor; BEGIN myproc(rc); return rc; END; Which you can … Read more
if you have access to the file system on your database box you could do something like this: CREATE OR REPLACE DIRECTORY documents AS ‘C:\’; SET SERVEROUTPUT ON DECLARE l_file UTL_FILE.FILE_TYPE; l_clob CLOB; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; BEGIN SELECT col1 INTO l_clob FROM tab1 WHERE rownum = 1; l_file … Read more
You can use a bind variable declared in SQL Developer to hold and show the results: var r refcursor; exec myPackage.mySPTest(P_NOTIFICATION_ID => 1975357, P_CURSOR => :r); print r; exec is shorthand for an anonymous block so this is equivalent to: var r refcursor; begin myPackage.mySPTest(P_NOTIFICATION_ID => 1975357, P_CURSOR => :r); end; / print r; Unless … Read more
If you need to change the time zone of Oracle SQL Developer (or Oracle Data Modeler), then this is how to do it: Go to the installation directory of Oracle SQL Developer. Open the file located at: sqldeveloper/bin/sqldeveloper.conf. At the end of file, add the following line: AddVMOption -Duser.timezone=GMT-4. You will need to change the … Read more
You’re trying to look too far back. You can only convert to and from SCNs that are in the redo/flashback window maintained by your system. Once changes age out then the mapping is lost. This is explained in the documentation: The association between an SCN and a timestamp when the SCN is generated is remembered … Read more
After converting your varchar2 date to a true date datatype, then convert back to varchar2 with the desired mask: to_char(to_date(’01/02/2012′,’MM/DD/YYYY’),’WW’) If you want the week number in a number datatype, you can wrap the statement in to_number(): to_number(to_char(to_date(’01/02/2012′,’MM/DD/YYYY’),’WW’)) However, you have several week number options to consider: WW Week of year (1-53) where week 1 … Read more
Unfortunately SQL Developer doesn’t fully honour the set echo off command that would (appear to) solve this in SQL*Plus. The only workaround I’ve found for this is to save what you’re doing as a script, e.g. test.sql with: set echo off spool c:\test.csv select /*csv*/ username, user_id, created from all_users; spool off; And then from … Read more
If GetQuestions is a function returning a refcursor, which seems to be what you have in the SQL Server version, then rather you may be able to do something like this: select * from table(MyPackage.GetQuestions(‘OMG Ponies’)); Or if you need it in a PL/SQL block then you can use the same select in a cursor. … Read more
Oracle SQL Developer 4.0.1.14 surely does support connections to PostgreSQL. download JDBC driver for Postgres (http://jdbc.postgresql.org/download.html) in SQL Developer go to Tools → Preferences, Database → Third Party JDBC Drivers and add the jar file (see http://www.oracle.com/technetwork/products/migration/jdbc-migration-1923524.html for step by step example) now just make a new Database Connection and instead of Oracle, select PostgreSQL … Read more
Create a diagram for existing database schema or its subset as follows: Click File → Data Modeler → Import → Data Dictionary. Select a DB connection (add one if none). Click Next. Check one or more schema names. Click Next. Check one or more objects to import. Click Next. Click Finish. The ERD is displayed. … Read more