Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Pivot/unpivot

Dorian GrimMar 6 2019 — edited Mar 11 2019

I am on Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

======

I need:

create table my_table_name 

(

  a varchar2(255)

  ,b varchar2(255)

  ,c varchar2(255)

  ,jan int

  ,feb int

  ,mar int

  ,apr int

);

insert into (a, b, c, jan, feb, mar, apr) values ('a', 'b', 'c', 11, 1, 12, 111);

abcjanfebmarapr
abc11112111

to look like:

abcdate_idval
abcjan11
abcfeb1
abcmar12
abcapr111

Could someone please help me achieve this?

I know of UNION, but I don't want to use it in hopes there's something already there for me, built-in.

with main as

(

  select

    'jan' date_id

    ,a

    ,b

    ,c

    ,jan as val

  from

    my_table_name

union all

  select

    'feb' date_id

    ,a

    ,b

    ,c

    ,feb as val

  from

    my_table_name

union all

  select

    'mar' date_id

    ,a

    ,b

    ,c

    ,mar as val

  from

    my_table_name

union all

  select

    'apr' date_id

    ,a

    ,b

    ,c

    ,apr as val

  from

    my_table_name

)

select * from main;

Thanks,

Dorian

This post has been answered by Frank Kulash on Mar 6 2019
Jump to Answer

Comments

vikram thakur

Hi,

Just curious, can you try without the double-quotes for region?

From:

region=us-"ashburn-1"

To:

region=us-ashburn-1

Thanks,

Vikram

3622227

Thanks Vikram for pointing out. Still the same without any quotes.

I was desperate and trying different options and tried with quotes. Initial config file created has no quotes and i reverted back what it was.

Tried with different region too but no luck.

$cat config

[DEFAULT]

user=ocid1.user.oc1..aaaaxxxxxxxxxrhbpmiv3vmx3fanyfotrjnfa

fingerprint=5e:e3:43:c6:b3:64:73:df:a4:e2:a6:a3:35:e6:39:e3

key_file=/home/.oci/oci_api_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaxjjaryrxflyysgaganbmybm

region=us-ashburn-1

I am out of ideas and any help in this regard is greatly appreciated.

vikram thakur
Answer

Understood. I also noticed the file timestamp for oci_api_key_public.pem, oci_api_key.pem and config are all the same. So I did not think the config file was manually updated.

Was yours an auto install or manual? From documentation, it almost looks like some of your information may be incorrect in the config. Clock, I remember, you mentioned is in sync.

---- from doc (Ref: https://docs.us-phoenix-1.oraclecloud.com/Content/API/References/apierrors.htm#ErrorDetailsandTroubleshooting )

html status code: 401

  • Missing or incorrect authentication information. Verify that all the required information (tenant OCID, user OCID, fingerprint, and private key) is provided and accurate. Verify that the public key corresponding to the fingerprint has been uploaded for the user. For more information, see Required Keys and OCIDs.
  • Clock skew. This status code is returned if the client's clock is skewed more than 5 minutes from the server's clock. For more information, see Maximum Allowed Client Clock Skew.
  • API request signature error. This status code is returned if a required header is missing from a signing string. For more information, see Request Signatures.

Thanks,

Vikram

Marked as Answer by 3622227 · Mar 17 2018
3622227

I did a fresh install on a OCI compute instance just to see if it has anything to do with my install scripts.

Anyway I tried with a new user instead of a service administrator and it worked ...YAYYYYYYYYYYYYYYYYYYYY

Vikram thanks a lot man for your kind support and help.... Really appreciated.

[opc@shaikprod .oci]$ oci os ns get

{

  "data": "shaikscloud33"

}

User_ID7D0

I'm having same problem

User_YCV6N

Hi,
I was also facing similar issue as above followed the below steps ,but while trying initialKubeSetup its throwing 401,Can anyone
once in the BOAT, follow the below instructions -
Click on the user icon in the top right
Click on "User Settings"
Click on "API Keys"
Click on "Add API Key"
Either generate a keypair, or use your own
Copy the private key to the host machine
Copy the configuration file, and update your ~/.oci/config with the required information
image.png

I was having a similar problem when trying to create a PHP script to download an object from Object Storage via the API.

It turned out that the date format was the cause of the problem. Using date("r") from PHP will generate a date string like this:

Sat, 26 Oct 2024 15:52:49 +0000

However, I had to use a named timezone like GMT instead:

Sat, 26 Oct 2024 15:52:49 GMT

As soon as I made this change, it accepted the signature.

nausherwan adil

When you authenticate an OCI session, a new key pair and fingerprint are generated. However, the OCI console does not automatically update with the newly generated key pair and fingerprint.

There are two solutions to resolve this:

Create a new session using oci session authenticate. This will generate a new OCI config along with a new key pair. Then, upload the new public key to the OCI console under the API Key section.

Manually generate an API key, download the key pair, and upload the public key to the OCI console. Then, copy the newly generated key pair to your OCI config folder on your machine. Additionally, when a new API key is generated in the OCI console, update your OCI config file accordingly on your machine.

Following either of these approaches will resolve the issue.

1 - 8

Post Details

Added on Mar 6 2019
13 comments
384 views